Sql script when run vi mysql python gives error

Question

I wrote a python script to execute my sql script. When i manually run this sql script using source it doesn't give me any error just warning and it fully executes as :

mysql> source the_actual_script.sql
Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.29 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

But when i try and run it via my python code :

import MySQLdb

db = MySQLdb.connect("aws.eu-central-1.rds.amazonaws.com","prod_user","pass","db_name")
cursor = db.cursor()
for line in open("/home/ubuntu/PATCHER/the_check_query.sql"):
  print  cursor.execute(line)
db.close()

It gives me errors :

test.py:8: Warning: PROCEDURE saas_neopostsa.temp_prod does not exist
  cursor.execute(line)
test.py:8: Warning: PROCEDURE saas_neopostsa.temp_prod_neopost does not exist
  cursor.execute(line)
test.py:8: Warning: PROCEDURE saas_neopostsa.temp_prod_neopostsa does not exist
  cursor.execute(line)
test.py:8: Warning: Unknown table 'temp_identity_neopostsa'
  cursor.execute(line)
test.py:8: Warning: Unknown table 'temp_identity'
  cursor.execute(line)
Traceback (most recent call last):
  File "test.py", line 8, in <module>
    cursor.execute(line)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1065, 'Query was empty')

can someone help me what's wrong ?

Possible duplicate of [reading external sql script in python](http://stackoverflow.com/questions/19472922/reading-external-sql-script-in-python) — Jean-François Fabre, Jan 03 '17 at 05:11

pokeybill · Answer 1 · 2017-01-19T19:20:31.550

The MySQL ER_EMPTY_QUERY or Empty Query error indicates the query being executed is either empty or a value which MySQL cannot convert to a string. I'd review the script file and be sure each line has an SQL statement.

for line in open("/home/ubuntu/PATCHER/the_check_query.sql"):
  print  cursor.execute(line)
db.close()

You should consider the with keyword for your file operations (assuming your version supports this keyword). I'd probably perform this with some sort of validation, something like:

import MySQLdb

db = MySQLdb.connect("aws.eu-central-1.rds.amazonaws.com","prod_user","pass","db_name")
cursor = db.cursor()

# Use with to open the file,'rb' for compatibility
with open("/home/ubuntu/PATCHER/the_check_query.sql",'rb') as infile:
    # Using list comprehension, read lines ecluding non-empty
    # or lines containing only a newline ('\n') character
    lines = [ln for ln in infile.readlines() if ln and ln != '\n']

# The file is now closed and you have a (somewhat) curated command list
for line in lines:
    try:
        cursor.execute(line)
    except:
        print line  # see what exactly is being executed
        # Other exception stuff here

db.close()

This will, at least, provide you with a clear picture of which command is resulting in the Empty Query error.

Is there an empty line or doubled semicolon (;;) anywhere in the script file after the 5th test? I'm a little confused since the stack trace is showing cursor.execute(line) and not print cursor.execute(line) as in your code sample and doesn't match the line number for the sample. Is the code provided the full relevant section of code which actually generated that stack trace?

It says mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$' at line 1") — Kittystone, Jan 04 '17 at 09:34

Sql script when run vi mysql python gives error

1 Answers1