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.

Bryan · Accepted Answer · 2014-09-04 17:54:44Z

15

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.

edited Sep 4, 2014 at 17:54

answered Sep 4, 2014 at 17:06

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NoName Over a year ago

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

Bryan Over a year ago

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

M463 Over a year ago

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!

Ali · Accepted Answer · 2022-08-12 20:54:24Z

1

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

answered Aug 12, 2022 at 20:54

Ali

112 bronze badges

Collectives™ on Stack Overflow

create a database using pyodbc

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related