Python - Execute multiple SQL query from file

Question

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

check the format for queries in the file.Add it to the question. — ns15
– ns15, Commented May 2, 2017 at 6:34
Have your code display the queries as it executes them so you will know which query is causing the problem, then edit your question to show us what that query looks like. — Gord Thompson
– Gord Thompson, Commented May 2, 2017 at 12:27
added the requested information of SQL file & print statement in the question. — Rajeev
– Rajeev, Commented May 4, 2017 at 0:36
Umm, in order to see the command that fails you need to print it before you try to execute it. — Gord Thompson
– Gord Thompson, Commented May 4, 2017 at 12:42
that's how I have printed i.e. before executing the SQL, looks like the queries are getting executed sequentially but note sure since the SQL count output is displayed as "Info : (7,)" format and that is causing the above said error while using c.fetchall() ? — Rajeev
– Rajeev, Commented May 4, 2017 at 19:53

brasofilo · Accepted Answer · 2017-05-06 01:23:47Z

1

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)

edited May 6, 2017 at 1:23

brasofilo

26.2k15 gold badges96 silver badges189 bronze badges

answered May 5, 2017 at 20:54

Rajeev

1,0613 gold badges14 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Python - Execute multiple SQL query from file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related