SQL query fails when using pyodbc, but works in SQL

Question

I am having some trouble with what should be a very simple script. I am just trying to create a new SQL Server database using the Python pyodbc module. The "sqlcommand" parameter I am attempting to pass in works perfectly when I execute it in SQL Server 2012, but it is failing from this python script. Not sure what is going wrong, anyone have any ideas?

import pyodbc, os

def create_db(folder, db_name):
    unc = r'\\arcsql\SDE\{0}'.format(folder)
    if not os.path.exists(unc):
        os.makedirs(unc)
    full_name = os.path.join(r'E:\SDE', folder, db_name)
    conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database=master; Trusted_Connection=yes", automcommit=True) 
    cursor = conn.cursor()
    sqlcommand = """USE [master]
GO
CREATE DATABASE [{0}] ON PRIMARY
( NAME = N'{0}', FILENAME = N'{1}.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'{0}_log', FILENAME = N'{1}_log.ldf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 10%)
GO

USE [{0}]
GO""".format(db_name, full_name)
    print sqlcommand

    cursor.execute(sqlcommand)
    print 'Created "{0}"'.format(db_name)

if __name__ == '__main__':
    #test
    create_db('_test', 'py_db_test')

and the errors:

>>> 
USE [master]
GO
CREATE DATABASE [py_db_test2] ON PRIMARY
( NAME = N'py_db_test2', FILENAME = N'E:\SDE\_test\py_db_test2.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'py_db_test2_log', FILENAME = N'E:\SDE\_test\py_db_test2_log.ldf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 10%)
GO

USE [py_db_test2]
GO

Traceback (most recent call last):
  File "C:/Users/calebma/Desktop/create_sql_db.py", line 40, in <module>
    create_db('_test', 'py_db_test2')
  File "C:/Users/calebma/Desktop/create_sql_db.py", line 35, in create_db
    cursor.execute(sqlcommand)
Error: ('08004', "[08004] [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'py_db_test2' does not exist. Make sure that the name is entered correctly. (911) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'GO'. (102)")
>>>

Well, I would look at your `py_db_test2` database. Does it exist? As an aside - does this work with `pypyodbc`? — Wayne Werner, Nov 14 '14 at 22:55
Thanks for the reply. The database does not exist because it is bombing out before it creates it. I was able to copy and paste the print out of the sqlcommand in SQL Server and that worked to create the db (without the 2 of course). It is saying there is an incorrect syntax near "GO" in the SQL command, but the syntax looks fine to me. And I did not know about pypyodbc. I am going to download that module now and see if it gives me better luck. — crmackey, Nov 14 '14 at 22:59
How about sending a series of statements instead of putting them all in one, separated by "GO"? So that's one "CREATE DATABASE" statement, and then maybe disconnect from master and reconnect to py_db_test2? — Phil Cairns, Nov 14 '14 at 22:59

score 11 · Accepted Answer · edited May 23 '17 at 12:27

GO is a batch terminator in SQL Server Management Studio. It doesn't make any sense in pyodbc. Instead, issue separate commands from your script.

Updated Code:

import pyodbc, os

def create_db(folder, db_name):
    unc = r'\\arcsql\SDE\{0}'.format(folder)
    if not os.path.exists(unc):
        os.makedirs(unc)
    full_name = os.path.join(r'E:\SDE', folder, db_name)
    conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database=master; Trusted_Connection=yes", automcommit=True) 
    cursor = conn.cursor()
    sqlcommand = """
CREATE DATABASE [{0}] ON PRIMARY
( NAME = N'{0}', FILENAME = N'{1}.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'{0}_log', FILENAME = N'{1}_log.ldf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 10%)
""".format(db_name, full_name)
    print sqlcommand

    cursor.execute(sqlcommand)
    print 'Created "{0}"'.format(db_name)

    # Do stuff in the new database
    conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database={0}; Trusted_Connection=yes".format(db_name), automcommit=True) 

if __name__ == '__main__':
    #test
    create_db('_test', 'py_db_test')

Thanks Mike! This worked! For some reason, passing in the autocommit parameter into the connection did not work, so I explicitly had to set conn.autocommit = True and it worked with your edits. — crmackey, Nov 14 '14 at 23:39

score 2 · Answer 2 · answered Nov 28 '14 at 06:42

2

For others that have a similar problem you are also missing the commit.

So, you can either use conn.autocommit = True at the start or conn.commit() after cursor.execute command

I struggled for ages trying to work out why my stored proc wouldn't run but wouldn't error - ended up that I was doing a cursor.commit() rather than a conn.commit()

answered Nov 28 '14 at 06:42

user1487861

420
6
16

Yes, that was giving me issues as well. Using conn.autocomit = True made it work for me but it was failing until I did that. – crmackey Dec 01 '14 at 14:13

score 0 · Answer 3 · answered Aug 05 '21 at 09:57

0

For others who have GO in their script and would like to run each script separately.

sqlCommands = sqlFile.split('GO;')
for command in sqlCommands:
    cursor.execute(command)

answered Aug 05 '21 at 09:57

AJ AJ

187
2
12

SQL query fails when using pyodbc, but works in SQL

3 Answers3

Linked