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
– Wayne Werner, Commented Nov 14, 2014 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
– crmackey, Commented Nov 14, 2014 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
– Phil Cairns, Commented Nov 14, 2014 at 22:59

Community · Accepted Answer · 2017-05-23 12:27:26Z

11

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')

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Nov 14, 2014 at 23:14

Mike

3,8513 gold badges31 silver badges39 bronze badges

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

1 Comment

crmackey Over a year ago

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.

user1487861 · Accepted Answer · 2014-11-28 06:42:21Z

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, 2014 at 6:42

user1487861

4726 silver badges17 bronze badges

1 Comment

crmackey Over a year ago

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.

AJ AJ · Accepted Answer · 2021-08-05 09:57:19Z

1

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 5, 2021 at 9:57

AJ AJ

3554 silver badges14 bronze badges

Collectives™ on Stack Overflow

SQL query fails when using pyodbc, but works in SQL

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related