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 !

I can't figure out the snide comments and answers. Looks like a valid question, perhaps lacking in English prose. A much more logical question than many others here on SO — Eli Bendersky
– Eli Bendersky, Commented Nov 30, 2009 at 4:57
@eliben: the initial question simply asked for a python script to do a specific task. Had python showed they had already attempted this in any way and was looking for assistance, then I couldn't agree more. — Russell
– Russell, Commented Nov 30, 2009 at 5:13

Eli Bendersky · Accepted Answer · 2009-11-30 04:59:14Z

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.

edited Nov 30, 2009 at 4:59

answered Nov 30, 2009 at 4:52

Eli Bendersky

276k92 gold badges372 silver badges427 bronze badges

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

Comments

YOU · Accepted Answer · 2009-11-30 04:59:28Z

1

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.

answered Nov 30, 2009 at 4:59

YOU

124k34 gold badges191 silver badges222 bronze badges

1 Comment

tree em Over a year ago

Just one line you give the ways. :)

Hugh Perkins · Accepted Answer · 2009-11-30 05:29:13Z

0

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()

answered Nov 30, 2009 at 5:29

Hugh Perkins

8,7028 gold badges67 silver badges78 bronze badges

1 Comment

tree em Over a year ago

thanks for your info.AnyWhere what I do is make a database migration from MS access to SQL.get data from access and insert to MySQL with Conditions. :)

Collectives™ on Stack Overflow

Format Python List to SQL script?

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related