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] <javascript:>>
> 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.
