CODEWITHSUNDEEP
pythonsqlpyodbc
prdctofchem
prdctofchem

Reputation: 49

SQL ALTER and DROP database IF EXISTS with PYODBC

As a SQL newbie, I am attempting to ensure that the initial import of data to a database is not going to create duplicate entries, and the databases will be created programatically. I won't be surprised if there is a more efficient way of doing this (do tell!) but my method is to drop the database and recreate it if it already exists, which as far as I can tell is very fast. I have used this same statement successfully outside of a function and without the formatted string, but in creating a function to do it I am receiving the error via PYODBC:

ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'test'. (207) (SQLExecDirectW)")

This is confusing since I'm not trying to reference any columns, let alone tables; so this has made it difficult to troubleshoot. The functions are as follows:

def db_connect(db, driver='ODBC Driver 17 for SQL Server', host='', UID='', PWD='', autocommit=False):
    """Returns a connection and a cursor object for the specified database."""
    conn = pyodbc.connect(driver=driver,
                        host=host,
                        database=db,
                        UID=UID,
                        PWD=PWD,
                        autocommit=autocommit
                        )
    print(f'Connect established to database {db}')
    return conn, conn.cursor()

def db_clear(db, recreate=True):
    """Drops and recreates the specified database, ready for insert."""
    conn, curs = db_connect('master')
    curs.execute(f"""IF EXISTS (SELECT name from sys.databases WHERE (name = {db}))
        BEGIN
            ALTER DATABASE {db} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            DROP DATABASE {db};
        END;""")
    if recreate:
        curs.execute(f"CREATE DATABASE {db};")
        conn.close()
        print(f'{db} successfully dropped and recreated.')
    else:
        print(f'{db} successfully dropped.')
    return

db_clear('test')

The exception is raised on the line containing END;"""). There are only two differences between the working version (not contained in a function) and this function version, in that I started using a newer driver to better handle data type conversions, and I turned autocommit off to insert in batches after these functions have done their jobs. I tried reverting both of those options back to my original settings within the function but received the same error. Any help is appreciated!

Upvotes: 1

Views: 4429

Answers (2)

the_deb
the_deb

Reputation: 61

Turn autocommit ON ( = True) to execute an ALTER DATABASE statement. "CREATE ... statement not allowed within multi-statement transaction" when using pyodbc

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123549

Your string formatting

f"""IF EXISTS (SELECT name from sys.databases WHERE (name = {db}))
    BEGIN
        ALTER DATABASE {db} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        DROP DATABASE {db};
    END;"""

is producing

IF EXISTS (SELECT name from sys.databases WHERE (name = test))
BEGIN
    ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE test;
END;

and when the SQL parser sees WHERE (name = test) it interprets test as a column name (in the sys.databases table/view), just as it (correctly) interprets name as a column name.

Instead, what you want to do is supply a parameter value to the WHERE clause:

sql = f"""IF EXISTS (SELECT name from sys.databases WHERE (name = ?))
          BEGIN
              ALTER DATABASE [{db}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
              DROP DATABASE [{db}];
          END;"""
curs.execute(sql, db)

Upvotes: 1

Related Questions