def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine domain = Table( 'domains', meta, Column('id', Integer, primary_key=True, autoincrement=True), Column('created_at', DateTime), Column('name', String(length=64), nullable=False), Column('project_id', String(length=255)), Column('updated_at', DateTime), Column('user_id', String(length=255)), Column('uuid', String(length=255)), UniqueConstraint('name', name='uniq_domain0name'), mysql_engine='InnoDB', mysql_charset='utf8', ) tables = [domain] for table in tables: try: table.create() except Exception as e: LOG.exception(e) meta.drop_all(tables=tables) raise
def setup_class(cls): global metadata global t1, t2, t3 metadata = MetaData(testing.db) t1 = Table("t1", metadata, Column("t1_id", Integer, primary_key=True), Column("name", String(32))) t2 = Table( "t2", metadata, Column("t2_id", Integer, primary_key=True), Column("t1_id", Integer, ForeignKey("t1.t1_id")), Column("name", String(32)), ) t3 = Table( "t3", metadata, Column("t3_id", Integer, primary_key=True), Column("t2_id", Integer, ForeignKey("t2.t2_id")), Column("name", String(32)), ) metadata.drop_all() metadata.create_all() # t1.10 -> t2.20 -> t3.30 # t1.11 -> t2.21 # t1.12 t1.insert().execute( {"t1_id": 10, "name": "t1 #10"}, {"t1_id": 11, "name": "t1 #11"}, {"t1_id": 12, "name": "t1 #12"} ) t2.insert().execute({"t2_id": 20, "t1_id": 10, "name": "t2 #20"}, {"t2_id": 21, "t1_id": 11, "name": "t2 #21"}) t3.insert().execute({"t3_id": 30, "t2_id": 20, "name": "t3 #30"})
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; # bind migrate_engine to your metadata meta = MetaData() meta.bind = migrate_engine ceph_cluster = Table( "clusters", meta, Column("id", Integer, primary_key=True, nullable=False), # TODO add UUID for cluster. If there are same cluster name. Column("name", String(length=255), nullable=True), Column("file_system", String(length=255), nullable=True), Column("primary_public_network", String(length=255), nullable=True), Column("secondary_public_network", String(length=255), nullable=True), Column("cluster_network", String(length=255), nullable=True), Column("journal_size", Integer, nullable=False), Column("size", Integer, nullable=True), # [!NOTE!] this field is json format string. # At now, it contains keyring.admin content from # monitor node. Column("info_dict", Text(convert_unicode=False, unicode_error=None, _warn_on_bytestring=False), nullable=True), Column("ceph_conf", Text(length=10485760), nullable=True), Column("deleted_times", Integer, nullable=True), Column("created_at", DateTime(timezone=False)), Column("updated_at", DateTime(timezone=False)), Column("deleted_at", DateTime(timezone=False)), Column("deleted", Boolean(create_constraint=True, name=None)), ) try: ceph_cluster.create() except Exception: meta.drop_all(tables=[ceph_cluster]) raise
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine # create new table operation_log = Table('operation_log', meta, Column('created_at', DateTime(timezone=False)), Column('updated_at', DateTime(timezone=False)), Column('deleted_at', DateTime(timezone=False)), Column('deleted', Boolean(create_constraint=True, name=None)), Column('id', Integer(), primary_key=True, nullable=False, autoincrement=True), Column('client_ip', String(255), nullable=False), Column('roles', String(255), nullable=False), Column('tenant', String(255), nullable=False), Column('user', String(255), nullable=False), Column('time_at', DateTime(timezone=False)), Column('path_info', String(255), nullable=False), Column('method', String(255), nullable=False), Column('body', String(255), nullable=False), ) try: operation_log.create() except Exception: meta.drop_all(tables=[operation_log]) raise if migrate_engine.name == "mysql": migrate_engine.execute("ALTER TABLE operation_log " "Engine=InnoDB")
class datos: def __init__(self): self.engine = create_engine('sqlite:///data/conta.db') self.metadata = MetaData(bind=self.engine) self.conn = self.engine.connect() def unsetup(self): self.metadata.drop_all() def setup(self): self.tblfacturas = Table('facturas', self.metadata, Column('id', sqlite.TEXT, primary_key=True, index=True), Column('fecha', sqlite.DATE), Column('ruc', sqlite.TEXT), Column('razon_social', sqlite.TEXT), Column('condicion', sqlite.SMALLINT, default=0), Column('numero', sqlite.TEXT), Column('timbrado', sqlite.TEXT), Column('timbrado_inicio', sqlite.DATE), Column('timbrado_fin', sqlite.DATE) ) self.metadata.create_all() def __del__(self): self.conn.close()
def test_explicit_default_schema_metadata(self): engine = testing.db if testing.against("sqlite"): # Works for CREATE TABLE main.foo, SELECT FROM main.foo, etc., # but fails on: # FOREIGN KEY(col2) REFERENCES main.table1 (col1) schema = "main" else: schema = engine.dialect.default_schema_name assert bool(schema) metadata = MetaData(engine, schema=schema) table1 = Table("table1", metadata, Column("col1", sa.Integer, primary_key=True), test_needs_fk=True) table2 = Table( "table2", metadata, Column("col1", sa.Integer, primary_key=True), Column("col2", sa.Integer, sa.ForeignKey("table1.col1")), test_needs_fk=True, ) try: metadata.create_all() metadata.create_all(checkfirst=True) assert len(metadata.tables) == 2 metadata.clear() table1 = Table("table1", metadata, autoload=True) table2 = Table("table2", metadata, autoload=True) assert len(metadata.tables) == 2 finally: metadata.drop_all()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; # bind migrate_engine to your metadata meta = MetaData() meta.bind = migrate_engine rbds = Table( 'rbds', meta, Column('id', Integer, primary_key=True, nullable=False), Column('pool', String(length=255), nullable=False), Column('image', String(length=255), nullable=False), Column('size', BigInteger, nullable=False), Column('format', Integer, nullable=False), Column('objects', Integer, nullable=False), Column('order', Integer, nullable=False), Column('created_at', DateTime(timezone=False)), Column('updated_at', DateTime(timezone=False)), Column('deleted_at', DateTime(timezone=False)), Column('deleted', Boolean(create_constraint=True, name=None)), ) try: rbds.create() except Exception: meta.drop_all(tables=[rbds]) raise
def test_with_inheritance(self): metadata = MetaData(testing.db) table1 = Table("mytable", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30)) ) table2 = Table("mytable2", metadata, Column('col1', Integer, ForeignKey('mytable.col1'), primary_key=True, test_needs_autoincrement=True), Column('col3', String(30)), ) @profile_memory def go(): class A(fixtures.ComparableEntity): pass class B(A): pass mapper(A, table1, polymorphic_on=table1.c.col2, polymorphic_identity='a') mapper(B, table2, inherits=A, polymorphic_identity='b') sess = create_session() a1 = A() a2 = A() b1 = B(col3='b1') b2 = B(col3='b2') for x in [a1,a2,b1, b2]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_( [ A(), A(), B(col3='b1'), B(col3='b2') ], alist) for a in alist: sess.delete(a) sess.flush() # dont need to clear_mappers() del B del A metadata.create_all() try: go() finally: metadata.drop_all() assert_no_mappers()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; # bind migrate_engine to your metadata meta = MetaData() meta.bind = migrate_engine monitors = Table( 'monitors', meta, Column('id', Integer, primary_key=True, nullable=False), Column('name', String(length=255), nullable=False), Column('address', String(length=255)), Column('health', String(length=255)), Column('details', String(length=255)), Column('skew', String(length=255)), Column('latency', String(length=255)), Column('kb_total', Integer), Column('kb_used', Integer), Column('kb_avail', Integer), Column('avail_percent', Integer), Column('created_at', DateTime(timezone=False)), Column('updated_at', DateTime(timezone=False)), Column('deleted_at', DateTime(timezone=False)), Column('deleted', Boolean(create_constraint=True, name=None)) ) try: monitors.create() except Exception: meta.drop_all(tables=[monitors]) raise
def drop_all_tables( self ): """! brief This will really drop all tables including their contents.""" #meta = MetaData( self.dbSessionMaker.engine ) meta = MetaData( engine ) meta.reflect() meta.drop_all()
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine # # New Tables # s3_images = Table('s3_images', meta, Column('created_at', DateTime(timezone=False)), Column('updated_at', DateTime(timezone=False)), Column('deleted_at', DateTime(timezone=False)), Column('deleted', Boolean(create_constraint=True, name=None)), Column('id', Integer(), primary_key=True, nullable=False, autoincrement=True), Column('uuid', String(36), nullable=False)) try: s3_images.create() except Exception: LOG.exception("Exception while creating table 's3_images'") meta.drop_all(tables=[s3_images]) raise
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine # Create new table volume_usage_cache = Table('volume_usage_cache', meta, Column('created_at', DateTime(timezone=False)), Column('updated_at', DateTime(timezone=False)), Column('deleted_at', DateTime(timezone=False)), Column('deleted', Boolean(create_constraint=True, name=None)), Column('id', Integer(), primary_key=True, nullable=False), Column('volume_id', String(36), nullable=False), Column("instance_id", Integer()), Column('tot_last_refreshed', DateTime(timezone=False)), Column('tot_reads', BigInteger(), default=0), Column('tot_read_bytes', BigInteger(), default=0), Column('tot_writes', BigInteger(), default=0), Column('tot_write_bytes', BigInteger(), default=0), Column('curr_last_refreshed', DateTime(timezone=False)), Column('curr_reads', BigInteger(), default=0), Column('curr_read_bytes', BigInteger(), default=0), Column('curr_writes', BigInteger(), default=0), Column('curr_write_bytes', BigInteger(), default=0), mysql_engine='InnoDB', mysql_charset='utf8' ) try: volume_usage_cache.create() except Exception: LOG.exception("Exception while creating table 'volume_usage_cache'") meta.drop_all(tables=[volume_usage_cache]) raise
def test_basic(self): try: # the 'convert_unicode' should not get in the way of the reflection # process. reflecttable for oracle, postgres (others?) expect non-unicode # strings in result sets/bind params bind = engines.utf8_engine(options={'convert_unicode':True}) metadata = MetaData(bind) if testing.against('sybase', 'maxdb', 'oracle', 'mssql'): names = set(['plain']) else: names = set([u'plain', u'Unit\u00e9ble', u'\u6e2c\u8a66']) for name in names: Table(name, metadata, Column('id', sa.Integer, sa.Sequence(name + "_id_seq"), primary_key=True)) metadata.create_all() reflected = set(bind.table_names()) if not names.issubset(reflected): # Python source files in the utf-8 coding seem to normalize # literals as NFC (and the above are explicitly NFC). Maybe # this database normalizes NFD on reflection. nfc = set([unicodedata.normalize('NFC', n) for n in names]) self.assert_(nfc == names) # Yep. But still ensure that bulk reflection and create/drop # work with either normalization. r = MetaData(bind, reflect=True) r.drop_all() r.create_all() finally: metadata.drop_all() bind.dispose()
def test_implicit_execution(self): metadata = MetaData() table = Table( "test_table", metadata, Column("foo", Integer), test_needs_acid=True, ) conn = testing.db.connect() metadata.create_all(bind=conn) try: trans = conn.begin() metadata.bind = conn t = table.insert() assert t.bind is conn table.insert().execute(foo=5) table.insert().execute(foo=6) table.insert().execute(foo=7) trans.rollback() metadata.bind = None assert ( conn.execute("select count(*) from test_table").scalar() == 0 ) finally: metadata.drop_all(bind=conn)
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; # bind migrate_engine to your metadata meta = MetaData() meta.bind = migrate_engine services = Table( 'services', meta, Column('created_at', DateTime), Column('updated_at', DateTime), Column('deleted_at', DateTime), Column('deleted', Boolean), Column('id', Integer, primary_key=True, nullable=False), Column('host', String(length=255)), Column('binary', String(length=255)), Column('topic', String(length=255)), Column('report_count', Integer, nullable=False), Column('disabled', Boolean), Column('availability_zone', String(length=255)), ) try: compute_nodes.create() except Exception: meta.drop_all(tables=[compute_nodes]) raise try: services.create() except Exception: meta.drop_all(tables=[services]) raise
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine subscriber = Table( 'subscriber', meta, Column('id', Integer, primary_key=True, autoincrement=True), Column('created_at', DateTime), Column('domain', String(length=64), nullable=False), Column('email_address', String(length=64), nullable=False), Column('ha1', String(length=64), nullable=False), Column('ha1b', String(length=64), nullable=False), Column('password', String(length=25), nullable=False), Column('project_id', String(length=255)), Column('rpid', String(length=64)), Column('updated_at', DateTime), Column('user_id', String(length=255)), Column('username', String(length=64), nullable=False), Column('uuid', String(length=255)), UniqueConstraint( 'username', 'domain', name='uniq_subscriber0username0domain'), mysql_engine='InnoDB', mysql_charset='utf8', ) tables = [subscriber] for table in tables: try: table.create() except Exception as e: LOG.exception(e) meta.drop_all(tables=tables) raise
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine cloned_resource = Table('plan_cloned_resources', meta, Column('created_at', DateTime(timezone=False)), Column('updated_at', DateTime(timezone=False)), Column('deleted_at', DateTime(timezone=False)), Column('id', Integer, primary_key=True, nullable=False), Column('plan_id', String(length=36), nullable=False), Column('destination', String(length=36), nullable=False), Column('relation', types.Json), Column('dependencies', types.Json), Column('deleted', Integer), mysql_engine='InnoDB', mysql_charset='utf8') try: cloned_resource.create() except Exception: meta.drop_all(tables=[cloned_resource]) raise if migrate_engine.name == "mysql": table = "template" migrate_engine.execute("SET foreign_key_checks = 0") migrate_engine.execute( "ALTER TABLE %s CONVERT TO CHARACTER SET utf8" % table) migrate_engine.execute("SET foreign_key_checks = 1") migrate_engine.execute( "ALTER DATABASE %s DEFAULT CHARACTER SET utf8" % migrate_engine.url.database) migrate_engine.execute("ALTER TABLE %s Engine=InnoDB" % table)
def test_join_cache(self): metadata = MetaData(testing.db) table1 = Table('table1', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('data', String(30))) table2 = Table('table2', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('data', String(30)), Column('t1id', Integer, ForeignKey('table1.id'))) class Foo(object): pass class Bar(object): pass mapper(Foo, table1, properties={'bars' : relationship(mapper(Bar, table2))}) metadata.create_all() session = sessionmaker() @profile_memory def go(): s = table2.select() sess = session() sess.query(Foo).join((s, Foo.bars)).all() sess.rollback() try: go() finally: metadata.drop_all()
def upgrade(migrate_engine): # Upgrade operations go here. Don't create your own engine; # bind migrate_engine to your metadata meta = MetaData() meta.bind = migrate_engine storage_pool_usages = Table( 'storage_pool_usages', meta, Column('id', Integer, primary_key=True, nullable=False), Column('pool_id', Integer, ForeignKey(models.StoragePool.id), nullable=False), Column('vsmapp_id', Integer, ForeignKey(models.Vsmapp.id), nullable=False), Column('attach_status', String(length=255), nullable=False), Column('attach_at', DateTime(timezone=False)), Column('terminate_at', DateTime(timezone=False)), Column('created_at', DateTime(timezone=False)), Column('updated_at', DateTime(timezone=False)), Column('deleted_at', DateTime(timezone=False)), Column('deleted', Boolean(create_constraint=True, name=None), default=False), ) try: storage_pool_usages.create() except Exception: meta.drop_all(tables=[storage_pool_usages]) raise
def test_basic(self): # the 'convert_unicode' should not get in the way of the # reflection process. reflecttable for oracle, postgresql # (others?) expect non-unicode strings in result sets/bind # params bind = self.bind names = set([rec[0] for rec in self.names]) reflected = set(bind.table_names()) # Jython 2.5 on Java 5 lacks unicodedata.normalize if not names.issubset(reflected) and hasattr(unicodedata, "normalize"): # Python source files in the utf-8 coding seem to # normalize literals as NFC (and the above are # explicitly NFC). Maybe this database normalizes NFD # on reflection. nfc = set([unicodedata.normalize("NFC", n) for n in names]) self.assert_(nfc == names) # Yep. But still ensure that bulk reflection and # create/drop work with either normalization. r = MetaData(bind) r.reflect() r.drop_all(checkfirst=False) r.create_all(checkfirst=False)
def test_attached_as_schema(self): cx = testing.db.connect() try: cx.execute('ATTACH DATABASE ":memory:" AS test_schema') dialect = cx.dialect assert dialect.get_table_names(cx, 'test_schema') == [] meta = MetaData(cx) Table('created', meta, Column('id', Integer), schema='test_schema') alt_master = Table('sqlite_master', meta, autoload=True, schema='test_schema') meta.create_all(cx) eq_(dialect.get_table_names(cx, 'test_schema'), ['created']) assert len(alt_master.c) > 0 meta.clear() reflected = Table('created', meta, autoload=True, schema='test_schema') assert len(reflected.c) == 1 cx.execute(reflected.insert(), dict(id=1)) r = cx.execute(reflected.select()).fetchall() assert list(r) == [(1, )] cx.execute(reflected.update(), dict(id=2)) r = cx.execute(reflected.select()).fetchall() assert list(r) == [(2, )] cx.execute(reflected.delete(reflected.c.id == 2)) r = cx.execute(reflected.select()).fetchall() assert list(r) == [] # note that sqlite_master is cleared, above meta.drop_all() assert dialect.get_table_names(cx, 'test_schema') == [] finally: cx.execute('DETACH DATABASE test_schema')
def test_include_columns(self): meta = MetaData(testing.db) foo = Table('foo', meta, *[Column(n, sa.String(30)) for n in ['a', 'b', 'c', 'd', 'e', 'f']]) meta.create_all() try: meta2 = MetaData(testing.db) foo = Table('foo', meta2, autoload=True, include_columns=['b', 'f', 'e']) # test that cols come back in original order eq_([c.name for c in foo.c], ['b', 'e', 'f']) for c in ('b', 'f', 'e'): assert c in foo.c for c in ('a', 'c', 'd'): assert c not in foo.c # test against a table which is already reflected meta3 = MetaData(testing.db) foo = Table('foo', meta3, autoload=True) foo = Table('foo', meta3, include_columns=['b', 'f', 'e'], useexisting=True) eq_([c.name for c in foo.c], ['b', 'e', 'f']) for c in ('b', 'f', 'e'): assert c in foo.c for c in ('a', 'c', 'd'): assert c not in foo.c finally: meta.drop_all()
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine # create new table task_log = Table('task_log', meta, Column('created_at', DateTime(timezone=False)), Column('updated_at', DateTime(timezone=False)), Column('deleted_at', DateTime(timezone=False)), Column('deleted', Boolean(create_constraint=True, name=None)), Column('id', Integer(), primary_key=True, nullable=False, autoincrement=True), Column('task_name', String(255), nullable=False), Column('state', String(255), nullable=False), Column('host', String(255), index=True, nullable=False), Column('period_beginning', String(255), index=True, nullable=False), Column('period_ending', String(255), index=True, nullable=False), Column('message', String(255), nullable=False), Column('task_items', Integer()), Column('errors', Integer()), ) try: task_log.create() except Exception: meta.drop_all(tables=[task_log]) raise if migrate_engine.name == "mysql": migrate_engine.execute("ALTER TABLE task_log " "Engine=InnoDB")
class InvalidateDuringResultTest(fixtures.TestBase): __backend__ = True def setup(self): self.engine = engines.reconnecting_engine() self.meta = MetaData(self.engine) table = Table( 'sometable', self.meta, Column('id', Integer, primary_key=True), Column('name', String(50))) self.meta.create_all() table.insert().execute( [{'id': i, 'name': 'row %d' % i} for i in range(1, 100)] ) def teardown(self): self.meta.drop_all() self.engine.dispose() @testing.fails_if([ '+mysqlconnector', '+mysqldb', '+cymysql', '+pymysql', '+pg8000'], "Buffers the result set and doesn't check for connection close") def test_invalidate_on_results(self): conn = self.engine.connect() result = conn.execute('select * from sometable') for x in range(20): result.fetchone() self.engine.test_shutdown() _assert_invalidated(result.fetchone) assert conn.invalidated
def test_unicode_warnings(self): metadata = MetaData(self.engine) table1 = Table( "mytable", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", Unicode(30)), ) metadata.create_all() i = [1] # the times here is cranked way up so that we can see # pysqlite clearing out its internal buffer and allow # the test to pass @testing.emits_warning() @profile_memory() def go(): # execute with a non-unicode object. a warning is emitted, # this warning shouldn't clog up memory. self.engine.execute( table1.select().where(table1.c.col2 == "foo%d" % i[0]) ) i[0] += 1 try: go() finally: metadata.drop_all()
def test_boolean(self): """Test that the boolean only treats 1 as True """ meta = MetaData(testing.db) t = Table('bool_table', meta, Column('id', Integer, primary_key=True), Column('boo', Boolean(create_constraint=False))) try: meta.create_all() testing.db.execute("INSERT INTO bool_table (id, boo) " "VALUES (1, 'false');") testing.db.execute("INSERT INTO bool_table (id, boo) " "VALUES (2, 'true');") testing.db.execute("INSERT INTO bool_table (id, boo) " "VALUES (3, '1');") testing.db.execute("INSERT INTO bool_table (id, boo) " "VALUES (4, '0');") testing.db.execute('INSERT INTO bool_table (id, boo) ' 'VALUES (5, 1);') testing.db.execute('INSERT INTO bool_table (id, boo) ' 'VALUES (6, 0);') eq_(t.select(t.c.boo).order_by(t.c.id).execute().fetchall(), [(3, True), (5, True)]) finally: meta.drop_all()
def test_use_alter(self): m = MetaData() Table('t', m, Column('a', Integer), ) Table('t2', m, Column('a', Integer, ForeignKey('t.a', use_alter=True, name='fk_ta')), Column('b', Integer, ForeignKey('t.a', name='fk_tb')) ) e = engines.mock_engine(dialect_name='postgresql') m.create_all(e) m.drop_all(e) e.assert_sql([ 'CREATE TABLE t (a INTEGER)', 'CREATE TABLE t2 (a INTEGER, b INTEGER, CONSTRAINT fk_tb ' 'FOREIGN KEY(b) REFERENCES t (a))', 'ALTER TABLE t2 ' 'ADD CONSTRAINT fk_ta FOREIGN KEY(a) REFERENCES t (a)', 'ALTER TABLE t2 DROP CONSTRAINT fk_ta', 'DROP TABLE t2', 'DROP TABLE t' ])
def test_clauseelement(self): metadata = MetaData() table = Table('test_table', metadata, Column('foo', Integer)) metadata.create_all(bind=testing.db) try: for elem in [ table.select, lambda **kwargs: sa.func.current_timestamp(**kwargs).select(), # func.current_timestamp().select, lambda **kwargs:text("select * from test_table", **kwargs) ]: for bind in ( testing.db, testing.db.connect() ): try: e = elem(bind=bind) assert e.bind is bind e.execute().close() finally: if isinstance(bind, engine.Connection): bind.close() e = elem() assert e.bind is None assert_raises( exc.UnboundExecutionError, e.execute ) finally: if isinstance(bind, engine.Connection): bind.close() metadata.drop_all(bind=testing.db)
def test_index_reflection(self): m1 = MetaData(testing.db) t1 = Table('party', m1, Column('id', sa.Integer, nullable=False), Column('name', sa.String(20), index=True) ) i1 = sa.Index('idx1', t1.c.id, unique=True) i2 = sa.Index('idx2', t1.c.name, t1.c.id, unique=False) m1.create_all() try: m2 = MetaData(testing.db) t2 = Table('party', m2, autoload=True) print len(t2.indexes), t2.indexes assert len(t2.indexes) == 3 # Make sure indexes are in the order we expect them in tmp = [(idx.name, idx) for idx in t2.indexes] tmp.sort() r1, r2, r3 = [idx[1] for idx in tmp] assert r1.name == 'idx1' assert r2.name == 'idx2' assert r1.unique == True assert r2.unique == False assert r3.unique == False assert [t2.c.id] == r1.columns assert [t2.c.name, t2.c.id] == r2.columns assert [t2.c.name] == r3.columns finally: m1.drop_all()
def dbinit(args): with utils.status("Reading config file 'catalyst.json'"): with open("catalyst.json", "r") as f: config = json.load(f) metadata = config['metadata'] module_name, variable_name = metadata.split(":") sys.path.insert(0, '') metadata = importlib.import_module(module_name) for variable_name in variable_name.split("."): metadata = getattr(metadata, variable_name) from sqlalchemy import create_engine, MetaData target = args.target or config.get('dburi', None) if target is None: raise Exception("No 'target' argument specified and no 'dburi' setting in config file.") if not args.yes and 'y' != input("Warning: any existing data at '%s' will be erased. Proceed? [y/n]" % target): return dst_engine = create_engine(target) # clear out any existing tables dst_metadata = MetaData(bind=dst_engine) dst_metadata.reflect() dst_metadata.drop_all() # create new tables dst_metadata = metadata dst_metadata.create_all(dst_engine)
class TestShellDatabase(Shell, DB): """Commands associated with a particular database""" # We'll need to clean up after ourself, since the shell creates its own txn; # we need to connect to the DB to see if things worked level = DB.CONNECT @usedb() def test_version_control(self): """Ensure we can set version control on a database""" path_repos = repos = self.tmp_repos() url = self.url result = self.env.run('migrate create %s repository_name' % repos) result = self.env.run('migrate drop_version_control %(url)s %(repos)s'\ % locals(), expect_error=True) self.assertEqual(result.returncode, 1) result = self.env.run('migrate version_control %(url)s %(repos)s' % locals()) # Clean up result = self.env.run( 'migrate drop_version_control %(url)s %(repos)s' % locals()) # Attempting to drop vc from a database without it should fail result = self.env.run('migrate drop_version_control %(url)s %(repos)s'\ % locals(), expect_error=True) self.assertEqual(result.returncode, 1) @usedb() def test_wrapped_kwargs(self): """Commands with default arguments set by manage.py""" path_repos = repos = self.tmp_repos() url = self.url result = self.env.run('migrate create --name=repository_name %s' % repos) result = self.env.run( 'migrate drop_version_control %(url)s %(repos)s' % locals(), expect_error=True) self.assertEqual(result.returncode, 1) result = self.env.run('migrate version_control %(url)s %(repos)s' % locals()) result = self.env.run( 'migrate drop_version_control %(url)s %(repos)s' % locals()) @usedb() def test_version_control_specified(self): """Ensure we can set version control to a particular version""" path_repos = self.tmp_repos() url = self.url result = self.env.run('migrate create --name=repository_name %s' % path_repos) result = self.env.run( 'migrate drop_version_control %(url)s %(path_repos)s' % locals(), expect_error=True) self.assertEqual(result.returncode, 1) # Fill the repository path_script = self.tmp_py() version = 2 for i in range(version): result = self.env.run('migrate script Desc --repository=%s' % path_repos) # Repository version is correct result = self.env.run('migrate version %s' % path_repos) self.assertEqual(result.stdout.strip(), str(version)) # Apply versioning to DB result = self.env.run( 'migrate version_control %(url)s %(path_repos)s %(version)s' % locals()) # Test db version number (should start at 2) result = self.env.run('migrate db_version %(url)s %(path_repos)s' % locals()) self.assertEqual(result.stdout.strip(), str(version)) # Clean up result = self.env.run( 'migrate drop_version_control %(url)s %(path_repos)s' % locals()) @usedb() def test_upgrade(self): """Can upgrade a versioned database""" # Create a repository repos_name = 'repos_name' repos_path = self.tmp() result = self.env.run('migrate create %(repos_path)s %(repos_name)s' % locals()) self.assertEqual(self.run_version(repos_path), 0) # Version the DB result = self.env.run('migrate drop_version_control %s %s' % (self.url, repos_path), expect_error=True) result = self.env.run('migrate version_control %s %s' % (self.url, repos_path)) # Upgrades with latest version == 0 self.assertEqual(self.run_db_version(self.url, repos_path), 0) result = self.env.run('migrate upgrade %s %s' % (self.url, repos_path)) self.assertEqual(self.run_db_version(self.url, repos_path), 0) result = self.env.run('migrate upgrade %s %s' % (self.url, repos_path)) self.assertEqual(self.run_db_version(self.url, repos_path), 0) result = self.env.run('migrate upgrade %s %s 1' % (self.url, repos_path), expect_error=True) self.assertEqual(result.returncode, 1) result = self.env.run('migrate upgrade %s %s -1' % (self.url, repos_path), expect_error=True) self.assertEqual(result.returncode, 2) # Add a script to the repository; upgrade the db result = self.env.run('migrate script Desc --repository=%s' % (repos_path)) self.assertEqual(self.run_version(repos_path), 1) self.assertEqual(self.run_db_version(self.url, repos_path), 0) # Test preview result = self.env.run('migrate upgrade %s %s 0 --preview_sql' % (self.url, repos_path)) result = self.env.run('migrate upgrade %s %s 0 --preview_py' % (self.url, repos_path)) result = self.env.run('migrate upgrade %s %s' % (self.url, repos_path)) self.assertEqual(self.run_db_version(self.url, repos_path), 1) # Downgrade must have a valid version specified result = self.env.run('migrate downgrade %s %s' % (self.url, repos_path), expect_error=True) self.assertEqual(result.returncode, 2) result = self.env.run('migrate downgrade %s %s -1' % (self.url, repos_path), expect_error=True) self.assertEqual(result.returncode, 2) result = self.env.run('migrate downgrade %s %s 2' % (self.url, repos_path), expect_error=True) self.assertEqual(result.returncode, 2) self.assertEqual(self.run_db_version(self.url, repos_path), 1) result = self.env.run('migrate downgrade %s %s 0' % (self.url, repos_path)) self.assertEqual(self.run_db_version(self.url, repos_path), 0) result = self.env.run('migrate downgrade %s %s 1' % (self.url, repos_path), expect_error=True) self.assertEqual(result.returncode, 2) self.assertEqual(self.run_db_version(self.url, repos_path), 0) result = self.env.run('migrate drop_version_control %s %s' % (self.url, repos_path)) def _run_test_sqlfile(self, upgrade_script, downgrade_script): # TODO: add test script that checks if db really changed repos_path = self.tmp() repos_name = 'repos' result = self.env.run('migrate create %s %s' % (repos_path, repos_name)) result = self.env.run('migrate drop_version_control %s %s' % (self.url, repos_path), expect_error=True) result = self.env.run('migrate version_control %s %s' % (self.url, repos_path)) self.assertEqual(self.run_version(repos_path), 0) self.assertEqual(self.run_db_version(self.url, repos_path), 0) beforeCount = len( os.listdir(os.path.join(repos_path, 'versions')) ) # hmm, this number changes sometimes based on running from svn result = self.env.run('migrate script_sql %s --repository=%s' % ('postgres', repos_path)) self.assertEqual(self.run_version(repos_path), 1) self.assertEqual(len(os.listdir(os.path.join(repos_path, 'versions'))), beforeCount + 2) open('%s/versions/001_postgres_upgrade.sql' % repos_path, 'a').write(upgrade_script) open('%s/versions/001_postgres_downgrade.sql' % repos_path, 'a').write(downgrade_script) self.assertEqual(self.run_db_version(self.url, repos_path), 0) self.assertRaises(Exception, self.engine.text('select * from t_table').execute) result = self.env.run('migrate upgrade %s %s' % (self.url, repos_path)) self.assertEqual(self.run_db_version(self.url, repos_path), 1) self.engine.text('select * from t_table').execute() result = self.env.run('migrate downgrade %s %s 0' % (self.url, repos_path)) self.assertEqual(self.run_db_version(self.url, repos_path), 0) self.assertRaises(Exception, self.engine.text('select * from t_table').execute) # The tests below are written with some postgres syntax, but the stuff # being tested (.sql files) ought to work with any db. @usedb(supported='postgres') def test_sqlfile(self): upgrade_script = """ create table t_table ( id serial, primary key(id) ); """ downgrade_script = """ drop table t_table; """ self.meta.drop_all() self._run_test_sqlfile(upgrade_script, downgrade_script) @usedb(supported='postgres') def test_sqlfile_comment(self): upgrade_script = """ -- Comments in SQL break postgres autocommit create table t_table ( id serial, primary key(id) ); """ downgrade_script = """ -- Comments in SQL break postgres autocommit drop table t_table; """ self._run_test_sqlfile(upgrade_script, downgrade_script) @usedb() def test_command_test(self): repos_name = 'repos_name' repos_path = self.tmp() result = self.env.run( 'migrate create repository_name --repository=%s' % repos_path) result = self.env.run('migrate drop_version_control %s %s' % (self.url, repos_path), expect_error=True) result = self.env.run('migrate version_control %s %s' % (self.url, repos_path)) self.assertEqual(self.run_version(repos_path), 0) self.assertEqual(self.run_db_version(self.url, repos_path), 0) # Empty script should succeed result = self.env.run('migrate script Desc %s' % repos_path) result = self.env.run('migrate test %s %s' % (self.url, repos_path)) self.assertEqual(self.run_version(repos_path), 1) self.assertEqual(self.run_db_version(self.url, repos_path), 0) # Error script should fail script_path = self.tmp_py() script_text = ''' from sqlalchemy import * from migrate import * def upgrade(): print 'fgsfds' raise Exception() def downgrade(): print 'sdfsgf' raise Exception() '''.replace("\n ", "\n") file = open(script_path, 'w') file.write(script_text) file.close() result = self.env.run('migrate test %s %s bla' % (self.url, repos_path), expect_error=True) self.assertEqual(result.returncode, 2) self.assertEqual(self.run_version(repos_path), 1) self.assertEqual(self.run_db_version(self.url, repos_path), 0) # Nonempty script using migrate_engine should succeed script_path = self.tmp_py() script_text = ''' from sqlalchemy import * from migrate import * from migrate.changeset import schema meta = MetaData(migrate_engine) account = Table('account', meta, Column('id', Integer, primary_key=True), Column('login', Text), Column('passwd', Text), ) def upgrade(): # Upgrade operations go here. Don't create your own engine; use the engine # named 'migrate_engine' imported from migrate. meta.create_all() def downgrade(): # Operations to reverse the above upgrade go here. meta.drop_all() '''.replace("\n ", "\n") file = open(script_path, 'w') file.write(script_text) file.close() result = self.env.run('migrate test %s %s' % (self.url, repos_path)) self.assertEqual(self.run_version(repos_path), 1) self.assertEqual(self.run_db_version(self.url, repos_path), 0) @usedb() def test_rundiffs_in_shell(self): # This is a variant of the test_schemadiff tests but run through the shell level. # These shell tests are hard to debug (since they keep forking processes) # so they shouldn't replace the lower-level tests. repos_name = 'repos_name' repos_path = self.tmp() script_path = self.tmp_py() model_module = 'migrate.tests.fixture.models:meta_rundiffs' old_model_module = 'migrate.tests.fixture.models:meta_old_rundiffs' # Create empty repository. self.meta = MetaData(self.engine) self.meta.reflect() self.meta.drop_all( ) # in case junk tables are lying around in the test database result = self.env.run('migrate create %s %s' % (repos_path, repos_name)) result = self.env.run('migrate drop_version_control %s %s' % (self.url, repos_path), expect_error=True) result = self.env.run('migrate version_control %s %s' % (self.url, repos_path)) self.assertEqual(self.run_version(repos_path), 0) self.assertEqual(self.run_db_version(self.url, repos_path), 0) # Setup helper script. result = self.env.run('migrate manage %s --repository=%s --url=%s --model=%s'\ % (script_path, repos_path, self.url, model_module)) self.assertTrue(os.path.exists(script_path)) # Model is defined but database is empty. result = self.env.run('migrate compare_model_to_db %s %s --model=%s' \ % (self.url, repos_path, model_module)) self.assertTrue("tables missing from database: tmp_account_rundiffs" in result.stdout) # Test Deprecation result = self.env.run('migrate compare_model_to_db %s %s --model=%s' \ % (self.url, repos_path, model_module.replace(":", ".")), expect_error=True) self.assertEqual(result.returncode, 0) self.assertTrue("tables missing from database: tmp_account_rundiffs" in result.stdout) # Update db to latest model. result = self.env.run('migrate update_db_from_model %s %s %s'\ % (self.url, repos_path, model_module)) self.assertEqual(self.run_version(repos_path), 0) self.assertEqual( self.run_db_version(self.url, repos_path), 0 ) # version did not get bumped yet because new version not yet created result = self.env.run('migrate compare_model_to_db %s %s %s'\ % (self.url, repos_path, model_module)) self.assertTrue("No schema diffs" in result.stdout) result = self.env.run('migrate drop_version_control %s %s' % (self.url, repos_path), expect_error=True) result = self.env.run('migrate version_control %s %s' % (self.url, repos_path)) result = self.env.run('migrate create_model %s %s' % (self.url, repos_path)) temp_dict = dict() six.exec_(result.stdout, temp_dict) # TODO: breaks on SA06 and SA05 - in need of total refactor - use different approach # TODO: compare whole table self.compare_columns_equal(models.tmp_account_rundiffs.c, temp_dict['tmp_account_rundiffs'].c, ['type']) ##self.assertTrue("""tmp_account_rundiffs = Table('tmp_account_rundiffs', meta, ##Column('id', Integer(), primary_key=True, nullable=False), ##Column('login', String(length=None, convert_unicode=False, assert_unicode=None)), ##Column('passwd', String(length=None, convert_unicode=False, assert_unicode=None))""" in result.stdout) ## We're happy with db changes, make first db upgrade script to go from version 0 -> 1. #result = self.env.run('migrate make_update_script_for_model', expect_error=True) #self.assertTrue('Not enough arguments' in result.stderr) #result_script = self.env.run('migrate make_update_script_for_model %s %s %s %s'\ #% (self.url, repos_path, old_model_module, model_module)) #self.assertEqualIgnoreWhitespace(result_script.stdout, #'''from sqlalchemy import * #from migrate import * #from migrate.changeset import schema #meta = MetaData() #tmp_account_rundiffs = Table('tmp_account_rundiffs', meta, #Column('id', Integer(), primary_key=True, nullable=False), #Column('login', Text(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)), #Column('passwd', Text(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)), #) #def upgrade(migrate_engine): ## Upgrade operations go here. Don't create your own engine; bind migrate_engine ## to your metadata #meta.bind = migrate_engine #tmp_account_rundiffs.create() #def downgrade(migrate_engine): ## Operations to reverse the above upgrade go here. #meta.bind = migrate_engine #tmp_account_rundiffs.drop()''') ## Save the upgrade script. #result = self.env.run('migrate script Desc %s' % repos_path) #upgrade_script_path = '%s/versions/001_Desc.py' % repos_path #open(upgrade_script_path, 'w').write(result_script.stdout) #result = self.env.run('migrate compare_model_to_db %s %s %s'\ #% (self.url, repos_path, model_module)) #self.assertTrue("No schema diffs" in result.stdout) self.meta.drop_all( ) # in case junk tables are lying around in the test database
def drop_all_tables(): db_metadata = MetaData(bind=engine) db_metadata.reflect() db_metadata.drop_all()
class Storage(object): """SQL Tabular Storage. It's an implementation of `jsontablescema.Storage`. Args: engine (object): SQLAlchemy engine dbschema (str): database schema name prefix (str): prefix for all buckets """ # Public def __init__(self, engine, dbschema=None, prefix=''): # Set attributes self.__connection = engine.connect() self.__dbschema = dbschema self.__prefix = prefix self.__descriptors = {} # Create metadata self.__metadata = MetaData(bind=self.__connection, schema=self.__dbschema, reflect=True) def __repr__(self): # Template and format template = 'Storage <{engine}/{dbschema}>' text = template.format(engine=self.__connection.engine, dbschema=self.__dbschema) return text @property def buckets(self): # Collect buckets = [] for table in self.__metadata.sorted_tables: bucket = mappers.tablename_to_bucket(self.__prefix, table.name) if bucket is not None: buckets.append(bucket) return buckets def create(self, bucket, descriptor, force=False): # Make lists buckets = bucket if isinstance(bucket, six.string_types): buckets = [bucket] descriptors = descriptor if isinstance(descriptor, dict): descriptors = [descriptor] # Check buckets for existence for bucket in reversed(self.buckets): if bucket in buckets: if not force: message = 'Bucket "%s" already exists.' % bucket raise RuntimeError(message) self.delete(bucket) # Define buckets for bucket, descriptor in zip(buckets, descriptors): # Add to schemas self.__descriptors[bucket] = descriptor # Crate table jsontableschema.validate(descriptor) tablename = mappers.bucket_to_tablename(self.__prefix, bucket) columns, constraints = mappers.descriptor_to_columns_and_constraints( self.__prefix, bucket, descriptor) Table(tablename, self.__metadata, *(columns + constraints)) # Create tables, update metadata self.__metadata.create_all() def delete(self, bucket=None, ignore=False): # Make lists buckets = bucket if isinstance(bucket, six.string_types): buckets = [bucket] elif bucket is None: buckets = reversed(self.buckets) # Iterate over buckets tables = [] for bucket in buckets: # Check existent if bucket not in self.buckets: if not ignore: message = 'Bucket "%s" doesn\'t exist.' % bucket raise RuntimeError(message) # Remove from buckets if bucket in self.__descriptors: del self.__descriptors[bucket] # Add table to tables table = self.__get_table(bucket) tables.append(table) # Drop tables, update metadata self.__metadata.drop_all(tables=tables) self.__metadata.clear() self.__metadata.reflect() def describe(self, bucket, descriptor=None): # Set descriptor if descriptor is not None: self.__descriptors[bucket] = descriptor # Get descriptor else: descriptor = self.__descriptors.get(bucket) if descriptor is None: table = self.__get_table(bucket) descriptor = mappers.columns_and_constraints_to_descriptor( self.__prefix, table.name, table.columns, table.constraints) return descriptor def iter(self, bucket): # Get result table = self.__get_table(bucket) # Streaming could be not working for some backends: # http://docs.sqlalchemy.org/en/latest/core/connections.html select = table.select().execution_options(stream_results=True) result = select.execute() # Yield data for row in result: yield list(row) def read(self, bucket): # Get rows rows = list(self.iter(bucket)) return rows def write(self, bucket, rows): # Prepare BUFFER_SIZE = 1000 descriptor = self.describe(bucket) schema = jsontableschema.Schema(descriptor) table = self.__get_table(bucket) # Write with self.__connection.begin(): keyed_rows = [] for row in rows: keyed_row = {} for index, field in enumerate(schema.fields): value = row[index] try: value = field.cast_value(value) except InvalidObjectType: value = json.loads(value) keyed_row[field.name] = value keyed_rows.append(keyed_row) if len(keyed_rows) > BUFFER_SIZE: # Insert data table.insert().execute(keyed_rows) # Clean memory keyed_rows = [] if len(keyed_rows) > 0: # Insert data table.insert().execute(keyed_rows) # Private def __get_table(self, bucket): """Return SQLAlchemy table for the given bucket. """ # Prepare name tablename = mappers.bucket_to_tablename(self.__prefix, bucket) if self.__dbschema: tablename = '.'.join(self.__dbschema, tablename) return self.__metadata.tables[tablename]
class DialectSQLAlchUsageTest(fixtures.TestBase): """ This usage test is meant to serve as documentation and follows the tutorial here: http://docs.sqlalchemy.org/en/latest/core/tutorial.html but with the dialect being developed """ # Note: this test uses pytest which captures stdout by default, pass -s to allow output to stdout def setUp(self): self.dialect = TeradataDialect() self.conn = testing.db.connect() self.engine = self.conn.engine # build a table with columns self.metadata = MetaData() self.users = Table('my_users', self.metadata, Column('uid', Integer, primary_key=True), Column('name', String(256)), Column('fullname', String(256)), ) self.addresses = Table('addresses', self.metadata, Column('id', Integer, primary_key=True), Column('user_id', None, ForeignKey('my_users.uid'), nullable=False), Column('email_address', String(256), nullable=False), ) self.metadata.create_all(self.engine) def tearDown(self): self.metadata.drop_all(self.engine) self.conn.close() def test_show_state(self): assert self.users in self.metadata.sorted_tables assert self.addresses in self.metadata.sorted_tables def test_inserts(self): self.ins = self.users.insert() # inserts by default require all columns to be provided assert(str(self.ins) == 'INSERT INTO my_users (uid, name, fullname) VALUES (:uid, :name, :fullname)') # use the VALUES clause to limit the values inserted self.ins = self.users.insert().values(name='mark', fullname='mark sandan') # actual values don't get stored in the string assert(str(self.ins) == 'INSERT INTO my_users (name, fullname) VALUES (:name, :fullname)') # data values are stored in the INSERT but only are used when executed, we can peek assert(str(self.ins.compile().params) == str({'fullname': 'mark sandan', 'name': 'mark'})) # None of the inserts above in this test get added to the database def test_executing(self): # re-create a new INSERT object self.ins = self.users.insert() # execute the insert statement res = self.conn.execute(self.ins, uid=1, name='jack', fullname='Jack Jones') assert(res.inserted_primary_key == [1]) res = self.conn.execute(self.ins, uid=2, name='wendy', fullname='Wendy Williams') assert(res.inserted_primary_key == [2]) # the res variable is a ResultProxy object, analagous to DBAPI cursor # issue many inserts, the same is possible for update and delete self.conn.execute(self.addresses.insert(), [ {'id': 1, 'user_id': 1, 'email_address': '*****@*****.**'}, {'id': 2, 'user_id': 1, 'email_address': '*****@*****.**'}, {'id': 3, 'user_id': 2, 'email_address': '*****@*****.**'}, {'id': 4, 'user_id': 2, 'email_address': '*****@*****.**'} ]) # test selects on the inserted values from sqlalchemy.sql import select s = select([self.users]) res = self.conn.execute(s) u1 = res.fetchone() u2 = res.fetchone() # accessing rows assert(u1['name'] == u'jack') assert(u1['fullname'] == u'Jack Jones') assert(u2['name'] == u'wendy') assert(u2['fullname'] == u'Wendy Williams') assert(u1[1] == u1['name']) assert(u1[2] == u1['fullname']) assert(u2[1] == u2['name']) assert(u2[2] == u2['fullname']) # be sure to close the result set res.close() # use cols to access rows res = self.conn.execute(s) u3 = res.fetchone() u4 = res.fetchone() assert(u3[self.users.c.name] == u1['name']) assert(u3[self.users.c.fullname] == u1['fullname']) assert(u4[self.users.c.name] == u2['name']) assert(u4[self.users.c.fullname] == u2['fullname']) # reference individual columns in select clause s = select([self.users.c.name, self.users.c.fullname]) res = self.conn.execute(s) u3 = res.fetchone() u4 = res.fetchone() assert(u3[self.users.c.name] == u1['name']) assert(u3[self.users.c.fullname] == u1['fullname']) assert(u4[self.users.c.name] == u2['name']) assert(u4[self.users.c.fullname] == u2['fullname']) # test joins # cartesian product usrs = [row for row in self.conn.execute(select([self.users]))] addrs = [row for row in self.conn.execute(select([self.addresses]))] prod = [row for row in self.conn.execute(select([self.users, self.addresses]))] assert(len(prod) == len(usrs) * len(addrs)) # inner join on id s = select([self.users, self.addresses]).where(self.users.c.uid == self.addresses.c.user_id) inner = [row for row in self.conn.execute(s)] assert(len(inner) == 4) # operators between columns objects & other col objects/literals expr = self.users.c.uid == self.addresses.c.user_id assert('my_users.uid = addresses.user_id' == str(expr)) # see how Teradata concats two strings assert(str((self.users.c.name + self.users.c.fullname).compile(bind=self.engine)) == 'my_users.name || my_users.fullname') # built-in conjunctions from sqlalchemy.sql import and_, or_ s = select([(self.users.c.fullname + ", " + self.addresses.c.email_address).label('titles')]).where( and_( self.users.c.uid == self.addresses.c.user_id, self.users.c.name.between('m', 'z'), or_( self.addresses.c.email_address.like('*****@*****.**'), self.addresses.c.email_address.like('*****@*****.**') ) ) ) # print(s) res = self.conn.execute(s) for row in res: assert(str(row[0]) == u'Wendy Williams, [email protected]') # more joins # ON condition auto generated based on ForeignKey assert(str(self.users.join(self.addresses)) == 'my_users JOIN addresses ON my_users.uid = addresses.user_id') # specify the join ON condition self.users.join(self.addresses, self.addresses.c.email_address.like(self.users.c.name + '%')) # select from clause to specify tables and the ON condition s = select([self.users.c.fullname]).select_from( self.users.join(self.addresses, self.addresses.c.email_address.like(self.users.c.name + '%'))) res = self.conn.execute(s) assert(len(res.fetchall()) == 3) # left outer joins s = select([self.users.c.fullname]).select_from(self.users.outerjoin(self.addresses)) # outer join works with teradata dialect (unlike oracle dialect < version9) assert(str(s) == str(s.compile(dialect=self.dialect))) # test bind params (positional) from sqlalchemy import text s = self.users.select(self.users.c.name.like( bindparam('username', type_=String)+text("'%'"))) res = self.conn.execute(s, username='******').fetchall() assert(len(res), 1) # functions from sqlalchemy.sql import func, column # certain function names are known by sqlalchemy assert(str(func.current_timestamp()), 'CURRENT_TIMESTAMP') # functions can be used in the select res = self.conn.execute(select( [func.max(self.addresses.c.email_address, type_=String).label( 'max_email')])).scalar() assert(res, '*****@*****.**') # func result sets, define a function taking params x,y return q,z,r # useful for nested queries, subqueries - w/ dynamic params calculate = select([column('q'), column('z'), column('r')]).\ select_from( func.calculate( bindparam('x'), bindparam('y') ) ) calc = calculate.alias() s = select([self.users]).where(self.users.c.uid > calc.c.z) assert('SELECT my_users.uid, my_users.name, my_users.fullname\ FROM my_users, (SELECT q, z, r\ FROM calculate(:x, :y)) AS anon_1\ WHERE my_users.uid > anon_1.z', s) # instantiate the func calc1 = calculate.alias('c1').unique_params(x=17, y=45) calc2 = calculate.alias('c2').unique_params(x=5, y=12) s = select([self.users]).where(self.users.c.uid.between(calc1.c.z, calc2.c.z)) parms = s.compile().params assert('x_2' in parms, 'x_1' in parms) assert('y_2' in parms, 'y_1' in parms) assert(parms['x_1'] == 17, parms['y_1'] == 45) assert(parms['x_2'] == 5, parms['y_2'] == 12) # order by asc stmt = select([self.users.c.name]).order_by(self.users.c.name) res = self.conn.execute(stmt).fetchall() assert('jack' == res[0][0]) assert('wendy' == res[1][0]) # order by desc stmt = select([self.users.c.name]).order_by(self.users.c.name.desc()) res = self.conn.execute(stmt).fetchall() assert('wendy' == res[0][0]) assert('jack' == res[1][0]) # group by stmt = select([self.users.c.name, func.count(self.addresses.c.id)]).\ select_from(self.users.join(self.addresses)).\ group_by(self.users.c.name) res = self.conn.execute(stmt).fetchall() assert(res[1][0] == 'jack') assert(res[0][0] == 'wendy') assert(res[0][1] == res[1][1]) # group by having stmt = select([self.users.c.name, func.count(self.addresses.c.id)]).\ select_from(self.users.join(self.addresses)).\ group_by(self.users.c.name).\ having(func.length(self.users.c.name) > 4) res = self.conn.execute(stmt).fetchall() assert(res[0] == ('wendy', 2)) # distinct stmt = select([self.users.c.name]).\ where(self.addresses.c.email_address.contains(self.users.c.name)).distinct() res = self.conn.execute(stmt).fetchall() assert(len(res) == 2) assert(res[0][0] != res[1][0]) # limit stmt = select([self.users.c.name, self.addresses.c.email_address]).\ select_from(self.users.join(self.addresses)).\ limit(1) res = self.conn.execute(stmt).fetchall() assert(len(res) == 1) # offset # test union and except from sqlalchemy.sql import except_, union u = union( self.addresses.select().where(self.addresses.c.email_address == '*****@*****.**'), self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**')),)# .order_by(self.addresses.c.email_address) # print(u) # #res = self.conn.execute(u) this fails, syntax error order by expects pos integer? u = except_( self.addresses.select().where(self.addresses.c.email_address.like('%@%.com')), self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**'))) res = self.conn.execute(u).fetchall() assert(1, len(res)) u = except_( union( self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**')), self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**')) ).alias().select(), self.addresses.select(self.addresses.c.email_address.like('*****@*****.**')) ) res = self.conn.execute(u).fetchall() assert(1, len(res)) # scalar subqueries stmt = select([func.count(self.addresses.c.id)]).where(self.users.c.uid == self.addresses.c.user_id).as_scalar() # we can place stmt as any other column within another select res = self.conn.execute(select([self.users.c.name, stmt])).fetchall() # res is a list of tuples, one tuple per user's name assert(2, len(res)) u1 = res[0] u2 = res[1] assert(len(u1) == len(u2)) assert(u1[0] == u'jack') assert(u1[1] == u2[1]) assert(u2[0] == u'wendy') # we can label the inner query stmt = select([func.count(self.addresses.c.id)]).\ where(self.users.c.uid == self.addresses.c.user_id).\ label("address_count") res = self.conn.execute(select([self.users.c.name, stmt])).fetchall() assert(2, len(res)) u1 = res[0] u2 = res[1] assert(len(u1) == 2) assert(len(u2) == 2) # inserts, updates, deletes stmt = self.users.update().values(fullname="Fullname: " + self.users.c.name) res = self.conn.execute(stmt) assert('name_1' in res.last_updated_params()) assert(res.last_updated_params()['name_1'] == 'Fullname: ') stmt = self.users.insert().values(name=bindparam('_name') + " .. name") res = self.conn.execute(stmt, [{'uid': 4, '_name': 'name1'}, {'uid': 5, '_name': 'name2'}, {'uid': 6, '_name': 'name3'}, ]) # updates stmt = self.users.update().where(self.users.c.name == 'jack').values(name='ed') res = self.conn.execute(stmt) assert(res.rowcount == 1) assert(res.returns_rows is False) # update many with bound params stmt = self.users.update().where(self.users.c.name == bindparam('oldname')).\ values(name=bindparam('newname')) res = self.conn.execute(stmt, [ {'oldname': 'jack', 'newname': 'ed'}, {'oldname': 'wendy', 'newname': 'mary'}, ]) assert(res.returns_rows is False) assert(res.rowcount == 1) res = self.conn.execute(select([self.users]).where(self.users.c.name == 'ed')) r = res.fetchone() assert(r['name'] == 'ed') # correlated updates stmt = select([self.addresses.c.email_address]).\ where(self.addresses.c.user_id == self.users.c.uid).\ limit(1) # this fails, syntax error bc of LIMIT - need TOP/SAMPLE instead # Note: TOP can't be in a subquery # res = self.conn.execute(self.users.update().values(fullname=stmt)) # multiple table updates stmt = self.users.update().\ values(name='ed wood').\ where(self.users.c.uid == self.addresses.c.id).\ where(self.addresses.c.email_address.startswith('ed%')) # this fails, teradata does update from set where not update set from where # #res = self.conn.execute(stmt) stmt = self.users.update().\ values({ self.users.c.name: 'ed wood', self.addresses.c.email_address: '*****@*****.**' }).\ where(self.users.c.uid == self.addresses.c.id).\ where(self.addresses.c.email_address.startswith('ed%')) # fails but works on MySQL, should this work for us? # #res = self.conn.execute(stmt) # deletes self.conn.execute(self.addresses.delete()) self.conn.execute(self.users.delete().where(self.users.c.name > 'm')) # matched row counts # updates + deletes have a number indicating # rows matched by WHERE clause res = self.conn.execute(self.users.delete()) assert(res.rowcount == 1)
def test_with_inheritance(self): metadata = MetaData() table1 = Table( "mytable", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", String(30)), ) table2 = Table( "mytable2", metadata, Column( "col1", Integer, ForeignKey("mytable.col1"), primary_key=True, test_needs_autoincrement=True, ), Column("col3", String(30)), ) @profile_memory() def go(): class A(fixtures.ComparableEntity): pass class B(A): pass clear_mappers() self.mapper_registry.map_imperatively( A, table1, polymorphic_on=table1.c.col2, polymorphic_identity="a", ) self.mapper_registry.map_imperatively(B, table2, inherits=A, polymorphic_identity="b") sess = Session(self.engine, autoflush=False) a1 = A() a2 = A() b1 = B(col3="b1") b2 = B(col3="b2") for x in [a1, a2, b1, b2]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_([A(), A(), B(col3="b1"), B(col3="b2")], alist) for a in alist: sess.delete(a) sess.flush() # don't need to clear_mappers() del B del A metadata.create_all(self.engine) try: go() finally: metadata.drop_all(self.engine) assert_no_mappers()
def test_mapper_reset(self): metadata = MetaData() table1 = Table( "mytable", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", String(30)), ) table2 = Table( "mytable2", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", String(30)), Column("col3", Integer, ForeignKey("mytable.col1")), ) @profile_memory() def go(): self.mapper_registry.map_imperatively( A, table1, properties={"bs": relationship(B, order_by=table2.c.col1)}, ) self.mapper_registry.map_imperatively(B, table2) sess = Session(self.engine, autoflush=False) a1 = A(col2="a1") a2 = A(col2="a2") a3 = A(col2="a3") a1.bs.append(B(col2="b1")) a1.bs.append(B(col2="b2")) a3.bs.append(B(col2="b3")) for x in [a1, a2, a3]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_( [ A(col2="a1", bs=[B(col2="b1"), B(col2="b2")]), A(col2="a2", bs=[]), A(col2="a3", bs=[B(col2="b3")]), ], alist, ) for a in alist: sess.delete(a) sess.flush() sess.close() clear_mappers() metadata.create_all(self.engine) try: go() finally: metadata.drop_all(self.engine) assert_no_mappers()
def test_session(self): metadata = MetaData() table1 = Table( "mytable", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", String(30)), ) table2 = Table( "mytable2", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", String(30)), Column("col3", Integer, ForeignKey("mytable.col1")), ) metadata.create_all(self.engine) m1 = self.mapper_registry.map_imperatively( A, table1, properties={ "bs": relationship(B, cascade="all, delete", order_by=table2.c.col1) }, ) m2 = self.mapper_registry.map_imperatively(B, table2) @profile_memory() def go(): with Session(self.engine) as sess: a1 = A(col2="a1") a2 = A(col2="a2") a3 = A(col2="a3") a1.bs.append(B(col2="b1")) a1.bs.append(B(col2="b2")) a3.bs.append(B(col2="b3")) for x in [a1, a2, a3]: sess.add(x) sess.commit() alist = sess.query(A).order_by(A.col1).all() eq_( [ A(col2="a1", bs=[B(col2="b1"), B(col2="b2")]), A(col2="a2", bs=[]), A(col2="a3", bs=[B(col2="b3")]), ], alist, ) for a in alist: sess.delete(a) sess.commit() go() metadata.drop_all(self.engine) del m1, m2 assert_no_mappers()
class PostgresqlDefaultCompareTest(TestBase): __only_on__ = "postgresql" __backend__ = True @classmethod def setup_class(cls): cls.bind = config.db staging_env() cls.migration_context = MigrationContext.configure( connection=cls.bind.connect(), opts={ "compare_type": True, "compare_server_default": True }, ) def setUp(self): self.metadata = MetaData(self.bind) self.autogen_context = api.AutogenContext(self.migration_context) @classmethod def teardown_class(cls): clear_staging_env() def tearDown(self): self.metadata.drop_all() def _compare_default_roundtrip(self, type_, orig_default, alternate=None, diff_expected=None): diff_expected = (diff_expected if diff_expected is not None else alternate is not None) if alternate is None: alternate = orig_default t1 = Table( "test", self.metadata, Column("somecol", type_, server_default=orig_default), ) t2 = Table( "test", MetaData(), Column("somecol", type_, server_default=alternate), ) t1.create(self.bind) insp = inspect(self.bind) cols = insp.get_columns(t1.name) insp_col = Column("somecol", cols[0]["type"], server_default=text(cols[0]["default"])) op = ops.AlterColumnOp("test", "somecol") _compare_server_default( self.autogen_context, op, None, "test", "somecol", insp_col, t2.c.somecol, ) diffs = op.to_diff_tuple() eq_(bool(diffs), diff_expected) def _compare_default(self, t1, t2, col, rendered): t1.create(self.bind, checkfirst=True) insp = inspect(self.bind) cols = insp.get_columns(t1.name) ctx = self.autogen_context.migration_context return ctx.impl.compare_server_default(None, col, rendered, cols[0]["default"]) def test_compare_string_blank_default(self): self._compare_default_roundtrip(String(8), "") def test_compare_string_nonblank_default(self): self._compare_default_roundtrip(String(8), "hi") def test_compare_interval_str(self): # this form shouldn't be used but testing here # for compatibility self._compare_default_roundtrip(Interval, "14 days") @config.requirements.postgresql_uuid_ossp def test_compare_uuid_text(self): self._compare_default_roundtrip(UUID, text("uuid_generate_v4()")) def test_compare_interval_text(self): self._compare_default_roundtrip(Interval, text("'14 days'")) def test_compare_array_of_integer_text(self): self._compare_default_roundtrip(ARRAY(Integer), text("(ARRAY[]::integer[])")) def test_compare_current_timestamp_text(self): self._compare_default_roundtrip( DateTime(), text("TIMEZONE('utc', CURRENT_TIMESTAMP)")) def test_compare_current_timestamp_fn_w_binds(self): self._compare_default_roundtrip( DateTime(), func.timezone("utc", func.current_timestamp())) def test_compare_integer_str(self): self._compare_default_roundtrip(Integer(), "5") def test_compare_integer_text(self): self._compare_default_roundtrip(Integer(), text("5")) def test_compare_integer_text_diff(self): self._compare_default_roundtrip(Integer(), text("5"), "7") def test_compare_float_str(self): self._compare_default_roundtrip(Float(), "5.2") def test_compare_float_text(self): self._compare_default_roundtrip(Float(), text("5.2")) def test_compare_float_no_diff1(self): self._compare_default_roundtrip(Float(), text("5.2"), "5.2", diff_expected=False) def test_compare_float_no_diff2(self): self._compare_default_roundtrip(Float(), "5.2", text("5.2"), diff_expected=False) def test_compare_float_no_diff3(self): self._compare_default_roundtrip(Float(), text("5"), text("5.0"), diff_expected=False) def test_compare_float_no_diff4(self): self._compare_default_roundtrip(Float(), "5", "5.0", diff_expected=False) def test_compare_float_no_diff5(self): self._compare_default_roundtrip(Float(), text("5"), "5.0", diff_expected=False) def test_compare_float_no_diff6(self): self._compare_default_roundtrip(Float(), "5", text("5.0"), diff_expected=False) def test_compare_numeric_no_diff(self): self._compare_default_roundtrip(Numeric(), text("5"), "5.0", diff_expected=False) def test_compare_unicode_literal(self): self._compare_default_roundtrip(String(), u"im a default") # TOOD: will need to actually eval() the repr() and # spend more effort figuring out exactly the kind of expression # to use def _TODO_test_compare_character_str_w_singlequote(self): self._compare_default_roundtrip(String(), "hel''lo") def test_compare_character_str(self): self._compare_default_roundtrip(String(), "hello") def test_compare_character_text(self): self._compare_default_roundtrip(String(), text("'hello'")) def test_compare_character_str_diff(self): self._compare_default_roundtrip(String(), "hello", "there") def test_compare_character_text_diff(self): self._compare_default_roundtrip(String(), text("'hello'"), text("'there'")) def test_primary_key_skip(self): """Test that SERIAL cols are just skipped""" t1 = Table("sometable", self.metadata, Column("id", Integer, primary_key=True)) t2 = Table("sometable", MetaData(), Column("id", Integer, primary_key=True)) assert not self._compare_default(t1, t2, t2.c.id, "")
q = (session.query(Animal).filter( Animal.facts.any( and_(AnimalFact.key == u'color', AnimalFact.value == u'reddish')))) print 'reddish animals', q.all() # Save some typing by wrapping that up in a function: with_characteristic = lambda key, value: and_(AnimalFact.key == key, AnimalFact.value == value) q = (session.query(Animal).filter( Animal.facts.any(with_characteristic(u'color', u'brown')))) print 'brown animals', q.all() q = (session.query(Animal).filter( not_( Animal.facts.any(with_characteristic(u'poisonous-part', u'elbows'))))) print 'animals without poisonous-part == elbows', q.all() q = (session.query(Animal).filter( Animal.facts.any(AnimalFact.value == u'somewhat'))) print 'any animal with any .value of "somewhat"', q.all() # Facts can be queried as well. q = (session.query(AnimalFact).filter( with_characteristic(u'cuteness', u'very'))) print 'just the facts', q.all() metadata.drop_all()
def test_autoincrement(self): meta = MetaData(testing.db) try: Table( "ai_1", meta, Column("int_y", Integer, primary_key=True, autoincrement=True), Column("int_n", Integer, DefaultClause("0"), primary_key=True), mysql_engine="MyISAM", ) Table( "ai_2", meta, Column("int_y", Integer, primary_key=True, autoincrement=True), Column("int_n", Integer, DefaultClause("0"), primary_key=True), mysql_engine="MyISAM", ) Table( "ai_3", meta, Column( "int_n", Integer, DefaultClause("0"), primary_key=True, autoincrement=False, ), Column("int_y", Integer, primary_key=True, autoincrement=True), mysql_engine="MyISAM", ) Table( "ai_4", meta, Column( "int_n", Integer, DefaultClause("0"), primary_key=True, autoincrement=False, ), Column( "int_n2", Integer, DefaultClause("0"), primary_key=True, autoincrement=False, ), mysql_engine="MyISAM", ) Table( "ai_5", meta, Column("int_y", Integer, primary_key=True, autoincrement=True), Column( "int_n", Integer, DefaultClause("0"), primary_key=True, autoincrement=False, ), mysql_engine="MyISAM", ) Table( "ai_6", meta, Column("o1", String(1), DefaultClause("x"), primary_key=True), Column("int_y", Integer, primary_key=True, autoincrement=True), mysql_engine="MyISAM", ) Table( "ai_7", meta, Column("o1", String(1), DefaultClause("x"), primary_key=True), Column("o2", String(1), DefaultClause("x"), primary_key=True), Column("int_y", Integer, primary_key=True, autoincrement=True), mysql_engine="MyISAM", ) Table( "ai_8", meta, Column("o1", String(1), DefaultClause("x"), primary_key=True), Column("o2", String(1), DefaultClause("x"), primary_key=True), mysql_engine="MyISAM", ) meta.create_all() table_names = [ "ai_1", "ai_2", "ai_3", "ai_4", "ai_5", "ai_6", "ai_7", "ai_8", ] mr = MetaData(testing.db) mr.reflect(only=table_names) for tbl in [mr.tables[name] for name in table_names]: for c in tbl.c: if c.name.startswith("int_y"): assert c.autoincrement elif c.name.startswith("int_n"): assert not c.autoincrement tbl.insert().execute() if "int_y" in tbl.c: assert select([tbl.c.int_y]).scalar() == 1 assert list(tbl.select().execute().first()).count(1) == 1 else: assert 1 not in list(tbl.select().execute().first()) finally: meta.drop_all()
def test_with_manytomany(self): metadata = MetaData(self.engine) table1 = Table( "mytable", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30))) table2 = Table( "mytable2", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30)), ) table3 = Table( 't1tot2', metadata, Column('t1', Integer, ForeignKey('mytable.col1')), Column('t2', Integer, ForeignKey('mytable2.col1')), ) @profile_memory() def go(): class A(fixtures.ComparableEntity): pass class B(fixtures.ComparableEntity): pass mapper(A, table1, properties={ 'bs': relationship(B, secondary=table3, backref='as', order_by=table3.c.t1) }) mapper(B, table2) sess = create_session() a1 = A(col2='a1') a2 = A(col2='a2') b1 = B(col2='b1') b2 = B(col2='b2') a1.bs.append(b1) a2.bs.append(b2) for x in [a1, a2]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_([A(bs=[B(col2='b1')]), A(bs=[B(col2='b2')])], alist) for a in alist: sess.delete(a) sess.flush() # don't need to clear_mappers() del B del A metadata.create_all() try: go() finally: metadata.drop_all() assert_no_mappers()
def test_with_inheritance(self): metadata = MetaData(self.engine) table1 = Table( "mytable", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30))) table2 = Table( "mytable2", metadata, Column('col1', Integer, ForeignKey('mytable.col1'), primary_key=True, test_needs_autoincrement=True), Column('col3', String(30)), ) @profile_memory() def go(): class A(fixtures.ComparableEntity): pass class B(A): pass mapper(A, table1, polymorphic_on=table1.c.col2, polymorphic_identity='a') mapper(B, table2, inherits=A, polymorphic_identity='b') sess = create_session() a1 = A() a2 = A() b1 = B(col3='b1') b2 = B(col3='b2') for x in [a1, a2, b1, b2]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_([A(), A(), B(col3='b1'), B(col3='b2')], alist) for a in alist: sess.delete(a) sess.flush() # don't need to clear_mappers() del B del A metadata.create_all() try: go() finally: metadata.drop_all() assert_no_mappers()
def test_session(self): metadata = MetaData(self.engine) table1 = Table( "mytable", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30))) table2 = Table( "mytable2", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30)), Column('col3', Integer, ForeignKey("mytable.col1"))) metadata.create_all() m1 = mapper(A, table1, properties={ "bs": relationship(B, cascade="all, delete", order_by=table2.c.col1) }) m2 = mapper(B, table2) m3 = mapper(A, table1, non_primary=True) @profile_memory() def go(): sess = create_session() a1 = A(col2="a1") a2 = A(col2="a2") a3 = A(col2="a3") a1.bs.append(B(col2="b1")) a1.bs.append(B(col2="b2")) a3.bs.append(B(col2="b3")) for x in [a1, a2, a3]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_([ A(col2="a1", bs=[B(col2="b1"), B(col2="b2")]), A(col2="a2", bs=[]), A(col2="a3", bs=[B(col2="b3")]) ], alist) for a in alist: sess.delete(a) sess.flush() go() metadata.drop_all() del m1, m2, m3 assert_no_mappers()
def parse(file, output="csv", edges_name="edges", nodes_name="nodes", output_path=".", separator=",", spatial=False, no_headers=False): if not os.path.exists(file): raise IOError("File {0} not found".format(file)) if output != "csv": metadata = MetaData() if (spatial): node_geom = Point(2) edge_geom = LineString(2) else: node_geom = String edge_geom = String nodes_table = Table(nodes_name, metadata, Column('id', Integer, primary_key=True), Column('original_id', Integer, index=True), Column('elevation', Integer), Column('lon', Float, index=True), Column('lat', Float, index=True), Column('the_geom', node_geom)) edges_table = Table(edges_name, metadata, Column('id', Integer, primary_key=True), Column('source', Integer, index=True), Column('target', Integer, index=True), Column('length', Float), Column('car', SmallInteger), Column('car_rev', SmallInteger), Column('bike', SmallInteger), Column('bike_rev', SmallInteger), Column('foot', SmallInteger), Column('the_geom', edge_geom)) GeometryDDL(nodes_table) GeometryDDL(edges_table) engine = create_engine(output) metadata.drop_all(engine) metadata.create_all(engine) mapper(Node, nodes_table) mapper(Edge, edges_table) Session = sessionmaker(bind=engine) session = Session() extension = os.path.splitext(file)[1] if extension == '.bz2': print "Recognized as bzip2 file" f = bz2.BZ2File(file, 'r') elif extension == '.gz': print "Recognized as gzip2 file" f = gzip.open(file, 'r') else: print "Supposing OSM/xml file" filesize = os.path.getsize(file) f = open(file, 'r') buffer_size = 4096 p = Parser() eof = False print "Step 1: reading file {0}".format(file) read = 0 while not eof: s = f.read(buffer_size) eof = len(s) != buffer_size p.read(s, len(s), eof) read += len(s) print " Read {0} nodes and {1} ways\n".format(p.get_osm_nodes(), p.get_osm_ways()) print "Step 2: saving the nodes" nodes = p.get_nodes() if output == "csv": n = open(output_path + '/' + nodes_name + '.csv', 'w') if no_headers == False: n.write('"node_id"' + separator + '"longitude"' + separator + '"latitude"\n') count = 0 for node in nodes: if output == "csv": n.write('{1}{0}{2}{0}{3}\n'.format(separator, node.id, node.lon, node.lat)) else: session.add(Node(node.id, node.lon, node.lat, spatial=spatial)) count += 1 if output == "csv": n.close() else: session.commit() print " Wrote {0} nodes\n".format(count) print "Step 3: saving the edges" edges = p.get_edges() count = 0 if output == "csv": print output_path + '/' + edges_name + '.csv' e = open(output_path + '/' + edges_name + '.csv', 'w') if no_headers == False: e.write('"edge_id"' + separator + '"source"' + separator + '"target"' + separator + '"length"' + separator + '"car"' + separator + '"car reverse"' + separator + '"bike"' + separator + '"bike reverse"' + separator + '"foot"' + separator + '"WKT"\n') for edge in edges: if output == "csv": e.write( '{1}{0}{2}{0}{3}{0}{4}{0}{5}{0}{6}{0}{7}{0}{8}{0}{9}{0}LINESTRING({10})\n' .format(separator, edge.edge_id, edge.source, edge.target, edge.length, edge.car, edge.car_d, edge.bike, edge.bike_d, edge.foot, edge.geom)) else: session.add( Edge(edge.edge_id, edge.source, edge.target, edge.length, edge.car, edge.car_d, edge.bike, edge.bike_d, edge.foot, edge.geom, spatial=spatial)) count += 1 if output == "csv": e.close() else: session.commit() print " Wrote {0} edges\n".format(count)
class SQLiteDefaultCompareTest(TestBase): __only_on__ = "sqlite" __backend__ = True @classmethod def setup_class(cls): cls.bind = config.db staging_env() cls.migration_context = MigrationContext.configure( connection=cls.bind.connect(), opts={ "compare_type": True, "compare_server_default": True }, ) def setUp(self): self.metadata = MetaData(self.bind) self.autogen_context = api.AutogenContext(self.migration_context) @classmethod def teardown_class(cls): clear_staging_env() def tearDown(self): self.metadata.drop_all() def _compare_default_roundtrip(self, type_, orig_default, alternate=None, diff_expected=None): diff_expected = (diff_expected if diff_expected is not None else alternate is not None) if alternate is None: alternate = orig_default t1 = Table( "test", self.metadata, Column("somecol", type_, server_default=orig_default), ) t2 = Table( "test", MetaData(), Column("somecol", type_, server_default=alternate), ) t1.create(self.bind) insp = inspect(self.bind) cols = insp.get_columns(t1.name) insp_col = Column("somecol", cols[0]["type"], server_default=text(cols[0]["default"])) op = ops.AlterColumnOp("test", "somecol") _compare_server_default( self.autogen_context, op, None, "test", "somecol", insp_col, t2.c.somecol, ) diffs = op.to_diff_tuple() eq_(bool(diffs), diff_expected) def _compare_default(self, t1, t2, col, rendered): t1.create(self.bind, checkfirst=True) insp = inspect(self.bind) cols = insp.get_columns(t1.name) ctx = self.autogen_context.migration_context return ctx.impl.compare_server_default(None, col, rendered, cols[0]["default"]) @config.requirements.sqlalchemy_12 def test_compare_current_timestamp_func(self): self._compare_default_roundtrip(DateTime(), func.datetime("now", "localtime")) @config.requirements.sqlalchemy_12 def test_compare_current_timestamp_func_now(self): self._compare_default_roundtrip(DateTime(), func.now()) def test_compare_current_timestamp_text(self): # SQLAlchemy doesn't render the parenthesis for a # SQLite server default specified as text(), so users will be doing # this; sqlite comparison needs to accommodate for these. self._compare_default_roundtrip(DateTime(), text("(datetime('now', 'localtime'))")) def test_compare_integer_str(self): self._compare_default_roundtrip(Integer(), "5") def test_compare_integer_str_diff(self): self._compare_default_roundtrip(Integer(), "5", "7") def test_compare_integer_text(self): self._compare_default_roundtrip(Integer(), text("5")) def test_compare_integer_text_diff(self): self._compare_default_roundtrip(Integer(), text("5"), "7") def test_compare_float_str(self): self._compare_default_roundtrip(Float(), "5.2") def test_compare_float_str_diff(self): self._compare_default_roundtrip(Float(), "5.2", "5.3") def test_compare_float_text(self): self._compare_default_roundtrip(Float(), text("5.2")) def test_compare_float_text_diff(self): self._compare_default_roundtrip(Float(), text("5.2"), "5.3") def test_compare_string_literal(self): self._compare_default_roundtrip(String(), "im a default") def test_compare_string_literal_diff(self): self._compare_default_roundtrip(String(), "im a default", "me too")
class BatchRoundTripTest(TestBase): __requires__ = ('sqlalchemy_08', ) __only_on__ = "sqlite" def setUp(self): self.conn = config.db.connect() self.metadata = MetaData() t1 = Table('foo', self.metadata, Column('id', Integer, primary_key=True), Column('data', String(50)), Column('x', Integer), mysql_engine='InnoDB') t1.create(self.conn) self.conn.execute(t1.insert(), [{ "id": 1, "data": "d1", "x": 5 }, { "id": 2, "data": "22", "x": 6 }, { "id": 3, "data": "8.5", "x": 7 }, { "id": 4, "data": "9.46", "x": 8 }, { "id": 5, "data": "d5", "x": 9 }]) context = MigrationContext.configure(self.conn) self.op = Operations(context) @contextmanager def _sqlite_referential_integrity(self): self.conn.execute("PRAGMA foreign_keys=ON") try: yield finally: self.conn.execute("PRAGMA foreign_keys=OFF") def _no_pk_fixture(self): nopk = Table('nopk', self.metadata, Column('a', Integer), Column('b', Integer), Column('c', Integer), mysql_engine='InnoDB') nopk.create(self.conn) self.conn.execute(nopk.insert(), [ { "a": 1, "b": 2, "c": 3 }, { "a": 2, "b": 4, "c": 5 }, ]) return nopk def _table_w_index_fixture(self): t = Table( 't_w_ix', self.metadata, Column('id', Integer, primary_key=True), Column('thing', Integer), Column('data', String(20)), ) Index('ix_thing', t.c.thing) t.create(self.conn) return t def _boolean_fixture(self): t = Table('hasbool', self.metadata, Column('x', Boolean(create_constraint=True, name='ck1')), Column('y', Integer)) t.create(self.conn) def _int_to_boolean_fixture(self): t = Table('hasbool', self.metadata, Column('x', Integer)) t.create(self.conn) def test_change_type_boolean_to_int(self): self._boolean_fixture() with self.op.batch_alter_table("hasbool") as batch_op: batch_op.alter_column('x', type_=Integer, existing_type=Boolean(create_constraint=True, name='ck1')) insp = Inspector.from_engine(config.db) eq_([ c['type']._type_affinity for c in insp.get_columns('hasbool') if c['name'] == 'x' ], [Integer]) def test_drop_col_schematype(self): self._boolean_fixture() with self.op.batch_alter_table("hasbool") as batch_op: batch_op.drop_column('x') insp = Inspector.from_engine(config.db) assert 'x' not in (c['name'] for c in insp.get_columns('hasbool')) def test_change_type_int_to_boolean(self): self._int_to_boolean_fixture() with self.op.batch_alter_table("hasbool") as batch_op: batch_op.alter_column('x', type_=Boolean(create_constraint=True, name='ck1')) insp = Inspector.from_engine(config.db) if exclusions.against(config, "sqlite"): eq_([ c['type']._type_affinity for c in insp.get_columns('hasbool') if c['name'] == 'x' ], [Boolean]) elif exclusions.against(config, "mysql"): eq_([ c['type']._type_affinity for c in insp.get_columns('hasbool') if c['name'] == 'x' ], [Integer]) def tearDown(self): self.metadata.drop_all(self.conn) self.conn.close() def _assert_data(self, data, tablename='foo'): eq_([ dict(row) for row in self.conn.execute("select * from %s" % tablename) ], data) def test_ix_existing(self): self._table_w_index_fixture() with self.op.batch_alter_table("t_w_ix") as batch_op: batch_op.alter_column('data', type_=String(30)) batch_op.create_index("ix_data", ["data"]) insp = Inspector.from_engine(config.db) eq_( set((ix['name'], tuple(ix['column_names'])) for ix in insp.get_indexes('t_w_ix')), set([('ix_data', ('data', )), ('ix_thing', ('thing', ))])) def test_fk_points_to_me_auto(self): self._test_fk_points_to_me("auto") # in particular, this tests that the failures # on PG and MySQL result in recovery of the batch system, # e.g. that the _alembic_batch_temp table is dropped @config.requirements.no_referential_integrity def test_fk_points_to_me_recreate(self): self._test_fk_points_to_me("always") @exclusions.only_on("sqlite") @exclusions.fails("intentionally asserting that this " "doesn't work w/ pragma foreign keys") def test_fk_points_to_me_sqlite_refinteg(self): with self._sqlite_referential_integrity(): self._test_fk_points_to_me("auto") def _test_fk_points_to_me(self, recreate): bar = Table('bar', self.metadata, Column('id', Integer, primary_key=True), Column('foo_id', Integer, ForeignKey('foo.id')), mysql_engine='InnoDB') bar.create(self.conn) self.conn.execute(bar.insert(), {'id': 1, 'foo_id': 3}) with self.op.batch_alter_table("foo", recreate=recreate) as batch_op: batch_op.alter_column('data', new_column_name='newdata', existing_type=String(50)) insp = Inspector.from_engine(self.conn) eq_([(key['referred_table'], key['referred_columns'], key['constrained_columns']) for key in insp.get_foreign_keys('bar')], [('foo', ['id'], ['foo_id'])]) def test_selfref_fk_auto(self): self._test_selfref_fk("auto") @config.requirements.no_referential_integrity def test_selfref_fk_recreate(self): self._test_selfref_fk("always") @exclusions.only_on("sqlite") @exclusions.fails("intentionally asserting that this " "doesn't work w/ pragma foreign keys") def test_selfref_fk_sqlite_refinteg(self): with self._sqlite_referential_integrity(): self._test_selfref_fk("auto") def _test_selfref_fk(self, recreate): bar = Table('bar', self.metadata, Column('id', Integer, primary_key=True), Column('bar_id', Integer, ForeignKey('bar.id')), Column('data', String(50)), mysql_engine='InnoDB') bar.create(self.conn) self.conn.execute(bar.insert(), {'id': 1, 'data': 'x', 'bar_id': None}) self.conn.execute(bar.insert(), {'id': 2, 'data': 'y', 'bar_id': 1}) with self.op.batch_alter_table("bar", recreate=recreate) as batch_op: batch_op.alter_column('data', new_column_name='newdata', existing_type=String(50)) insp = Inspector.from_engine(self.conn) insp = Inspector.from_engine(self.conn) eq_([(key['referred_table'], key['referred_columns'], key['constrained_columns']) for key in insp.get_foreign_keys('bar')], [('bar', ['id'], ['bar_id'])]) def test_change_type(self): with self.op.batch_alter_table("foo") as batch_op: batch_op.alter_column('data', type_=Integer) self._assert_data([{ "id": 1, "data": 0, "x": 5 }, { "id": 2, "data": 22, "x": 6 }, { "id": 3, "data": 8, "x": 7 }, { "id": 4, "data": 9, "x": 8 }, { "id": 5, "data": 0, "x": 9 }]) def test_drop_column(self): with self.op.batch_alter_table("foo") as batch_op: batch_op.drop_column('data') self._assert_data([{ "id": 1, "x": 5 }, { "id": 2, "x": 6 }, { "id": 3, "x": 7 }, { "id": 4, "x": 8 }, { "id": 5, "x": 9 }]) def test_add_pk_constraint(self): self._no_pk_fixture() with self.op.batch_alter_table("nopk", recreate="always") as batch_op: batch_op.create_primary_key('newpk', ['a', 'b']) pk_const = Inspector.from_engine(self.conn).get_pk_constraint('nopk') with config.requirements.reflects_pk_names.fail_if(): eq_(pk_const['name'], 'newpk') eq_(pk_const['constrained_columns'], ['a', 'b']) @config.requirements.check_constraints_w_enforcement def test_add_ck_constraint(self): with self.op.batch_alter_table("foo", recreate="always") as batch_op: batch_op.create_check_constraint("newck", text("x > 0")) # we dont support reflection of CHECK constraints # so test this by just running invalid data in foo = self.metadata.tables['foo'] assert_raises_message(exc.IntegrityError, "newck", self.conn.execute, foo.insert(), { "id": 6, "data": 5, "x": -2 }) @config.requirements.sqlalchemy_094 @config.requirements.unnamed_constraints def test_drop_foreign_key(self): bar = Table('bar', self.metadata, Column('id', Integer, primary_key=True), Column('foo_id', Integer, ForeignKey('foo.id')), mysql_engine='InnoDB') bar.create(self.conn) self.conn.execute(bar.insert(), {'id': 1, 'foo_id': 3}) naming_convention = { "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", } with self.op.batch_alter_table( "bar", naming_convention=naming_convention) as batch_op: batch_op.drop_constraint("fk_bar_foo_id_foo", type_="foreignkey") eq_(Inspector.from_engine(self.conn).get_foreign_keys('bar'), []) def test_drop_column_fk_recreate(self): with self.op.batch_alter_table("foo", recreate='always') as batch_op: batch_op.drop_column('data') self._assert_data([{ "id": 1, "x": 5 }, { "id": 2, "x": 6 }, { "id": 3, "x": 7 }, { "id": 4, "x": 8 }, { "id": 5, "x": 9 }]) def test_rename_column(self): with self.op.batch_alter_table("foo") as batch_op: batch_op.alter_column('x', new_column_name='y') self._assert_data([{ "id": 1, "data": "d1", "y": 5 }, { "id": 2, "data": "22", "y": 6 }, { "id": 3, "data": "8.5", "y": 7 }, { "id": 4, "data": "9.46", "y": 8 }, { "id": 5, "data": "d5", "y": 9 }]) def test_rename_column_boolean(self): bar = Table('bar', self.metadata, Column('id', Integer, primary_key=True), Column('flag', Boolean()), mysql_engine='InnoDB') bar.create(self.conn) self.conn.execute(bar.insert(), {'id': 1, 'flag': True}) self.conn.execute(bar.insert(), {'id': 2, 'flag': False}) with self.op.batch_alter_table("bar") as batch_op: batch_op.alter_column('flag', new_column_name='bflag', existing_type=Boolean) self._assert_data([ { "id": 1, 'bflag': True }, { "id": 2, 'bflag': False }, ], 'bar') @config.requirements.non_native_boolean def test_rename_column_non_native_boolean_no_ck(self): bar = Table('bar', self.metadata, Column('id', Integer, primary_key=True), Column('flag', Boolean(create_constraint=False)), mysql_engine='InnoDB') bar.create(self.conn) self.conn.execute(bar.insert(), {'id': 1, 'flag': True}) self.conn.execute(bar.insert(), {'id': 2, 'flag': False}) self.conn.execute(bar.insert(), {'id': 3, 'flag': 5}) with self.op.batch_alter_table( "bar", reflect_args=[ Column('flag', Boolean(create_constraint=False)) ]) as batch_op: batch_op.alter_column('flag', new_column_name='bflag', existing_type=Boolean) self._assert_data([{ "id": 1, 'bflag': True }, { "id": 2, 'bflag': False }, { 'id': 3, 'bflag': 5 }], 'bar') def test_drop_column_pk(self): with self.op.batch_alter_table("foo") as batch_op: batch_op.drop_column('id') self._assert_data([{ "data": "d1", "x": 5 }, { "data": "22", "x": 6 }, { "data": "8.5", "x": 7 }, { "data": "9.46", "x": 8 }, { "data": "d5", "x": 9 }]) def test_rename_column_pk(self): with self.op.batch_alter_table("foo") as batch_op: batch_op.alter_column('id', new_column_name='ident') self._assert_data([{ "ident": 1, "data": "d1", "x": 5 }, { "ident": 2, "data": "22", "x": 6 }, { "ident": 3, "data": "8.5", "x": 7 }, { "ident": 4, "data": "9.46", "x": 8 }, { "ident": 5, "data": "d5", "x": 9 }]) def test_add_column_auto(self): # note this uses ALTER with self.op.batch_alter_table("foo") as batch_op: batch_op.add_column( Column('data2', String(50), server_default='hi')) self._assert_data([{ "id": 1, "data": "d1", "x": 5, 'data2': 'hi' }, { "id": 2, "data": "22", "x": 6, 'data2': 'hi' }, { "id": 3, "data": "8.5", "x": 7, 'data2': 'hi' }, { "id": 4, "data": "9.46", "x": 8, 'data2': 'hi' }, { "id": 5, "data": "d5", "x": 9, 'data2': 'hi' }]) def test_add_column_recreate(self): with self.op.batch_alter_table("foo", recreate='always') as batch_op: batch_op.add_column( Column('data2', String(50), server_default='hi')) self._assert_data([{ "id": 1, "data": "d1", "x": 5, 'data2': 'hi' }, { "id": 2, "data": "22", "x": 6, 'data2': 'hi' }, { "id": 3, "data": "8.5", "x": 7, 'data2': 'hi' }, { "id": 4, "data": "9.46", "x": 8, 'data2': 'hi' }, { "id": 5, "data": "d5", "x": 9, 'data2': 'hi' }]) def test_create_drop_index(self): insp = Inspector.from_engine(config.db) eq_(insp.get_indexes('foo'), []) with self.op.batch_alter_table("foo", recreate='always') as batch_op: batch_op.create_index('ix_data', ['data'], unique=True) self._assert_data([{ "id": 1, "data": "d1", "x": 5 }, { "id": 2, "data": "22", "x": 6 }, { "id": 3, "data": "8.5", "x": 7 }, { "id": 4, "data": "9.46", "x": 8 }, { "id": 5, "data": "d5", "x": 9 }]) insp = Inspector.from_engine(config.db) eq_([ dict(unique=ix['unique'], name=ix['name'], column_names=ix['column_names']) for ix in insp.get_indexes('foo') ], [{ 'unique': True, 'name': 'ix_data', 'column_names': ['data'] }]) with self.op.batch_alter_table("foo", recreate='always') as batch_op: batch_op.drop_index('ix_data') insp = Inspector.from_engine(config.db) eq_(insp.get_indexes('foo'), [])
class ReconnectRecipeTest(fixtures.TestBase): """Test for the reconnect recipe given at doc/build/faq/connections.rst. Make sure the above document is updated if changes are made here. """ # this recipe works on PostgreSQL also but only if the connection # is cut off from the server side, otherwise the connection.cursor() # method rightly fails because we explicitly closed the connection. # since we don't have a fixture # that can do this we currently rely on the MySQL drivers that allow # us to call cursor() even when the connection were closed. In order # to get a real "cut the server off" kind of fixture we'd need to do # something in provisioning that seeks out the TCP connection at the # OS level and kills it. __only_on__ = ("mysql+mysqldb", "mysql+pymysql") future = False def make_engine(self, engine): num_retries = 3 retry_interval = 0.5 def _run_with_retries(fn, context, cursor, statement, *arg, **kw): for retry in range(num_retries + 1): try: fn(cursor, statement, context=context, *arg) except engine.dialect.dbapi.Error as raw_dbapi_err: connection = context.root_connection if engine.dialect.is_disconnect(raw_dbapi_err, connection, cursor): if retry > num_retries: raise engine.logger.error( "disconnection error, retrying operation", exc_info=True, ) connection.invalidate() if self.future: connection.rollback() else: trans = connection.get_transaction() if trans: trans.rollback() time.sleep(retry_interval) context.cursor = ( cursor) = connection.connection.cursor() else: raise else: return True e = engine.execution_options(isolation_level="AUTOCOMMIT") @event.listens_for(e, "do_execute_no_params") def do_execute_no_params(cursor, statement, context): return _run_with_retries( context.dialect.do_execute_no_params, context, cursor, statement, ) @event.listens_for(e, "do_execute") def do_execute(cursor, statement, parameters, context): return _run_with_retries( context.dialect.do_execute, context, cursor, statement, parameters, ) return e __backend__ = True def setup_test(self): self.engine = engines.reconnecting_engine(options=dict( future=self.future)) self.meta = MetaData() self.table = Table( "sometable", self.meta, Column("id", Integer, primary_key=True), Column("name", String(50)), ) self.meta.create_all(self.engine) def teardown_test(self): self.meta.drop_all(self.engine) self.engine.dispose() def test_restart_on_execute_no_txn(self): engine = self.make_engine(self.engine) with engine.connect() as conn: eq_(conn.execute(select(1)).scalar(), 1) self.engine.test_shutdown() self.engine.test_restart() eq_(conn.execute(select(1)).scalar(), 1) def test_restart_on_execute_txn(self): engine = self.make_engine(self.engine) with engine.begin() as conn: eq_(conn.execute(select(1)).scalar(), 1) self.engine.test_shutdown() self.engine.test_restart() eq_(conn.execute(select(1)).scalar(), 1) def test_autocommits_txn(self): engine = self.make_engine(self.engine) with engine.begin() as conn: conn.execute( self.table.insert(), [ { "id": 1, "name": "some name 1" }, { "id": 2, "name": "some name 2" }, { "id": 3, "name": "some name 3" }, ], ) self.engine.test_shutdown() self.engine.test_restart() eq_( conn.execute(select(self.table).order_by( self.table.c.id)).fetchall(), [(1, "some name 1"), (2, "some name 2"), (3, "some name 3")], ) def test_fail_on_executemany_txn(self): engine = self.make_engine(self.engine) with engine.begin() as conn: conn.execute( self.table.insert(), [ { "id": 1, "name": "some name 1" }, { "id": 2, "name": "some name 2" }, { "id": 3, "name": "some name 3" }, ], ) self.engine.test_shutdown() self.engine.test_restart() assert_raises( exc.DBAPIError, conn.execute, self.table.insert(), [ { "id": 4, "name": "some name 4" }, { "id": 5, "name": "some name 5" }, { "id": 6, "name": "some name 6" }, ], ) if self.future: conn.rollback() else: trans = conn.get_transaction() trans.rollback()
class AlterColRoundTripFixture(object): # since these tests are about syntax, use more recent SQLAlchemy as some of # the type / server default compare logic might not work on older # SQLAlchemy versions as seems to be the case for SQLAlchemy 1.1 on Oracle __requires__ = ("alter_column", "sqlalchemy_12") def setUp(self): self.conn = config.db.connect() self.ctx = MigrationContext.configure(self.conn) self.op = Operations(self.ctx) self.metadata = MetaData() def _compare_type(self, t1, t2): c1 = Column("q", t1) c2 = Column("q", t2) assert not self.ctx.impl.compare_type( c1, c2 ), "Type objects %r and %r didn't compare as equivalent" % (t1, t2) def _compare_server_default(self, t1, s1, t2, s2): c1 = Column("q", t1, server_default=s1) c2 = Column("q", t2, server_default=s2) assert not self.ctx.impl.compare_server_default( c1, c2, s2, s1 ), "server defaults %r and %r didn't compare as equivalent" % (s1, s2) def tearDown(self): self.metadata.drop_all(self.conn) self.conn.close() def _run_alter_col(self, from_, to_, compare=None): column = Column( from_.get("name", "colname"), from_.get("type", String(10)), nullable=from_.get("nullable", True), server_default=from_.get("server_default", None), # comment=from_.get("comment", None) ) t = Table("x", self.metadata, column) t.create(self.conn) insp = inspect(self.conn) old_col = insp.get_columns("x")[0] # TODO: conditional comment support self.op.alter_column( "x", column.name, existing_type=column.type, existing_server_default=column.server_default if column.server_default is not None else False, existing_nullable=True if column.nullable else False, # existing_comment=column.comment, nullable=to_.get("nullable", None), # modify_comment=False, server_default=to_.get("server_default", False), new_column_name=to_.get("name", None), type_=to_.get("type", None), ) insp = inspect(self.conn) new_col = insp.get_columns("x")[0] if compare is None: compare = to_ eq_( new_col["name"], compare["name"] if "name" in compare else column.name, ) self._compare_type( new_col["type"], compare.get("type", old_col["type"]) ) eq_(new_col["nullable"], compare.get("nullable", column.nullable)) self._compare_server_default( new_col["type"], new_col.get("default", None), compare.get("type", old_col["type"]), compare["server_default"].text if "server_default" in compare else column.server_default.arg.text if column.server_default is not None else None, )
def test_orm_many_engines(self): metadata = MetaData() table1 = Table( "mytable", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", String(30)), ) table2 = Table( "mytable2", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", String(30)), Column("col3", Integer, ForeignKey("mytable.col1")), ) metadata.create_all(self.engine) m1 = self.mapper_registry.map_imperatively( A, table1, properties={ "bs": relationship(B, cascade="all, delete", order_by=table2.c.col1) }, _compiled_cache_size=50, ) m2 = self.mapper_registry.map_imperatively(B, table2, _compiled_cache_size=50) @profile_memory() def go(): engine = engines.testing_engine( options={ "logging_name": "FOO", "pool_logging_name": "BAR", "use_reaper": False, }) with Session(engine) as sess: a1 = A(col2="a1") a2 = A(col2="a2") a3 = A(col2="a3") a1.bs.append(B(col2="b1")) a1.bs.append(B(col2="b2")) a3.bs.append(B(col2="b3")) for x in [a1, a2, a3]: sess.add(x) sess.commit() alist = sess.query(A).order_by(A.col1).all() eq_( [ A(col2="a1", bs=[B(col2="b1"), B(col2="b2")]), A(col2="a2", bs=[]), A(col2="a3", bs=[B(col2="b3")]), ], alist, ) for a in alist: sess.delete(a) sess.commit() engine.dispose() go() metadata.drop_all(self.engine) del m1, m2 assert_no_mappers()
class ApdbSchema(object): """Class for management of APDB schema. Attributes ---------- objects : `sqlalchemy.Table` DiaObject table instance objects_nightly : `sqlalchemy.Table` DiaObjectNightly table instance, may be None objects_last : `sqlalchemy.Table` DiaObjectLast table instance, may be None sources : `sqlalchemy.Table` DiaSource table instance forcedSources : `sqlalchemy.Table` DiaForcedSource table instance visits : `sqlalchemy.Table` ApdbProtoVisits table instance Parameters ---------- engine : `sqlalchemy.engine.Engine` SQLAlchemy engine instance dia_object_index : `str` Indexing mode for DiaObject table, see `ApdbConfig.dia_object_index` for details. dia_object_nightly : `bool` If `True` then create per-night DiaObject table as well. schema_file : `str` Name of the YAML schema file. extra_schema_file : `str`, optional Name of the YAML schema file with extra column definitions. column_map : `str`, optional Name of the YAML file with column mappings. afw_schemas : `dict`, optional Dictionary with table name for a key and `afw.table.Schema` for a value. Columns in schema will be added to standard APDB schema (only if standard schema does not have matching column). prefix : `str`, optional Prefix to add to all scheam elements. """ # map afw type names into cat type names _afw_type_map = {"I": "INT", "L": "BIGINT", "F": "FLOAT", "D": "DOUBLE", "Angle": "DOUBLE", "String": "CHAR", "Flag": "BOOL"} _afw_type_map_reverse = {"INT": "I", "BIGINT": "L", "FLOAT": "F", "DOUBLE": "D", "DATETIME": "L", "CHAR": "String", "BOOL": "Flag"} def __init__(self, engine, dia_object_index, dia_object_nightly, schema_file, extra_schema_file=None, column_map=None, afw_schemas=None, prefix=""): self._engine = engine self._dia_object_index = dia_object_index self._dia_object_nightly = dia_object_nightly self._prefix = prefix self._metadata = MetaData(self._engine) self.objects = None self.objects_nightly = None self.objects_last = None self.sources = None self.forcedSources = None self.visits = None if column_map: _LOG.debug("Reading column map file %s", column_map) with open(column_map) as yaml_stream: # maps cat column name to afw column name self._column_map = yaml.load(yaml_stream, Loader=yaml.SafeLoader) _LOG.debug("column map: %s", self._column_map) else: _LOG.debug("No column map file is given, initialize to empty") self._column_map = {} self._column_map_reverse = {} for table, cmap in self._column_map.items(): # maps afw column name to cat column name self._column_map_reverse[table] = {v: k for k, v in cmap.items()} _LOG.debug("reverse column map: %s", self._column_map_reverse) # build complete table schema self._schemas = self._buildSchemas(schema_file, extra_schema_file, afw_schemas) # map cat column types to alchemy self._type_map = dict(DOUBLE=self._getDoubleType(), FLOAT=sqlalchemy.types.Float, DATETIME=sqlalchemy.types.TIMESTAMP, BIGINT=sqlalchemy.types.BigInteger, INTEGER=sqlalchemy.types.Integer, INT=sqlalchemy.types.Integer, TINYINT=sqlalchemy.types.Integer, BLOB=sqlalchemy.types.LargeBinary, CHAR=sqlalchemy.types.CHAR, BOOL=sqlalchemy.types.Boolean) # generate schema for all tables, must be called last self._makeTables() def _makeTables(self, mysql_engine='InnoDB', oracle_tablespace=None, oracle_iot=False): """Generate schema for all tables. Parameters ---------- mysql_engine : `str`, optional MySQL engine type to use for new tables. oracle_tablespace : `str`, optional Name of Oracle tablespace, only useful with oracle oracle_iot : `bool`, optional Make Index-organized DiaObjectLast table. """ info = dict(oracle_tablespace=oracle_tablespace) if self._dia_object_index == 'pix_id_iov': # Special PK with HTM column in first position constraints = self._tableIndices('DiaObjectIndexHtmFirst', info) else: constraints = self._tableIndices('DiaObject', info) table = Table(self._prefix+'DiaObject', self._metadata, *(self._tableColumns('DiaObject') + constraints), mysql_engine=mysql_engine, info=info) self.objects = table if self._dia_object_nightly: # Same as DiaObject but no index table = Table(self._prefix+'DiaObjectNightly', self._metadata, *self._tableColumns('DiaObject'), mysql_engine=mysql_engine, info=info) self.objects_nightly = table if self._dia_object_index == 'last_object_table': # Same as DiaObject but with special index info2 = info.copy() info2.update(oracle_iot=oracle_iot) table = Table(self._prefix+'DiaObjectLast', self._metadata, *(self._tableColumns('DiaObjectLast') + self._tableIndices('DiaObjectLast', info)), mysql_engine=mysql_engine, info=info2) self.objects_last = table # for all other tables use index definitions in schema for table_name in ('DiaSource', 'SSObject', 'DiaForcedSource', 'DiaObject_To_Object_Match'): table = Table(self._prefix+table_name, self._metadata, *(self._tableColumns(table_name) + self._tableIndices(table_name, info)), mysql_engine=mysql_engine, info=info) if table_name == 'DiaSource': self.sources = table elif table_name == 'DiaForcedSource': self.forcedSources = table # special table to track visits, only used by prototype table = Table(self._prefix+'ApdbProtoVisits', self._metadata, Column('visitId', sqlalchemy.types.BigInteger, nullable=False), Column('visitTime', sqlalchemy.types.TIMESTAMP, nullable=False), PrimaryKeyConstraint('visitId', name=self._prefix+'PK_ApdbProtoVisits'), Index(self._prefix+'IDX_ApdbProtoVisits_vTime', 'visitTime', info=info), mysql_engine=mysql_engine, info=info) self.visits = table def makeSchema(self, drop=False, mysql_engine='InnoDB', oracle_tablespace=None, oracle_iot=False): """Create or re-create all tables. Parameters ---------- drop : `bool`, optional If True then drop tables before creating new ones. mysql_engine : `str`, optional MySQL engine type to use for new tables. oracle_tablespace : `str`, optional Name of Oracle tablespace, only useful with oracle oracle_iot : `bool`, optional Make Index-organized DiaObjectLast table. """ # re-make table schema for all needed tables with possibly different options _LOG.debug("clear metadata") self._metadata.clear() _LOG.debug("re-do schema mysql_engine=%r oracle_tablespace=%r", mysql_engine, oracle_tablespace) self._makeTables(mysql_engine=mysql_engine, oracle_tablespace=oracle_tablespace, oracle_iot=oracle_iot) # create all tables (optionally drop first) if drop: _LOG.info('dropping all tables') self._metadata.drop_all() _LOG.info('creating all tables') self._metadata.create_all() def getAfwSchema(self, table_name, columns=None): """Return afw schema for given table. Parameters ---------- table_name : `str` One of known APDB table names. columns : `list` of `str`, optional Include only given table columns in schema, by default all columns are included. Returns ------- schema : `lsst.afw.table.Schema` column_map : `dict` Mapping of the table/result column names into schema key. """ table = self._schemas[table_name] col_map = self._column_map.get(table_name, {}) # make a schema col2afw = {} schema = afwTable.SourceTable.makeMinimalSchema() for column in table.columns: if columns and column.name not in columns: continue afw_col = col_map.get(column.name, column.name) if afw_col in schema.getNames(): # Continue if the column is already in the minimal schema. key = schema.find(afw_col).getKey() elif column.type in ("DOUBLE", "FLOAT") and column.unit == "deg": # # NOTE: degree to radian conversion is not supported (yet) # # angles in afw are radians and have special "Angle" type key = schema.addField(afw_col, type="Angle", doc=column.description or "", units="rad") elif column.type == "BLOB": # No BLOB support for now key = None else: units = column.unit or "" # some units in schema are not recognized by afw but we do not care if self._afw_type_map_reverse[column.type] == 'String': key = schema.addField(afw_col, type=self._afw_type_map_reverse[column.type], doc=column.description or "", units=units, parse_strict="silent", size=10) elif units == "deg": key = schema.addField(afw_col, type='Angle', doc=column.description or "", parse_strict="silent") else: key = schema.addField(afw_col, type=self._afw_type_map_reverse[column.type], doc=column.description or "", units=units, parse_strict="silent") col2afw[column.name] = key return schema, col2afw def getAfwColumns(self, table_name): """Returns mapping of afw column names to Column definitions. Parameters ---------- table_name : `str` One of known APDB table names. Returns ------- column_map : `dict` Mapping of afw column names to `ColumnDef` instances. """ table = self._schemas[table_name] col_map = self._column_map.get(table_name, {}) cmap = {} for column in table.columns: afw_name = col_map.get(column.name, column.name) cmap[afw_name] = column return cmap def getColumnMap(self, table_name): """Returns mapping of column names to Column definitions. Parameters ---------- table_name : `str` One of known APDB table names. Returns ------- column_map : `dict` Mapping of column names to `ColumnDef` instances. """ table = self._schemas[table_name] cmap = {column.name: column for column in table.columns} return cmap def _buildSchemas(self, schema_file, extra_schema_file=None, afw_schemas=None): """Create schema definitions for all tables. Reads YAML schemas and builds dictionary containing `TableDef` instances for each table. Parameters ---------- schema_file : `str` Name of YAML file with standard cat schema. extra_schema_file : `str`, optional Name of YAML file with extra table information or `None`. afw_schemas : `dict`, optional Dictionary with table name for a key and `afw.table.Schema` for a value. Columns in schema will be added to standard APDB schema (only if standard schema does not have matching column). Returns ------- schemas : `dict` Mapping of table names to `TableDef` instances. """ _LOG.debug("Reading schema file %s", schema_file) with open(schema_file) as yaml_stream: tables = list(yaml.load_all(yaml_stream, Loader=yaml.SafeLoader)) # index it by table name _LOG.debug("Read %d tables from schema", len(tables)) if extra_schema_file: _LOG.debug("Reading extra schema file %s", extra_schema_file) with open(extra_schema_file) as yaml_stream: extras = list(yaml.load_all(yaml_stream, Loader=yaml.SafeLoader)) # index it by table name schemas_extra = {table['table']: table for table in extras} else: schemas_extra = {} # merge extra schema into a regular schema, for now only columns are merged for table in tables: table_name = table['table'] if table_name in schemas_extra: columns = table['columns'] extra_columns = schemas_extra[table_name].get('columns', []) extra_columns = {col['name']: col for col in extra_columns} _LOG.debug("Extra columns for table %s: %s", table_name, extra_columns.keys()) columns = [] for col in table['columns']: if col['name'] in extra_columns: columns.append(extra_columns.pop(col['name'])) else: columns.append(col) # add all remaining extra columns table['columns'] = columns + list(extra_columns.values()) if 'indices' in schemas_extra[table_name]: raise RuntimeError("Extra table definition contains indices, " "merging is not implemented") del schemas_extra[table_name] # Pure "extra" table definitions may contain indices tables += schemas_extra.values() # convert all dicts into named tuples schemas = {} for table in tables: columns = table.get('columns', []) table_name = table['table'] afw_schema = afw_schemas and afw_schemas.get(table_name) if afw_schema: # use afw schema to create extra columns column_names = {col['name'] for col in columns} column_names_lower = {col.lower() for col in column_names} for _, field in afw_schema: column = self._field2dict(field, table_name) if column['name'] not in column_names: # check that there is no column name that only differs in case if column['name'].lower() in column_names_lower: raise ValueError("afw.table column name case does not match schema column name") columns.append(column) table_columns = [] for col in columns: # For prototype set default to 0 even if columns don't specify it if "default" not in col: default = None if col['type'] not in ("BLOB", "DATETIME"): default = 0 else: default = col["default"] column = ColumnDef(name=col['name'], type=col['type'], nullable=col.get("nullable"), default=default, description=col.get("description"), unit=col.get("unit"), ucd=col.get("ucd")) table_columns.append(column) table_indices = [] for idx in table.get('indices', []): index = IndexDef(name=idx.get('name'), type=idx.get('type'), columns=idx.get('columns')) table_indices.append(index) schemas[table_name] = TableDef(name=table_name, description=table.get('description'), columns=table_columns, indices=table_indices) return schemas def _tableColumns(self, table_name): """Return set of columns in a table Parameters ---------- table_name : `str` Name of the table. Returns ------- column_defs : `list` List of `Column` objects. """ # get the list of columns in primary key, they are treated somewhat # specially below table_schema = self._schemas[table_name] pkey_columns = set() for index in table_schema.indices: if index.type == 'PRIMARY': pkey_columns = set(index.columns) break # convert all column dicts into alchemy Columns column_defs = [] for column in table_schema.columns: kwargs = dict(nullable=column.nullable) if column.default is not None: kwargs.update(server_default=str(column.default)) if column.name in pkey_columns: kwargs.update(autoincrement=False) ctype = self._type_map[column.type] column_defs.append(Column(column.name, ctype, **kwargs)) return column_defs def _field2dict(self, field, table_name): """Convert afw schema field definition into a dict format. Parameters ---------- field : `lsst.afw.table.Field` Field in afw table schema. table_name : `str` Name of the table. Returns ------- field_dict : `dict` Field attributes for SQL schema: - ``name`` : field name (`str`) - ``type`` : type name in SQL, e.g. "INT", "FLOAT" (`str`) - ``nullable`` : `True` if column can be ``NULL`` (`bool`) """ column = field.getName() column = self._column_map_reverse[table_name].get(column, column) ctype = self._afw_type_map[field.getTypeString()] return dict(name=column, type=ctype, nullable=True) def _tableIndices(self, table_name, info): """Return set of constraints/indices in a table Parameters ---------- table_name : `str` Name of the table. info : `dict` Additional options passed to SQLAlchemy index constructor. Returns ------- index_defs : `list` List of SQLAlchemy index/constraint objects. """ table_schema = self._schemas[table_name] # convert all index dicts into alchemy Columns index_defs = [] for index in table_schema.indices: if index.type == "INDEX": index_defs.append(Index(self._prefix+index.name, *index.columns, info=info)) else: kwargs = {} if index.name: kwargs['name'] = self._prefix+index.name if index.type == "PRIMARY": index_defs.append(PrimaryKeyConstraint(*index.columns, **kwargs)) elif index.type == "UNIQUE": index_defs.append(UniqueConstraint(*index.columns, **kwargs)) return index_defs def _getDoubleType(self): """DOUBLE type is database-specific, select one based on dialect. Returns ------- type_object : `object` Database-specific type definition. """ if self._engine.name == 'mysql': from sqlalchemy.dialects.mysql import DOUBLE return DOUBLE(asdecimal=False) elif self._engine.name == 'postgresql': from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION return DOUBLE_PRECISION elif self._engine.name == 'oracle': from sqlalchemy.dialects.oracle import DOUBLE_PRECISION return DOUBLE_PRECISION elif self._engine.name == 'sqlite': # all floats in sqlite are 8-byte from sqlalchemy.dialects.sqlite import REAL return REAL else: raise TypeError('cannot determine DOUBLE type, unexpected dialect: ' + self._engine.name)
def test_alias_pathing(self): metadata = MetaData() a = Table( "a", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("bid", Integer, ForeignKey("b.id")), Column("type", String(30)), ) asub = Table( "asub", metadata, Column("id", Integer, ForeignKey("a.id"), primary_key=True), Column("data", String(30)), ) b = Table( "b", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), ) self.mapper_registry.map_imperatively(A, a, polymorphic_identity="a", polymorphic_on=a.c.type) self.mapper_registry.map_imperatively(ASub, asub, inherits=A, polymorphic_identity="asub") self.mapper_registry.map_imperatively( B, b, properties={"as_": relationship(A)}) metadata.create_all(self.engine) sess = Session(self.engine) a1 = ASub(data="a1") a2 = ASub(data="a2") a3 = ASub(data="a3") b1 = B(as_=[a1, a2, a3]) sess.add(b1) sess.commit() del sess # sqlite has a slow enough growth here # that we have to run it more times to see the # "dip" again @profile_memory(maxtimes=120) def go(): sess = Session(self.engine) sess.query(B).options(subqueryload(B.as_.of_type(ASub))).all() sess.close() del sess try: go() finally: metadata.drop_all(self.engine) clear_mappers()
Column('id', Integer, primary_key=True, default=id_generator), Column('continent', String(30), nullable=False), Column('city', String(50), nullable=False)) weather_reports = Table( "weather_reports", meta, Column('id', Integer, primary_key=True), Column('location_id', Integer, ForeignKey('weather_locations.id')), Column('temperature', Float), Column('report_time', DateTime, default=datetime.datetime.now), ) # create tables for db in (db1, db2, db3, db4): meta.drop_all(db) meta.create_all(db) # establish initial "id" in db1 db1.execute(ids.insert(), nextid=1) # step 5. define sharding functions. # we'll use a straight mapping of a particular set of "country" # attributes to shard id. shard_lookup = { 'North America': 'north_america', 'Asia': 'asia', 'Europe': 'europe', 'South America': 'south_america' }
def test_with_manytomany(self): metadata = MetaData() table1 = Table( "mytable", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", String(30)), ) table2 = Table( "mytable2", metadata, Column( "col1", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("col2", String(30)), ) table3 = Table( "t1tot2", metadata, Column("t1", Integer, ForeignKey("mytable.col1")), Column("t2", Integer, ForeignKey("mytable2.col1")), ) @profile_memory() def go(): class A(fixtures.ComparableEntity): pass class B(fixtures.ComparableEntity): pass self.mapper_registry.map_imperatively( A, table1, properties={ "bs": relationship(B, secondary=table3, backref="as", order_by=table3.c.t1) }, ) self.mapper_registry.map_imperatively(B, table2) sess = Session(self.engine, autoflush=False) a1 = A(col2="a1") a2 = A(col2="a2") b1 = B(col2="b1") b2 = B(col2="b2") a1.bs.append(b1) a2.bs.append(b2) for x in [a1, a2]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_([A(bs=[B(col2="b1")]), A(bs=[B(col2="b2")])], alist) for a in alist: sess.delete(a) sess.flush() # mappers necessarily find themselves in the compiled cache, # so to allow them to be GC'ed clear out the cache self.engine.clear_compiled_cache() del B del A metadata.create_all(self.engine) try: go() finally: metadata.drop_all(self.engine) assert_no_mappers()
def drop_tables(): meta = MetaData() meta.drop_all(bind=engine, tables=[task])
def test_update(self): """ Tests sending functions and SQL expressions to the VALUES and SET clauses of INSERT/UPDATE instances, and that column-level defaults get overridden. """ meta = MetaData(testing.db) t = Table('t1', meta, Column('id', Integer, Sequence('t1idseq', optional=True), primary_key=True), Column('value', Integer) ) t2 = Table('t2', meta, Column('id', Integer, Sequence('t2idseq', optional=True), primary_key=True), Column('value', Integer, default=7), Column('stuff', String(20), onupdate="thisisstuff") ) meta.create_all() try: t.insert(values=dict(value=func.length("one"))).execute() assert t.select().execute().first()['value'] == 3 t.update(values=dict(value=func.length("asfda"))).execute() assert t.select().execute().first()['value'] == 5 r = t.insert(values=dict(value=func.length("sfsaafsda"))).execute() id = r.inserted_primary_key[0] assert t.select(t.c.id == id).execute().first()['value'] == 9 t.update(values={t.c.value: func.length("asdf")}).execute() assert t.select().execute().first()['value'] == 4 t2.insert().execute() t2.insert(values=dict(value=func.length("one"))).execute() t2.insert(values=dict(value=func.length("asfda") + -19)).\ execute(stuff="hi") res = exec_sorted(select([t2.c.value, t2.c.stuff])) eq_(res, [(-14, 'hi'), (3, None), (7, None)]) t2.update(values=dict(value=func.length("asdsafasd"))).\ execute(stuff="some stuff") assert select([t2.c.value, t2.c.stuff]).execute().fetchall() == \ [(9, "some stuff"), (9, "some stuff"), (9, "some stuff")] t2.delete().execute() t2.insert(values=dict(value=func.length("one") + 8)).execute() assert t2.select().execute().first()['value'] == 11 t2.update(values=dict(value=func.length("asfda"))).execute() eq_( select([t2.c.value, t2.c.stuff]).execute().first(), (5, "thisisstuff") ) t2.update(values={t2.c.value: func.length("asfdaasdf"), t2.c.stuff: "foo"}).execute() print("HI", select([t2.c.value, t2.c.stuff]).execute().first()) eq_(select([t2.c.value, t2.c.stuff]).execute().first(), (9, "foo") ) finally: meta.drop_all()
def drop_tables(target_config=None): engine = get_engine(target_config) meta = MetaData() meta.drop_all(bind=engine, tables=[users, flags, orders, transactions])
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine profile = Table('profile', meta, Column('id', String(length=36), primary_key=True, nullable=False), Column('name', String(length=255)), Column('type', String(length=255)), Column('context', types.Dict), Column('spec', types.Dict), Column('user', String(length=32), nullable=False), Column('project', String(length=32), nullable=False), Column('domain', String(length=32)), Column('permission', String(length=32)), Column('meta_data', types.Dict), Column('created_at', DateTime), Column('updated_at', DateTime), mysql_engine='InnoDB', mysql_charset='utf8') cluster = Table('cluster', meta, Column('id', String(36), primary_key=True, nullable=False), Column('name', String(255), nullable=False), Column('profile_id', String(36), ForeignKey('profile.id'), nullable=False), Column('user', String(32), nullable=False), Column('project', String(32), nullable=False), Column('domain', String(32)), Column('parent', String(36)), Column('init_at', DateTime), Column('created_at', DateTime), Column('updated_at', DateTime), Column('min_size', Integer), Column('max_size', Integer), Column('desired_capacity', Integer), Column('next_index', Integer), Column('timeout', Integer), Column('status', String(255)), Column('status_reason', Text), Column('meta_data', types.Dict), Column('data', types.Dict), mysql_engine='InnoDB', mysql_charset='utf8') node = Table('node', meta, Column('id', String(36), primary_key=True, nullable=False), Column('name', String(255)), Column('physical_id', String(36)), Column('cluster_id', String(36)), Column('profile_id', String(36), ForeignKey('profile.id'), nullable=False), Column('user', String(32)), Column('project', String(32)), Column('domain', String(32)), Column('index', Integer), Column('role', String(64)), Column('init_at', DateTime), Column('created_at', DateTime), Column('updated_at', DateTime), Column('status', String(255)), Column('status_reason', Text), Column('meta_data', types.Dict), Column('data', types.Dict), mysql_engine='InnoDB', mysql_charset='utf8') cluster_lock = Table('cluster_lock', meta, Column('cluster_id', String(36), primary_key=True, nullable=False), Column('action_ids', types.List), Column('semaphore', Integer), mysql_engine='InnoDB', mysql_charset='utf8') node_lock = Table('node_lock', meta, Column('node_id', String(36), primary_key=True, nullable=False), Column('action_id', String(36)), mysql_engine='InnoDB', mysql_charset='utf8') policy = Table('policy', meta, Column('id', String(36), primary_key=True, nullable=False), Column('name', String(255)), Column('type', String(255)), Column('user', String(32), nullable=False), Column('project', String(32), nullable=False), Column('domain', String(32)), Column('cooldown', Integer), Column('level', Integer), Column('created_at', DateTime), Column('updated_at', DateTime), Column('spec', types.Dict), Column('data', types.Dict), mysql_engine='InnoDB', mysql_charset='utf8') cluster_policy = Table('cluster_policy', meta, Column('id', String(36), primary_key=True, nullable=False), Column('cluster_id', String(36), ForeignKey('cluster.id'), nullable=False), Column('policy_id', String(36), ForeignKey('policy.id'), nullable=False), Column('cooldown', Integer), Column('priority', Integer), Column('level', Integer), Column('enabled', Boolean), Column('data', types.Dict), Column('last_op', DateTime), mysql_engine='InnoDB', mysql_charset='utf8') receiver = Table('receiver', meta, Column('id', String(36), primary_key=True, nullable=False), Column('name', String(255)), Column('type', String(255)), Column('user', String(32)), Column('project', String(32)), Column('domain', String(32)), Column('created_at', DateTime), Column('updated_at', DateTime), Column('cluster_id', String(36)), Column('actor', types.Dict), Column('action', Text), Column('params', types.Dict), Column('channel', types.Dict), mysql_engine='InnoDB', mysql_charset='utf8') credential = Table('credential', meta, Column('user', String(32), primary_key=True, nullable=False), Column('project', String(32), primary_key=True, nullable=False), Column('cred', types.Dict, nullable=False), Column('data', types.Dict), mysql_engine='InnoDB', mysql_charset='utf8') action = Table( 'action', meta, Column('id', String(36), primary_key=True, nullable=False), Column('name', String(63)), Column('context', types.Dict), Column('target', String(36)), Column('action', Text), Column('cause', String(255)), Column('owner', String(36)), Column('interval', Integer), # FIXME: Don't specify fixed precision. Column('start_time', Float(precision='24,8')), Column('end_time', Float(precision='24,8')), Column('timeout', Integer), Column('control', String(255)), Column('status', String(255)), Column('status_reason', Text), Column('inputs', types.Dict), Column('outputs', types.Dict), Column('depends_on', types.List), Column('depended_by', types.List), Column('created_at', DateTime), Column('updated_at', DateTime), Column('data', types.Dict), mysql_engine='InnoDB', mysql_charset='utf8') dependency = Table('dependency', meta, Column('id', String(36), nullable=False, primary_key=True), Column('depended', String(36), ForeignKey('action.id'), nullable=False), Column('dependent', String(36), ForeignKey('action.id'), nullable=False), mysql_engine='InnoDB', mysql_charset='utf8') event = Table('event', meta, Column('id', String(36), primary_key=True, nullable=False), Column('timestamp', DateTime, nullable=False), Column('obj_id', String(36)), Column('obj_name', String(255)), Column('obj_type', String(36)), Column('cluster_id', String(36)), Column('level', String(63)), Column('user', String(32)), Column('project', String(32)), Column('action', String(36)), Column('status', String(255)), Column('status_reason', Text), Column('meta_data', types.Dict), mysql_engine='InnoDB', mysql_charset='utf8') tables = ( profile, cluster, node, cluster_lock, node_lock, policy, cluster_policy, credential, action, dependency, receiver, event, ) for index, table in enumerate(tables): try: table.create() except Exception: # If an error occurs, drop all tables created so far to return # to the previously existing state. meta.drop_all(tables=tables[:index]) raise
def drop_tables(eng): meta = MetaData() meta.drop_all(bind=eng, tables=[chat_user, chat, message])
def test_orm_many_engines(self): metadata = MetaData(self.engine) table1 = Table( "mytable", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30))) table2 = Table( "mytable2", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30)), Column('col3', Integer, ForeignKey("mytable.col1"))) metadata.create_all() m1 = mapper(A, table1, properties={ "bs": relationship(B, cascade="all, delete", order_by=table2.c.col1) }, _compiled_cache_size=50) m2 = mapper(B, table2, _compiled_cache_size=50) m3 = mapper(A, table1, non_primary=True) @profile_memory() def go(): engine = engines.testing_engine( options={ 'logging_name': 'FOO', 'pool_logging_name': 'BAR', 'use_reaper': False }) sess = create_session(bind=engine) a1 = A(col2="a1") a2 = A(col2="a2") a3 = A(col2="a3") a1.bs.append(B(col2="b1")) a1.bs.append(B(col2="b2")) a3.bs.append(B(col2="b3")) for x in [a1, a2, a3]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_([ A(col2="a1", bs=[B(col2="b1"), B(col2="b2")]), A(col2="a2", bs=[]), A(col2="a3", bs=[B(col2="b3")]) ], alist) for a in alist: sess.delete(a) sess.flush() sess.close() engine.dispose() go() metadata.drop_all() del m1, m2, m3 assert_no_mappers()
print(engine) meta = MetaData() TEST = Table( 'TESTSELECT', meta, Column('id', BIGINT), #Column('id', BIGINT, primary_key = True), # Column('name', VARCHAR(20) ), #Column('name', nz.NVARCHAR(20) ), Column('name', nz.NVARCHAR(2)), #Column('name', nz.NCHAR(20) ), Column('gender', CHAR), #Column('gender', nz.NCHAR), ) meta.drop_all(engine) meta.create_all(engine) conn = engine.connect() conn.execute(TEST.insert().values(id='3', name='j', gender='M')) conn.execute(TEST.insert().values(id='3', name='j', gender='M')) #meta.drop_all(engine) col = Column('name') col2 = Column('gender') #s = select([TEST])#.where(col=='jack1') #result = conn.execute(s) #for row in result: # print (row) #s = select([TEST]).where(col=='jack1').order_by(desc(col2)).limit(10)