CODEWITHSUNDEEP
pythonsqlsql-serverpyodbc
crmackey
crmackey

Reputation: 349

SQL query fails when using pyodbc, but works in SQL

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

Upvotes: 5

Views: 11016

Answers (3)

AJ AJ
AJ AJ

Reputation: 325

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)

Upvotes: 1

user1487861
user1487861

Reputation: 472

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

Upvotes: 2

Mike
Mike

Reputation: 3811

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

Upvotes: 11

Related Questions