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.

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.