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.
