1

Initially I had used the following command to rename SQL tables:

Q = """sp_rename {}, {}""".format(OLD_TABLE_NAME,NEW_TABLE NAME)

However, this caused an "Lock request time out period exceeded" error, which I believe was due to the lack of "commit" at the end of the query (although I am not confident on this).

So instead, I adopted a new query (adapted from this question).

Q2 = """BEGIN TRANSACTION
    GO
    EXECUTE sp_rename N'{}', N'{}', 'OBJECT' 
    GO
    ALTER TABLE {} SET (LOCK_ESCALATION = TABLE)
    GO
    COMMIT""".format(OLD_TABLE_NAME,NEW_TABLE NAME,NEW_TABLE NAME)

However, I'm now getting a ProgrammingError saying "Incorrect syntax near 'GO'."

Do I need to remove some parts of Q2 for the query to work? Or is some other part wrong?

Below are the two functions I use to connect to my SQL server:

from sqlalchemy import create_engine
import pypyodbc as pp

server1 = {
        'drivername': 'mssql+pyodbc',
        'servername': 'SERVERNAME',
        #'port': '5432',
        'username': 'WebAccess',
        'password': ':|Ax-*6_6!5H',
        'driver': 'SQL Server Native Client 11.0',
        'trusted_connection': 'yes',  
        'legacy_schema_aliasing': False
        }

def getEngine(servername, database):
    DB = server1

    #Create connection to SQL database
    DB['database'] = database

    servername1 = servername.lower()

    engine = create_engine('mssql+pyodbc://' + DB['username'] + ':' + DB['password'] + '@' + DB['servername'] + '/' + DB['database'] + '?' + 'driver=' + DB['driver'])#, echo=True)

    return engine



def SQLcommand(query,servername,database):

    connection = pp.connect("""Driver={SQL Server};Server=""" + servername + """;Database=""" + database + """;uid=USERNAME;pwd=PASSWORD""")

    cursor = connection.cursor()

    cursor.execute(query)

    connection.commit()

    connection.close()
OD1995
  • 1,647
  • 4
  • 22
  • 52

3 Answers3

1

This works for me based on the note in the SQLAlchemy Docs:

sql_stmt = f"""EXECUTE sp_rename '{table2}', '{table1}';"""            

with connection.begin() as conn:
     conn.execute(text(sql_stmt))
Bobby Lansing
  • 11
  • 1
0

Alright dude, you got a few problems here.

  1. pypyodbc is something you should move away from. That library was all the rage a while ago, but to my knowledge it's not getting many commits anymore, google is moving away from it, in my build we moved away from it for a number of reasons. It was great while it lasted but I think its on the out.

  2. You cannot use 'GO' in a query. 'GO' is not a tsql statement, its a sqlcmd command - thus it doesn't work with odbc. 'GO' essentially just breaks the code into batches, so to do this not using 'GO' you need to run multiple batches, something like:

    conn = engine.connect()
tran = conn.transaction()
conn.execute(f"EXECUTE sp_rename N'{OLD_TABLE_NAME}', N'{NEW_TABLE_NAME}', 'OBJECT'")
conn.execute(f"ALTER TABLE {NEW_TABLE_NAME} SET LOCK_ESCALATION = TABLE")
tran.commit()

I'm sure there's more going on here as well, but hopefully this'll get you going.

Jamie Marshall
  • 1,885
  • 3
  • 27
  • 50
  • The line `tran = conn.transaction()` causes the error `TypeError: transaction() missing 1 required positional argument: 'callable_'` – OD1995 Apr 05 '19 at 10:19
  • is your engine connected successfully? After this line `conn = engine.connect()` try `conn.execute("SELECT 1")` Does that line succeed or fail? – Jamie Marshall Apr 05 '19 at 15:41
  • The output for that is `` – OD1995 Apr 10 '19 at 10:33
  • Is it because I'm using `sqlalchemy`? Is that not the library you were expecting? This link (https://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Connection.transaction) suggests `conn.transaction()` does need a callable argument – OD1995 Apr 10 '19 at 12:49
  • Your output does not show the result of "SELECT 1". ` – Jamie Marshall Apr 10 '19 at 17:21
  • I'm not sure what you mean. Should I do `print(conn.execute("SELECT 1"))` instead? – OD1995 May 14 '19 at 10:15
  • I think maybe you should look into some basic OOP programming principals. You are getting a result, but it's in the form of an `object`. You're seeing the `memory address` of that result `object` because you're printing the `reference` to the `object` instead of interacting with the `object` itself. `objects` can only be interacted with via `methods` and `properties`. I would suggest looking into these key terms. That should get you to the finish line. You're almost there. – Jamie Marshall May 14 '19 at 18:45
0

For anyone still wondering how to solve the problem, I fixed Jamie Marshall's answer in order to adapt it to new transaction api:

conn = engine.connect()

def rename(cnxn):
    rename_query = f"EXECUTE sp_rename N'{OLD_TABLE_NAME}', N'{NEW_TABLE_NAME}', 'OBJECT'"
    cnxn.execute(rename_query)

conn.transaction(rename)
AleDig
  • 1