1

I am trying to insert my data into my ms access sql connection. How do I write an insert statement where I do not have to input all the column names and "?" values.

For example from https://code.google.com/p/pyodbc/wiki/Cursor :

Executes the same SQL statement for each set of parameters. seq_of_parameters is a sequence of sequences.

params = [ ('A', 1), ('B', 2) ]
executemany("insert into t(name, id) values (?, ?)", params)

This will execute the SQL statement twice, once with ('A', 1) and once with ('B', 2).

I have a table with 22+ columns and i do not want to write "t(names,id,..etc)" and "values (?,?,...,?). I just want to be able to throw in my parameters without doing that. Any suggestions?

collarblind
  • 4,549
  • 13
  • 31
  • 49
  • If you are able to get ms access working with sqlalchemy (see eg http://stackoverflow.com/questions/9233912/connecting-sqlalchemy-to-msaccess), you can just use `to_sql` – joris Jul 09 '15 at 20:01

1 Answers1

3

I would recommend to use sqlalchemy. Its core language is database API independent (so the same interface for all widely used sql database such like MySQL, sqlite, cx_oracle, etc.) and pandas uses it as the default package to interact with sql database. You don't have to type those SQL statement manually, and df.to_sql will take care of it. Sample code looks like this.

from sqlalchemy import create_engine
import pandas as pd

# create an engine with the following syntax
# mssql (MicroSoft SQL)
# pyodbc (the python database api to your sql)
# username, password, database_name
ms_sql_engine = create_engine('mssql+pyodbc://my_username:my_password@my_database_name')

# I don't have MS sql installed in my PC, so just use sqlite in memory here
my_engine = create_engine('sqlite:///:memory:')


# your data in pandas dataframe
df = pd.DataFrame(np.random.randn(100, 5), columns='A B C D E'.split())

# write to sql
# replace engine with your own MS sql engine
df.to_sql('my_frame', my_engine)
Jianxun Li
  • 24,004
  • 10
  • 58
  • 76
  • sqlalchemy does not support ms access. do you know how to install the dialect for it? https://bitbucket.org/zzzeek/sqlalchemy-access/overview – collarblind Jul 09 '15 at 19:58
  • That dialect is read-only, so it wouldn't work for inserts anyway. Unfortunately, I'm not familiar enough with Access to know how to potentially query system objects in the fashion you want. – FlipperPA Jul 09 '15 at 21:01
  • Update, September 2019: The sqlalchemy-access dialect has been resurrected, mainly to help with `to_sql`. Details [here](https://pypi.org/project/sqlalchemy-access/). – Gord Thompson Sep 05 '19 at 23:30