Populate MySQL table with db file

Question

I have a xx.db file which I access with the keys, printing the keys to the screen for checking. There are many levels of keys, I'm showing the top level here.

    for filename in filenames:
        with contextlib.closing(shelve.open(filename, flag = "w")) as data:     
prdata = data['all'].keys()

print prdata I have a table created like this in Python:

tbl= """CREATE TABLE IF NOT EXISTS table2 (
      `class` varchar(30) default NULL,
      `name` varchar(120) default NULL,
       )""" 
cursor.execute(tbl)

I would like to load the output of the first collection of data (from .db file) into the created MySQLdb table. How should I proceed? Do I first need to create a text file from the db file? I have read that LOAD DATA INFILE is a fast way to load data into the database from a text file. Since there is a lot of data in the db, it would be faster if I can skip making a text file first.

edit: Have tried this, provides syntax error"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 'prdata INTO TABLE table2 \n\t FIELDS TERMINATED BY ' '' . I do not understand the syntax documentation correctly and also how to enclose it in Python.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]

for filename in filenames: with contextlib.closing(shelve.open(filename, flag = "w")) as data:
prdata = data['all'].keys()

cursor.execute("""LOAD DATA INFILE prdata INTO TABLE table2 FIELDS TERMINATED BY ' ' """)

Update: Decided to go with INSERT INTO table2 (class,name) VALUE (%s,%s) works fine

kchimba · Accepted Answer · 2013-06-14 15:52:20Z

1

When a connection is created, do not forget to add the local_infile parameter

connection = MySQLdb.connect(host="localhost", user="appuser", passwd="",
db="test", local_infile = 1)

otherwise this error will appear:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

answered Jun 14, 2013 at 15:52

kchimba

112 bronze badges

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

Comments

bpgergo · Accepted Answer · 2012-01-20 16:37:54Z

0

you can do the loading from python as well:

cursor.executemany(
      """INSERT INTO table2 (class, name)
      VALUES (%s, %s)""",
      [
      ("class1", "name1" ),
      ("class2", "name2" ),
      ("class3", "name3" )
      ] )

docs http://mysql-python.sourceforge.net/MySQLdb.html

answered Jan 20, 2012 at 16:37

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

1 Comment

Tom Over a year ago

Sure, but the actual data resides in a .db file. So I was hoping to pull from that, rather than manually inserting the "class1","name1" etc.

Collectives™ on Stack Overflow

Populate MySQL table with db file

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related