Format Python List to SQL script ?

Question

I have a list need to be format it to SQL scripte

list = 
    [['11', ' 0', " 'MMB'", " '2 MB INTERNATIONAL'", ' NULL', ' NULL', ' 0\n'], 
     ['12', ' 0', " '3D STRUCTURES'", " '3D STRUCTURES'", ' NULL', ' NULL', ' 0\n'],
     ['13', ' 0', " '2 STRUCTURES'", " '2D STRUCTURES'", ' NULL', ' NULL', ' 0\n'],

To sql script like this:

INSERT INTO `Tbl_ABC` VALUES (11, 0, 'MMB', '2 MB INTERNATIONAL', NULL, NULL, 0)
INSERT INTO `Tbl_ABC` VALUES (12, 0, '3D STRUCTURES', '3D STRUCTURES', NULL, NULL, 0)
INSERT INTO `Tbl_ABC` VALUES (13, 0, '2 STRUCTURES', '2D STRUCTURES', NULL, NULL, 0)

This I have try

import pickle
import re
#RX = re.compile(r'^.*?\(\d+,\s0,.*\s0\)\s*$')

outfile = open('destination.sql', 'wb')
data = []
for ln in open('source.sql', 'r').xreadlines():
    replace1 = ln.replace("INSERT INTO `Tbl_ABC` VALUES (", "")
    replace2 = replace1.replace(")", "")
    list_replace = replace2.split(',')
    data.append(list_replace) 
destinationdata = [d for d in data if d[1] == ' 0' and d[6]==' 0\n']#print '%s ,%s' % (list_replace[1], list_replace[6])

    #start write line to destination.sql
     #if RX.match(ln):        

pickle.dump(destinationdata, outfile)
outfile.close()

Thank for your help !

Answer 1

Iterate over each element in the list
  Unpack the element (which is also a list) into its fields
  Generate a SQL line from these fields

The simplest and ugliest way that just gets the job done is:

list = [
    ['11', ' 0', " 'MMB'", " '2 MB INTERNATIONAL'", ' NULL', ' NULL', ' 0'], 
    ['12', ' 0', " '3D STRUCTURES'", " '3D STRUCTURES'", ' NULL', ' NULL', ' 0'],
    ['13', ' 0', " '2 STRUCTURES'", " '2D STRUCTURES'", ' NULL', ' NULL', ' 0']]

for elem in list:
    print 'INSERT INTO \'Tbl_ABS\' VALUES (%s, %s, %s, %s, %s, %s, %s)' % tuple(elem)

Note I cleaned the '\n' after the 0 (adapt accordingly if it's needed there). This is OK for one-off scripts. For more serious reporting and conversions, look at some template libraries to separate your data and presentation.

Answer 2

Basically, this should work

print "\n".join(["INSERT INTO `Tbl_ABC` VALUES ("+",".join(x).strip()+")" for x in list1])

result

INSERT INTO `Tbl_ABC` VALUES (11, 0, 'MMB', '2 MB INTERNATIONAL', NULL, NULL, 0)
INSERT INTO `Tbl_ABC` VALUES (12, 0, '3D STRUCTURES', '3D STRUCTURES', NULL, NULL, 0)
INSERT INTO `Tbl_ABC` VALUES (13, 0, '2 STRUCTURES', '2D STRUCTURES', NULL, NULL, 0)

Adjust it if there is minor difference, depends on your need.

ps: I intentionally changed list to list1 in my code, because overriding built-in function is not good idea.

Answer 3

Technically it's fairly easy to take a list and convert it into sql by hand, but .... you might consider checking out SQLAlchemy http://www.sqlalchemy.org. It is I feel really superb. It will do all your conversions for you from Python objects into database table rows, and can retrieve python objects back from the tables.

You can do things like this:

# retrieve each row from the company table and print it :-O
for company in session.query(Company):
   print company.name + " " + company.address

# add a new company to the company table:
company = Company("Fred Flintstone", "The Quarry")
session.add(company)
session.commit()