bulk insert in db table

Question

Hello,

I'm having an array I want to insert in a single query in a table. Any idea?

Answer 1

If you are using PostgreSQL, you can use the COPY statement.

Answer 2

If you are using dbapi to access the database, then the connection.executemany() method works.

con.executemany("INSERT INTO some_table (field) VALUES (?)", [(v,) for v in your_array])

The format of the bindparameter depends on the database, sqlite uses ?, mysql uses %s, postgresl uses %s or %(key)s if passing in dicts. To abstract this you can use SQLAlchemy:

import sqlalchemy as sa
metadata = sa.MetaData(sa.create_engine(database_url))
some_table = Table('some_table', metadata, autoload=True)
some_table.insert([{'field': v} for v in your_array]).execute()

Answer 3

As uneffective (individual inserts) one-liner:

data = [{'id': 1, 'x': 2, 'y': 4}, {'somethig': 'other', 'otherfield': 5, 'y': 6}]
table = 'table_name'
connection = connect() # DBAPI connection

map(connection.cursor().execute, ('INSERT INTO %s (%s) VALUES (%s)' % ('table', ','.join(x), ','.join(['%s'] * len(x))) for x in data), (x.values() for x in data))