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.
On Monday, July 20, 2015 at 1:37:14 AM UTC+3, Michael Bayer wrote:
>
>
>
> 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
>
>
>
>
> 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. ))))
>
>
> On Sunday, July 19, 2015 at 11:46:46 PM UTC+3, Michael Bayer wrote:
>>
>>
>>
>> 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_enginefrom 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>
>> 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 [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>>
>> --
> 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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
