though really, all you need to do is process your tables in dependency order,
assuming you have no self-referential foreign keys.
if you iterate through tables as follows:
for table in metadata.sorted_tables:
<copy table>
you'd be moving the data in order of dependency.
http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=sorted_tables#sqlalchemy.schema.MetaData.sorted_tables
On Oct 10, 2014, at 11:33 AM, Michael Bayer <[email protected]> wrote:
> Assuming you're working with Oracle as is implied by your script, constraints
> can be disabled as in:
>
> http://www.dba-oracle.com/t_enabling_disabling_constraints.htm
>
> that is, for each table, issue a "DISABLE CONSTRAINT" for each FK constraint
> (using connection.execute("ALTER TABLE foo DISABLE CONSTRAINT xyz") ). These
> are listed in table.foreign_keys, however if you want to get at Oracle's
> generated names for each, you or you can use inspector.get_foreign_keys().
> See https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/DropEverything
> for an example of how to iterate through constraints.
>
>
>
> On Oct 10, 2014, at 11:03 AM, Eren Gölge <[email protected]> wrote:
>
>> Here for example the code I use to transfer a table from my src DB to dst DB
>>
>> #!/usr/bin/env python
>>
>>
>> import getopt
>> import sys
>> from sqlalchemy import create_engine, MetaData, Table
>> from sqlalchemy.orm import sessionmaker
>> from sqlalchemy.ext.declarative import declarative_base
>> #from joblib import Parallel, delayed
>>
>>
>> def make_session(connection_string):
>> engine = create_engine(connection_string, echo=True, encoding='utf8')
>> Session = sessionmaker(bind=engine)
>> Base = declarative_base()
>> return Session(), engine
>>
>>
>> def create_all_tables(metadata):
>> for table_name in metadata.tables.keys():
>> exec('global '+str(table_name)+'; '+str(table_name)+' =
>> metadata.tables[table_name]')
>> print table_name
>> print 'TABLE CLASSES ARE CREATED !!!'
>>
>>
>> def pull_data(from_db, to_db, tables):
>> source, sengine = make_session(from_db)
>> smeta = MetaData(bind=sengine)
>> smeta.reflect(bind=sengine)
>> destination, dengine = make_session(to_db)
>>
>> for table_name in tables:
>> print 'Processing', table_name
>> print 'Pulling schema from source server'
>> # table = Table(table_name, smeta, autoload=True)
>> table = smeta.tables[table_name]
>> print 'Creating table on destination server'
>> # table.metadata.create_all(dengine)
>> table.create(dengine, checkfirst=True)
>> data = sengine.execute(table.select()).fetchall()
>> if data:
>> print (table.insert())
>> dengine.execute(table.insert(), data)
>>
>> print 'Finished!'
>>
>>
>>
>> def print_usage():
>> print """
>> Usage: %s -f source_server -t destination_server table [table ...]
>> -f, -t = driver://user[:password]@host[:port]/database
>>
>>
>> Example: %s -f oracle://someuser:PaSsWd@db1/TSH1 \\
>> -t mysql://root@db2:3307/reporting table_one table_two
>> """ % (sys.argv[0], sys.argv[0])
>>
>>
>> def quick_mapper(table):
>> Base = declarative_base()
>> class GenericMapper(Base):
>> __table__ = table
>> return GenericMapper
>>
>>
>> # Call this from terminal
>> if __name__ == '__main__':
>> optlist, tables = getopt.getopt(sys.argv[1:], 'f:t:')
>>
>>
>> options = dict(optlist)
>> if '-f' not in options or '-t' not in options or not tables:
>> print_usage()
>> raise SystemExit, 1
>>
>>
>> pull_data(
>> options['-f'],
>> options['-t'],
>> tables,
>> )
>>
>>
>>
>>
>>
>> And this is the error I get form my table
>>
>> sqlalchemy.exc.IntegrityError: (IntegrityError) (1452, 'Cannot add or update
>> a child row: a foreign key constraint fails (`s...
>>
>>
>>
>> On Friday, 10 October 2014 17:44:24 UTC+3, Michael Bayer wrote:
>>
>> On Oct 10, 2014, at 10:30 AM, Eren Gölge <[email protected]> wrote:
>>
>> > I try to do basic ETL job with SQLalchemy but it always enforces relations
>> > between tables.
>>
>> SQLAlchemy doesn't do anything like that. I think you are referring to the
>> FOREIGN KEY constraints that are within your database.
>>
>>
>> > I only like to copy the tables with the raw data inside without the
>> > considration of table relations. How can I drop all those relations from
>> > the DB metadata and copy the tables.
>>
>> You can drop or possibly temporarily disable FOREIGN KEY constraints on the
>> database side. How this is done depends on the kind of database you're
>> using. For example, with MySQL, it's very easy, use FOREIGN_KEY_CHECKS:
>> http://www.sqlines.com/mysql/set_foreign_key_checks.
>>
>>
>> > In addition I also need to create tables if they are not exist. The use of
>> > create_all() function does not meet my needs because of the aforementioned
>> > problems.
>>
>> I don't understand how an existing foreign key constraint interferes with
>> the processing of a CREATE TABLE statement, you'd have to illustrate a
>> specific example.
>>
>> --
>> 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].
> 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].
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.
