create a database using pyodbc

Question

I am trying to create a database using pyodbc, however, I cannot find it seems to be paradox as the pyodbc needs to connect to a database first, and the new database is created within the linked one. Please correct me if I am wrong.

In my case, I used following code to create a new database

conn = pyodbc.connect("driver={SQL Server};server= serverName; database=databaseName; trusted_connection=true") 

cursor = conn.cursor()

sqlcommand = """
                   CREATE DATABASE ['+ @IndexDBName +'] ON  PRIMARY 
                    ( NAME = N'''+ @IndexDBName+''', FILENAME = N''' + @mdfFileName + ''' , SIZE = 4000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
                     LOG ON 
                    ( NAME = N'''+ @IndexDBName+'_log'', FILENAME = N''' + @ldfFileName + ''' , SIZE = 1024KB , MAXSIZE = 100GB , FILEGROWTH = 10%)'
             """

cursor.execute(sqlcommand)

cursor.commit()

conn.commit()

The above code works without errors, however, there is no database created.

So how can I create a database using pyodbc?

Thanks a lot.

Birdsong

Answer 1

If you try to create a database with the default autocommit value for the connection, you should receive an error like the following. If you're not seeing this error message, try updating the SQL Server native client for a more descriptive message:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0]
[SQL Server]CREATE DATABASE statement not allowed within multi-statement transaction.
(226) (SQLExecDirectW)')

Turn on autocommit for the connection to resolve:

conn = pyodbc.connect("driver={SQL Server};server=serverName; database=master; trusted_connection=true",
                      autocommit=True) 

Note two things:

• autocommit is not part of the connection string, it is a separate keyword passed to the connect function
• specify the initial connection database context is the master system database

As an aside, you may want to check the @IndexDBName, @mdfFileName, and @ldfFileName are being appropriately set in your T-SQL. With the code you provided, a database named '+ @IndexDBName +' would be created.

Cady

Sooo you didn't really explain what autocommit actually does? – Hong

@Hong - Since context is an ODBC driver for SQL Server, Transactions in ODBC may be helpful for details. – Cady

Thank you for explicitly pointing this out! I was wondering why the database wouldn't store my pawo change for the 'sa' account and have been researching about access permissions to no avail! – Histoplasmosis

Answer 2

The accepted answer did not work for me but I managed to create a database using the following code on Ubuntu:

conn_str = r"Driver={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1};" + f"""
    Server={server_ip};
    UID=sa;
    PWD=passwd;
"""
conn = pyodbc.connect(conn_str, autocommit=True)
cursor = conn.cursor()
cursor.execute(f"CREATE DATABASE {db_name}")

Which uses the default "master database" when connecting. You can check if the dataset is created by this query:

SELECT name FROM master.sys.databases

Fiora