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

1,401 views
Skip to first unread message

Ivan Evstegneev

unread,
Jul 19, 2015, 11:02:13 AM7/19/15
to sqlal...@googlegroups.com

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.

Ivan Evstegneev

unread,
Jul 19, 2015, 4:33:03 PM7/19/15
to sqlal...@googlegroups.com
Someone? Please.

Mike Bayer

unread,
Jul 19, 2015, 4:46:46 PM7/19/15
to sqlal...@googlegroups.com


On 7/19/15 4:33 PM, Ivan Evstegneev wrote:
Someone? Please.

On Sunday, July 19, 2015 at 6:02:13 PM UTC+3, Ivan Evstegneev wrote:

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.

Your example illustrates some basic misunderstandings about how the insert() function is used; you need to pass it the kinds of objects it expects, in this case not the string name of a table but rather a Table object, as well as data as key/value arguments via the values() method.

Re: wasting all day, if by "online examples" you mean that you're just reading blog posts and things trying to find a code snippet that matches what you're doing, then yes, that's a wasteful use of time :)   You'd be better off learning the insert() construct fully so that you can use it in a way specific to what you're doing, and that means giving the Core tutorial a good read http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html.    That said, dataset is built on SQLAlchemy, so if it does what you need, there's no reason you need to switch off of it.



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.
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Ivan Evstegneev

unread,
Jul 19, 2015, 4:58:57 PM7/19/15
to sqlal...@googlegroups.com
Thanks for  the reply. 

>>if by "online examples" you mean that you're just reading blog posts and things trying to find a code snippet that matches what you're doing, then yes, that's a wasteful >>use of time :) 

Wrong! ^_^  I'm not a lazy one. 

I'm also reading sqla official documentation, but most of the time it's difficult to follow. Maybe cause of lack of the experience. 

Furthermore, I got the "dataset" source code in order to understand how they combined all the stuff together.

Additionally I saw, many tutorials with python drivers like mysqldb, pymysql, where you need just to construct a string (using "format") and then pass it as sql query to make the job done.


Still, haven't seen any examples of how to use sqlalchemy, in order to load the data in the way I've described. Maybe my attitude about "doing stuff this way" is wrong...
Duh... feeling dumb... 



Still, many thanks for your answer. )))) 

Ivan Ogasawara

unread,
Jul 19, 2015, 5:04:37 PM7/19/15
to sqlal...@googlegroups.com
Hi Ivan,

Maybe you may iterate over the system_data[table_name] to execute your conn.execute.

Mike Bayer

unread,
Jul 19, 2015, 6:37:14 PM7/19/15
to sqlal...@googlegroups.com


On 7/19/15 4:58 PM, Ivan Evstegneev wrote:
Thanks for  the reply. 

>>if by "online examples" you mean that you're just reading blog posts and things trying to find a code snippet that matches what you're doing, then yes, that's a wasteful >>use of time :) 

Wrong! ^_^  I'm not a lazy one. 

I'm also reading sqla official documentation, but most of the time it's difficult to follow. Maybe cause of lack of the experience. 

Furthermore, I got the "dataset" source code in order to understand how they combined all the stuff together.

Additionally I saw, many tutorials with python drivers like mysqldb, pymysql, where you need just to construct a string (using "format") and then pass it as sql query to make the job done.

sure, those are the python drivers.   SQLAlchemy rides on top of those, like in the diagram at http://docs.sqlalchemy.org/en/rel_1_0/intro.html.

That diagram is at the base of my "Intro to SQLAlchemy" video which might be worth it if you have the time, at http://www.sqlalchemy.org/library.html#introductiontosqlalchemy

Ivan Evstegneev

unread,
Jul 21, 2015, 6:20:15 AM7/21/15
to sqlal...@googlegroups.com
Hello again )))

I solved my problem (after thorough re-reading of SA docs)

Here is the code:

import (all the relevant import here)


def alchemy_current():
    system_data = load_current_system_data()
    engine = cretae_engine('mysql+pymysql://user:pass@locahost/my_db')
    meta = MetaData()
    meta.reflect(bind=engine)
    conn = engine.connect()
   
    for table_name in ordered_tables_list
        conn.execute(meta.tables[table_name].insert().values(system_data[table_name]))
   
    conn.close()
    # engine.dispose()
    print("System's Data successfully loaded!")


In case some have any improvement advice, will be glad to hear.


Ivan.
Reply all
Reply to author
Forward
0 new messages