SQL ALTER and DROP database IF EXISTS with PYODBC

Question

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!

Gord Thompson · Accepted Answer · 2020-03-22T21:27:05.233

1

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)

edited Mar 22 '20 at 21:27

answered Mar 20 '20 at 18:26

Gord Thompson

116,920
32
215
418

Thanks! I have a similar issue with the statement `cursor_obj.executemany(f"""INSERT INTO {table_name} ({columns}) VALUES ({parameter_placeholders});""", subset)` where subset is a list of tuples (rows). Is there a more efficient way to parameterize {table_name} without copying each tuple in the list and adding the table_name as the first parameter? I am having trouble finding these details on PYODBC parameters. – prdctofchem Mar 22 '20 at 21:01
Use an f-string to create the SQL command text, e.g., `INSERT INTO my_table (col_a, col_b) VALUES (?, ?)` and then pass `executemany` that command text along with the list of tuples containing just the column *values*. If you need more clarification feel free to [ask a new question](https://stackoverflow.com/questions/ask). – Gord Thompson Mar 22 '20 at 21:22

score 1 · Answer 2 · answered Jun 09 '20 at 00:48

1

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

answered Jun 09 '20 at 00:48

the_deb

61
1
1

SQL ALTER and DROP database IF EXISTS with PYODBC

2 Answers2