CODEWITHSUNDEEP
sqlsql-serverpython-2.7pymssql
Rajeev
Rajeev

Reputation: 1061

Python - Execute multiple SQL query from file

I am trying to execute SQL query from a file in Python 2.7.13 and getting the following error while displaying the resultset. The SQL statements in the file are simple like count(*) from table but if this logic works I need to replace it with complex queries.

Error

Info : (7,)
Traceback (most recent call last):
  File "SQLserver_loop.py", line 19, in <module>
    fields = c.fetchall()
  File "pymssql.pyx", line 542, in pymssql.Cursor.fetchall (pymssql.c:9352)
pymssql.OperationalError: Statement not executed or executed statement has no re
sultset

Python Script:

import pymssql

conn = pymssql.connect(
    host=r'name',
    user=r'user',
    password='credential',
    database='Test')

c = conn.cursor()


fd = open('ZooDatabase.sql', 'r')     # Open and read the file as a single buffer

sqlFile = fd.read()

fd.close()


sqlCommands = sqlFile.split(';')        # all SQL commands (split on ';')


for command in sqlCommands:         # Execute every command from the input file

     c.execute(command)

     fields = c.fetchall()

     for row in fields:

      print "Info : %s " % str(row)

c.close()

conn.close()

Error Message

    **SQL File - ZooDatabase.sql**

    select count(*) from emp2;

    select count(*) from emp1; 

**Error Log with SQL print statement output:**

   C:\Python27\pycode>python SQLserver_loop.py
    SELECT count(*) FROM emp2
    Info : (7,)

    SELECT count(*) FROM emp1
    Info : (7,)

    Traceback (most recent call last):
      File "SQLserver_loop.py", line 20, in <module>
        fields = c.fetchall()
      File "pymssql.pyx", line 542, in pymssql.Cursor.fetchall (pymssql.c:9352)
    pymssql.OperationalError: Statement not executed or executed statement has no re
    sultset

Upvotes: 2

Views: 8599

Answers (1)

Rajeev
Rajeev

Reputation: 1061

fields = c.fetchall() was causing the error I commented it and works fine now.

for command in sqlCommands:

     #print command
     c.execute(command)
     #fields = c.fetchall()
     for row in c:
      print (row)

Upvotes: 1

Related Questions