How can I create a database using pymssql

Question

Im trying to create a database using pymssql and im getting this error.

cur.execute("CREATE DATABASE %s;" % self.getsql('dbname'), conn)

gives

*** OperationalError: (226, 'CREATE DATABASE statement not allowed within multi-
statement transaction.DB-Lib error message 226, severity 16:\\nGeneral SQL Serve
r error: Check messages from the SQL Server\\n')

What does this mean ??

Try cur.execute("create database %s" % (self.getsql('dbname'),), conn), also try it without the getsql call. — Naftuli Kay
– Naftuli Kay, Commented Mar 29, 2012 at 1:57
If you are running other statements before your CREATE DATABASE one, you may need to commit() the current transaction before running it. — Amber
– Amber, Commented Mar 29, 2012 at 1:59

jagguli · Accepted Answer · 2012-03-29 21:58:39Z

13

The issue was that cur.execute starts a transaction every time, but 'CREATE DATABASE' operation cannot be excuted within a transaction

http://social.msdn.microsoft.com/Forums/pl/adodotnetdataproviders/thread/594ff024-8af6-40b3-89e0-53edb3ad7245

>>> connection.autocommit(True)
>>> cursor = connection.cursor()
>>> cursor.execute("CREATE DATABASE Foo")
>>> connection.autocommit(False)

This to works. Strangely its not documented in pymssql ... hmmm

answered Mar 29, 2012 at 21:58

jagguli

7011 gold badge7 silver badges22 bronze badges

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

3 Comments

pianoJames Over a year ago

FWIW, pyodbc also automatically starts transactions, so setting autocommit True solves this for pyodbc too.

jpspesh Over a year ago

yes this also worked for me. Strangely if you toggle autocommit before and after the create statement it does work.

Anu Over a year ago

How would you provide the server, user, password ? in connection

Collectives™ on Stack Overflow

How can I create a database using pymssql

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related