CODEWITHSUNDEEP
pythonmysqlsqlinsertpyodbc
David Dav
David Dav

Reputation: 51

execute sql statements read from a file

I want to load text file, which is containing tonnes of sql queries(inserts) and execute it by using Python(PYODBC)

My current code looks like:

cursor = cnxn.cursor()
with open('C:\Python_Script_Test\INSERTS.txt','r') as file:
    var1 = file.read().replace("\r\n","")
var2 = var1

cursor.execute(var2)
file.close()

there are more then 5000 lines in that file, and sample INSERT from it looks like:

Insert into x (q, w, e, r, t, y, u, i, o, p, a, s, d, f, g, h, j)
  VALUES (582, 'GA26', 'TMO ', 'xxx', '[email protected]', '', 'NULL', 'NULL', 
  '', '', '', '', '', '', '', '', NULL);

error:

pyodbc.ProgrammingError: ('42000', 
  "[42000] [MySQL][ODBC 3.51 Driver][mysqld-5.6.13]
  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 
  'insert into x
   (z, b, c, d, e' at line 3 (1064) (SQLExecDirectW)")

Edit:

Antoher idea:

Ok, there is no errors for this moment. Current code looks like: previous = '' for sql_insert in open('C:\Python_Script_Test\x.txt','r'): y = sql_insert.strip().replace("\r\n",";")
if y.startswith('insert'):
print previous if previous.endswith(';'): print 'test\r\n' cursor.execute(previous)
previous = y
else: previous += y

BUT, there is no changes on table in database...

Upvotes: 1

Views: 8396

Answers (2)

msw
msw

Reputation: 43497

I understand that you must use Python, but it isn't clear that you must execute the entire file at once. Since your input file is already divided into lines, what about:

cursor = cnxn.cursor()
with open('C:\Python_Script_Test\INSERTS.txt','r') as inserts:
    for statement in inserts:
        cursor.execute(statement)

Also, using file as a variable name in Python is a Bad Idea because it overwrites the file() built-in.

Upvotes: 2

Michał Niklas
Michał Niklas

Reputation: 54302

It seems that you forgot closing ) between j and VALUES.

At first try executing such line using specialized SQL editor from vendor.

I'm not sure if MySQL ODBC is able to execute many statements at once. If not then you will have to parse input file. If each INSERT is in separate file the it is easy:

for line in open(...):
    sql_insert = line.strip()
    if sql_insert:
        cursor.execute(sql_insert)

If not then you must parse it other way. Maybe split it into statements by using );. It depends from your input file.

Upvotes: 0

Related Questions