How to load data into existing database tables, using sqlalchemy?

Question

I have my data loaded from excel files and organized as python dict where each key is database table name and its value is defined as list of dictionaries (the rows)

system_data = {table_name1:[{'col_1':val1, 'col2': val1...},
                            {'col_1':val2, 'col2': val2..}..],     
               table_name2:[{},{}..],[{},{}..]..}

This data needs to be loaded into existing database while picking table_names keys and values from system_data.

Additionally I use ordered_table list which I've created in specific order to avoid FK problems while data is being loaded.

Here is the code (one of the 1000 versions I've tried):

from sqlalchemy import create_engine
from sqlalchemy.sql import insert

def alchemy_load():
    system_data = load_current_system_data()
    engine = create_engine('mysql+pymysql://username:password@localhost/my_db')
    conn = engine.connect()
    for table_name in ordered_tables:
        conn.execute(insert(table_name, system_data[table_name]))
    print("System's Data successfully loaded into Database!")

This function yield a following error:

"TypeError: 'method' object is not iterable"

I've wasted almost all day on this stuff (((

All the online examples describe the situation when a user uses MetaData and creates its own tables... There is nothing about how to actually add data into existing tables.

There is a solution to my problem using "dataset" library.

The code:

import dataset

def current_data():
    db = dataset.connect(url='mysql+pymysql://user:pass@localhost/my_db')
    system_data = load_current_system_data()

    for table_name in ordered_tables:
        db[table_name].insert_many(system_data[table_name])
    print("System's Data successfully loaded into Database!")

BUT, I have no idea how to implement this code using sqlalchemy...

Any help will be appreciated.

Answer 1

One possible solution using SQLAlchemy metadata would go like this:

In [7]:

from sqlalchemy.schema import MetaData
meta = MetaData()
meta.reflect(bind=engine)

In [20]:

for t in meta.tables:
    for x in engine.execute(meta.tables[t].select()):
        print x
(1, u'one')
(2, u'two')
(1, 1, 1)
(2, 1, 2)
(3, 2, 0)
(4, 2, 4)

(I use select instead of insert and apply it to a silly database I've got for trials.)

Hope it helps.

EDIT: After comments, I add some clarification.

In MetaData(), tables is a dictionary of the tables in the schema. The dictionary goes by table name, and it is actually very similar to the dictionary in your code. You could iterate the metadata like this,

for table_name, table in meta.tables.items():
    for x in engine.execute(table.select()):
        print x

or, trying to adapt it into your code, you could just do something like,

for table_name in ordered_tables:
    conn.execute(meta.tables[table_name].insert_many(system_data[table_name]))