1

I'm new to SQL but OK with working things out in python.

I use pandas to read a data frame I call "lp" and fix the columns so that they match my Access table called "full" in my file "all lp.accdb". I want to append the entirety of "lp" to "full." I'd like to stick to python because I do a bunch of other stuff and I plan on doing it frequently.

conn = pypyodbc.connect(
r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
r"Dbq=all lp.accdb")

cur = conn.cursor()
cur.execute(r'INSERT INTO full(*) VALUES(lp) WHERE *')

cur.commit()
cur.close()
conn.close()

I think the line cur.execute(r'INSERT INTO full(*) VALUES(lp) WHERE *') is wrong. When I copied pypyodbc's Hello World Sample:

cur.execute('''INSERT INTO sellout(customer_name, product_name, price, volume, sell_time) VALUES(?,?,?,?,?)''',(u'江文', 'Huawei Ascend mate', '5000.5', 2, datetime.datetime.now()) )

I got syntax errors. I've also tried eschewing using a cursor at all and:

engine = sqlalchemy.create_engine('mysql+pyodbc://', creator = conn)
lp.to_sql('temp', engine, if_exists = 'append', index = False)

I'm not sure why or how to create an engine. I've checked panda's .to_sql documentation which made me think I needed to create an engine. I've also looked at sqlalchemy's documentation on dialects and it doesn't look like there's an option for pypyodbc. Not sure what to use in lieu of mysql.

My questions are "How do I fix my 'insert into' query?" or "How do I make this engine?" Thanks so much for your advice!

atl
  • 11
  • 2
  • @Gord Thompson Thanks for linking the other question; this answers my second question. Any thoughts about using 'cur.execute(r'INSERT INTO full(*) VALUES(lp) WHERE *')' rather than sqlalchemy? Ie., is the "insert into" query the write way to append a dataframe to Access table? and where am I going wrong in my syntax? Thanks – atl Sep 25 '17 at 15:09
  • Update, September 2019: The sqlalchemy-access dialect has been resurrected. Details [here](https://pypi.org/project/sqlalchemy-access/). – Gord Thompson Sep 05 '19 at 23:11

0 Answers0