python Commands out of sync; you can't run this command now"

Question

I am trying to run this small python script which inserts data to mySql database under MacOs however it gives me following error:

File "inserter.py", line 58, in cursor.execute('SELECT countryId from searcher_country');

File "build/bdist.macosx-10.9-intel/egg/MySQLdb/cursors.py", line 205, in execute

File "build/bdist.macosx-10.9-intel/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler _mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

Script :

from openpyxl import load_workbook;
import MySQLdb;
import random;

connection = MySQLdb.connect(host='localhost',user='root',passwd='testpassword',db='test');
cursor = connection.cursor();
fileLoc = "data.xlsx";

wb = load_workbook(fileLoc);
ws = wb.active;

#outFile = raw_input("Where do you want your count to go? ");

countryCountProc = """ CREATE PROCEDURE countProc (OUT param1 INT)
BEGIN
    SELECT COUNT(*) INTO param1 FROM searcher_country;
END;"""

readyFunction = """
CREATE FUNCTION ready(id INT)
returns CHAR(50)
return 'The program has been initialized';
"""

cursor.execute(countryCountProc);
cursor.execute(readyFunction);
outFile = '/tmp/testingCount';

print cursor.execute('CALL countProc(@a); SELECT @a INTO OUTFILE \'{0}\';'.format(outFile));
yearIndex = 2;
while True:
    value = str(ws.cell(row=1,column=yearIndex).value);
    try:
        sql = 'INSERT INTO searcher_year (year) values (\'{0}\')'.format(value.encode("utf8"))
        cursor.execute(sql);
    except Exception as e:
        print sql
        print e
    yearIndex = yearIndex + 1
    if value == '2011':
        print yearIndex-1;
        break;

countryIndex = 2;
while True:
    value = ws.cell(row=countryIndex,column=1).value.replace('\'','\\\'');
    try:
        sql = 'INSERT INTO searcher_country (country) values (\'{0}\')'.format(value.encode("utf8"))
        cursor.execute(sql);
    except Exception as e:
        print sql
        print e
    countryIndex+=1
    if value == "Saba":
        print countryIndex-1;
        break;

cursor.execute('SELECT countryId from searcher_country');
results = [int(item[0]) for item in cursor.fetchall()]
minCountryId = min(results);
maxCountryId = max(results);

cursor.execute('SELECT yearId from searcher_year');
results = [int(item[0]) for item in cursor.fetchall()]
minYearId = min(results);
maxYearId = max(results);

for i in xrange(500):
    yearId = random.randint(350,370);
    countryId = random.randint(3800,3820)
    data = round(random.random()*10,2);
    sql = 'INSERT INTO searcher_data (country_id,year_id,stat) values ({0},{1},\'{2}\')'.format(countryId,yearId,str(data))
    cursor.execute(sql);

connection.execute('SELECT ready(1) INTO OUTFILE {0}'.format(outFile))
connection.commit();
cursor.close();

Use query parameters instead of embedding them. At the very least, if you do so, you need to [escape](http://stackoverflow.com/questions/3617052/escape-string-python-for-mysql) them. — ivan_pozdeev, Feb 17 '16 at 03:01

score 0 · Answer 1 · answered Feb 12 '21 at 09:02

0

You are not releasing the cursor after query execution. Kindly include connection.commit(); cursor.close(); after each execution stateement

answered Feb 12 '21 at 09:02

lewis machilika

819
2
11
25

Do you have an example on how this were to play out in some code? An easy to read code sample with the use of both cursor.close and connection.commit? – Lar Feb 01 '22 at 16:23
```cursor.execute(countryCountProc); connection.commit(); cursor.execute(readyFunction); connection.commit(); cursor.close();``` – lewis machilika Feb 02 '22 at 09:23
Thank you tons! However it's still not working, is there any way I could send you my code and then you check it out? I think it's still on my profile. – Lar Feb 03 '22 at 01:14
Yaaa sure you can send through twitter handle *@lewis_machilika* – lewis machilika Feb 07 '22 at 06:23

python Commands out of sync; you can't run this command now"

1 Answers1

Linked