7

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.

Improve this question
6
  • You should look at examples using the SQLalchemy table objects on stack overflow. Commented Jul 19, 2015 at 14:21
  • 1
    Already done this, there are tons of "static" examples, where you create table and then loading the data. Furthermore there are dozens "copy/pasted" tutorials over the internet, that are actually the same. I think I can implement this by using direct driver API, like MySQLdb or PyMySQL... But how can I make this work with SQLalchemy? Could you provide some code snippet or just correct my code? Thanks. Commented Jul 19, 2015 at 14:56
  • if you have csv data it can easily be brought into mysql. if it must be in sqlalchemy you create a smaller niche of help. thus is the plight of those using orms and stuck in their glue trap. Commented Jul 19, 2015 at 17:17
  • Your methodology ie iterating through a bunch of data is perfectly sound. But you'll need to tell SQLAlchemy about the database structure ie you'll need the tables (or declarative objects). Perhaps sqlacodegen will point you in the right directon? Commented Jul 20, 2015 at 7:56
  • thanks for advice I'll check it. Additionally I'm reading "dataset" source code in order to understand how they got all things together. Commented Jul 20, 2015 at 7:58

3 Answers 3

Reset to default
6

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]))
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Awesome!!! You gave me the way... Yesterday I've read about metadata class in SA, but I'm not sure if I can pass table_name string to it. Check it also.
Not sure that I've understood completely your comment but I've updated the answer trying to clarify.
Thanks. What I've tried to say, that I didn't clearly understand "how and where" to pass table_ name string. Now I can see that meta.tables[table_name string] accepts it. The problem is that I'm new to OOP (especially with Python) and SQLAlchemy concepts. By this reason some of my questions may seem too dumb to others. Still I'm working hard to improve my skills and learn more stuff. Again thanks for your time and answer.
You're welcome! And come on, loads of very useful SO questions are so difficult for the guy who asks it and so simple for the guy who answers it. Still, they are useful. Also for other people who end up having similar doubts and find them... Anyway, not sure if your question is solved or not. If it is, please consider accepting my answer. If it isn't, just post your doubts (preferably as edits to your question....) Thanks.
0

This is the solution I used:

from sqlalchemy import create_engine, MetaData
# from myplace import load_current_system_data() and other relevant functions

def alchemy_current():
    system_data = load_current_system_data()
    engine = create_engine('mysql+pymysql://user:password@localhost/your_db_name')
    meta = MetaData()
    meta.reflect(bind=engine)
    conn = engine.connect()
    for table_name in ordered_tables:
        conn.execute(meta.tables[table_name].insert().values(system_data[table_name]))
    conn.close()
    # engine.dispose()

    print("System's Data successfully loaded into Database!")

All this should work, assuming that one have:

  1. Existing mysql database.
  2. An organized data as I've described prior in this question.
  3. An ordered table_name_list in order to keep referential integrity and avoid FK problems.
Improve this answer

Comments

0

you could import text:

from sqlalchemy.sql import text

and then execute the following

    conn.execute(text("mysql commands goes in here"))

example for insert:

    conn.execute(text("INSERT INTO `table_name`(column_1,column_2,...) VALUES (value_1,value_2,...);"))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.