pETL fails to load data to MySQL DB

Question

I'm using petl package to build an ETL pipeline from Python 2.7 to MySQL5.6

My db connector is MySQLdb (mysql-python).

The following code fails to execute:

import MySQLdb as mdb
import petl as etl 

con = mdb.connect(host = '127.0.0.1', user = '<someuser>', passwd = '<somepass>') 
cur = con.cursor()     # get the cursor

cur.execute('DROP SCHEMA IF EXISTS petltest')
cur.execute('CREATE SCHEMA petltest')
cur.execute('USE petltest')

dat  = [{'id':1,'name':'One'},
 {'id':2,'name':'Two'},
 {'id':3,'name':'Three'}]

table =  etl.fromdicts(dat) # petl object

etl.todb(table,cur,'table',schema='petltest',create=True)

The error code is:

ProgrammingError: (1064, '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 \'"table" (\n\tid INTEGER NOT NULL, \n\tname VARCHAR(5) NOT NULL\n)\' at line 1')

This error occurs also when trying to create table separately or running petl.appenddb

How can I fix it / overcome the issue?

Thanks

Answer 1

Apparently the problem was the quotes style that PETL use. If you run:

cur.execute('SET SQL_MODE=ANSI_QUOTES')

before petl sql (petl.todb()) statements it executes well.