def test_create_table_nested_types(self): table = Table('t1', self.metadata(), Column('x', types.Int32, primary_key=True), Column('y', types.Array(types.String)), engines.Memory()) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE t1 ' '(x Int32, y Array(String)) ' 'ENGINE = Memory') table = Table('t1', self.metadata(), Column('x', types.Int32, primary_key=True), Column('y', types.Array(types.Array(types.String))), engines.Memory()) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE t1 ' '(x Int32, y Array(Array(String))) ' 'ENGINE = Memory') table = Table('t1', self.metadata(), Column('x', types.Int32, primary_key=True), Column('y', types.Array(types.Array(types.String))), engines.Memory()) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE t1 ' '(x Int32, y Array(Array(String))) ' 'ENGINE = Memory')
async def create_table(conn): await conn.execute('DROP TABLE IF EXISTS cars') await conn.execute('DROP TABLE IF EXISTS users') await conn.execute('DROP TYPE IF EXISTS s_enum CASCADE') await conn.execute("CREATE TYPE s_enum AS ENUM ('f', 's')") await conn.execute(CreateTable(users)) await conn.execute(CreateTable(cars))
async def db_prepair(engine): async with engine.acquire() as conn: print("Recreate tables in db") await conn.execute('DROP TABLE IF EXISTS reports') await conn.execute('DROP TABLE IF EXISTS devices') await conn.execute(CreateTable(devices)) await conn.execute(CreateTable(reports))
async def create_tables(conn): await conn.execute('DROP TABLE IF EXISTS orders') await conn.execute('DROP TABLE IF EXISTS customers') await conn.execute('DROP TABLE IF EXISTS products') await conn.execute(CreateTable(customers)) await conn.execute(CreateTable(products)) await conn.execute(CreateTable(orders))
async def _execute_pyrogram_ddl(self, conn): """ Create initial structures """ await conn.execute(str(CreateTable(self._t_session))) await conn.execute(str(CreateTable(self._t_peers))) await conn.execute(str(CreateIndex(self._i_peers_username))) await conn.execute(str(CreateIndex(self._i_peers_phone_number))) await conn.execute( insert(self._t_session).values([2, None, None, 0, None, None]))
async def db_create_tables(dsn): async with create_engine(dsn) as engine: async with engine.acquire() as conn: await conn.execute( "DROP TABLE IF EXISTS stores, shelves, shelf_items CASCADE" ) await conn.execute(CreateTable(stores_table)) await conn.execute(CreateTable(shelves_table)) await conn.execute(CreateTable(shelf_items_table))
def create_tables(app: web.Application) -> None: cfg = app['config']['postgres'] engine = sa.create_engine(f'postgresql://{cfg["user"]}:{cfg["password"]}@' f'{cfg["host"]}:{cfg["port"]}/{cfg["database"]}') try: engine.execute(CreateTable(section)) engine.execute(CreateTable(post)) engine.execute(CreateTable(comment)) except Exception as e: print(e)
def _rename_and_delete_columns(engine, table_name, column_renames: t.List[t.Tuple[str, str]], column_deletes: t.List[str]): temp_table_name = table_name + '_temp' table = db.Model.metadata.tables[table_name] tmp_ddl = CreateTable(table).compile(engine).string.replace( table_name, temp_table_name) map2old = {t[1]: t[0] for t in column_renames} new_column_table = [ k for k in table.columns.keys() if k not in column_deletes ] old_column_table = [map2old.get(item, item) for item in new_column_table] with engine.connect() as connection: old_c_s = [f'"{c}"' for c in old_column_table] new_c_s = [f'"{c}"' for c in new_column_table] connection.execute(tmp_ddl) try: connection.execute( f'INSERT INTO {temp_table_name}({", ".join(new_c_s)}) ' f'SELECT {", ".join(old_c_s)} FROM {table_name}') except: connection.execute(f"DROP TABLE {temp_table_name}") raise else: connection.execute(f"DROP TABLE {table_name}") connection.execute( f"ALTER TABLE {temp_table_name} RENAME TO {table_name}")
def _delete_columns(engine, table_name, column_deletes: t.List[str]): temp_table_name = table_name + '_temp' table = db.Model.metadata.tables[table_name] tmp_ddl = CreateTable(table).compile(engine).string.replace( table_name, temp_table_name) new_column_table = [ k for k in table.columns.keys() if k not in column_deletes ] with engine.connect() as connection: new_c_s = [f'"{c}"' for c in new_column_table] connection.execute(tmp_ddl) try: connection.execute( f'INSERT INTO {temp_table_name}({", ".join(new_c_s)}) ' f'SELECT {", ".join(new_c_s)} FROM {table_name}') except: connection.execute(f"DROP TABLE {temp_table_name}") raise else: connection.execute(f"DROP TABLE {table_name}") connection.execute( f"ALTER TABLE {temp_table_name} RENAME TO {table_name}")
def renderCreateTable(self, table, createIndexes=True): ''' Render the CREATE TABLE statement for the given table. Arguments: table (sqlalchemy.sql.schema.Table:): The SQLAlchemy Table object to render createIndexes (bool): Whether or not it should create table indexes Returns: string: The compiled CREATE TABLE statement ''' ddl = [] separator = '' if self.minified else '\n' if not self.minified: ddl.append('--\n-- Structure for table {}\n--\n'.format( table.name)) ddl.append(str(CreateTable(table).compile(dialect=self._dialect))) if len(table._data): ddl.append(separator + self.renderInserts(table)) if len(table.foreign_keys) and self._dialect.supports_alter: ddl.append(separator + self.renderTableConstraints(table)) if createIndexes and len(table.indexes): ddl.append(separator + self.renderTableIndexes(table)) return separator.join(ddl)
async def prepare_tables(pg): tables = Base.metadata.tables.values() await delete_tables(pg, tables) async with pg.acquire() as conn: for table in tables: create_expr = CreateTable(table) await conn.execute(create_expr)
def test_replicated(self): class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) y = Column(types.String) sign = Column(types.Int8) version = Column(types.Int8) __table_args__ = (engines.ReplicatedVersionedCollapsingMergeTree( '/table/path', 'name', sign, version, partition_by=date, order_by=(date, x), primary_key=(x, y)), ) self.assertEqual( self.compile(CreateTable(TestTable.__table__)), "CREATE TABLE test_table " "(date Date, x Int32, y String, sign Int8, version Int8) " "ENGINE = ReplicatedVersionedCollapsingMergeTree" "('/table/path', 'name', sign, version) " "PARTITION BY date " "ORDER BY (date, x) " "PRIMARY KEY (x, y)")
def test_create_table_with_codec(self): table = Table( 't1', self.metadata(), Column( 'list', types.DateTime, clickhouse_codec=['DoubleDelta', 'ZSTD'], ), Column( 'tuple', types.UInt8, clickhouse_codec=('T64', 'ZSTD(5)'), ), Column('explicit_none', types.UInt32, clickhouse_codec=None), Column('str', types.Int8, clickhouse_codec='ZSTD'), engines.Memory() ) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE t1 (' 'list DateTime CODEC(DoubleDelta, ZSTD), ' 'tuple UInt8 CODEC(T64, ZSTD(5)), ' 'explicit_none UInt32, ' 'str Int8 CODEC(ZSTD)) ' 'ENGINE = Memory' )
def test_basic(self): class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) y = Column(types.String) sign = Column(types.Int8) version = Column(types.Int8) __table_args__ = (engines.VersionedCollapsingMergeTree( sign, version, partition_by=date, order_by=(date, x), primary_key=(x, y), sample_by=func.random(), key='value'), ) self.assertEqual( self.compile(CreateTable(TestTable.__table__)), 'CREATE TABLE test_table ' '(date Date, x Int32, y String, sign Int8, version Int8) ' 'ENGINE = VersionedCollapsingMergeTree(sign, version) ' 'PARTITION BY date ' 'ORDER BY (date, x) ' 'PRIMARY KEY (x, y) ' 'SAMPLE BY random() ' 'SETTINGS key=value')
def test_replicated(self): class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) y = Column(types.String) version = Column(types.Int32) __table_args__ = ( engines.ReplicatedReplacingMergeTree( '/table/path', 'name', version='version', partition_by='date', order_by=('date', 'x') ), ) self.assertEqual( self.compile(CreateTable(TestTable.__table__)), "CREATE TABLE test_table " "(date Date, x Int32, y String, version Int32) " "ENGINE = ReplicatedReplacingMergeTree(" "'/table/path', 'name', version) " "PARTITION BY date " "ORDER BY (date, x)" )
def test_create_table_decimal_symlink(self): table = Table('test', TypesTestCase.metadata(), Column('x', types.Decimal(10, 2)), engines.Memory()) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE test (x Decimal(10, 2)) ENGINE = Memory')
def test_all_settings(self): class TestTable(self.base): date = Column(types.Date, primary_key=True) x = Column(types.Int32) y = Column(types.String) __table_args__ = ( engines.MergeTree( partition_by=date, order_by=(date, x), primary_key=(x, y), sample_by=func.hashFunc(x), setting1=2, setting2=5 ), ) self.assertEqual( self.compile(CreateTable(TestTable.__table__)), 'CREATE TABLE test_table ' '(date Date, x Int32, y String) ' 'ENGINE = MergeTree() ' 'PARTITION BY date ' 'ORDER BY (date, x) ' 'PRIMARY KEY (x, y) ' 'SAMPLE BY hashFunc(x) ' 'SETTINGS setting1=2, setting2=5' )
async def check_and_create_table(conn, table, table_name): """Check if table exist and creates it if none""" sql = f"SELECT to_regclass('public.{table_name}')" check_sql = await conn.execute(sql) check = await check_sql.fetchone() if check[0] is None: logging.info(f"Creating table {table_name}") await conn.execute(CreateTable(table))
def start(): engine = yield from make_engine() with (yield from engine) as conn: yield from conn.execute('DROP TABLE IF EXISTS sa_tbl4') sql = CreateTable(tbl) yield from conn.execute(sql) engine.release(conn) return engine
def test_compile_create_table_ddl(): create_statement = CreateTable(ddl_test_table) result, params = connection.compile_query(create_statement) assert result == ( '\nCREATE TABLE ddl_test_table (\n\tint_col' ' INTEGER, \n\tstr_col VARCHAR\n)\n\n' ) assert len(params) == 0
async def initialize_database(connection, stage2_only=False, stage1_only=False): if not stage2_only: await connection.execute( "CREATE TYPE extension_type AS ENUM('SIMPLE', 'MULTIRING', 'GROUP', 'EXTERNAL', " "'TRUNK')") await connection.execute( "CREATE TYPE forwarding_mode AS ENUM('DISABLED', 'ENABLED', 'ON_BUSY', " "'ON_UNAVAILABLE')") await connection.execute( "CREATE TYPE fork_rank_mode AS ENUM('DEFAULT', 'NEXT', 'DROP')") await connection.execute( "CREATE TYPE fork_rankmember_type AS ENUM('DEFAULT', 'AUXILIARY', 'PERSISTENT')" ) if not stage1_only: await connection.execute( "CREATE TYPE dect_displaymode AS ENUM('NUMBER', 'NUMBER_AND_NAME', 'NAME')" ) if not stage2_only: await connection.execute(CreateTable(Yate.table)) await connection.execute(CreateTable(Extension.table)) await connection.execute(CreateTable(ForkRank.table)) await connection.execute(CreateTable(ForkRank.member_table)) if not stage1_only: await connection.execute(CreateTable(User.table)) await connection.execute(CreateTable(Registration.table)) await connection.execute(CreateTable(ActiveCall.table))
def test_create_table_with_column_comment(self): table = Table( 't1', self.metadata(session=self.session), Column('x', types.Int32, primary_key=True, comment='col_comment'), engines.Memory()) self.assertEqual( self.compile(CreateTable(table)), "CREATE TABLE t1 (x Int32 COMMENT 'col_comment') ENGINE = Memory")
def test_create_table_tuple(self): table = Table('t1', self.metadata(), Column('x', types.Tuple(types.Int8, types.Float32)), engines.Memory()) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE t1 (' 'x Tuple(Int8, Float32)) ' 'ENGINE = Memory')
def test_create_table_without_engine(self): no_engine_table = Table('t1', self.metadata(), Column('x', types.Int32, primary_key=True), Column('y', types.String)) with self.assertRaises(exc.CompileError) as ex: self.compile(CreateTable(no_engine_table)) self.assertEqual(str(ex.exception), "No engine for table 't1'")
def test_create_table_map(self): table = Table('t1', self.metadata(), Column('x', types.Map(types.String, types.String)), engines.Memory()) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE t1 (' 'x Map(String, String)) ' 'ENGINE = Memory')
async def start(): engine = await make_engine() with (await engine) as conn: await conn.execute('DROP TABLE IF EXISTS sa_tbl4') await conn.execute('DROP SEQUENCE IF EXISTS id_sequence_seq') await conn.execute(CreateTable(tbl)) await conn.execute('CREATE SEQUENCE id_sequence_seq') return engine
def start(): engine = yield from make_engine() with (yield from engine) as conn: yield from conn.execute('DROP TABLE IF EXISTS sa_tbl4') yield from conn.execute('DROP SEQUENCE IF EXISTS id_sequence_seq') yield from conn.execute(CreateTable(tbl)) yield from conn.execute('CREATE SEQUENCE id_sequence_seq') return engine
async def create_data_base(self, drop_table=False): """ Create tables if they not exist :return: """ if drop_table: async with self.engine.acquire() as conn: await conn.execute('DROP TABLE IF EXISTS user_changes CASCADE') await conn.execute('DROP TABLE IF EXISTS users CASCADE') await conn.execute('DROP TABLE IF EXISTS user_changes') await conn.execute('DROP TABLE IF EXISTS session_list CASCADE') await conn.execute('DROP TABLE IF EXISTS files_info CASCADE') await conn.execute('DROP TABLE IF EXISTS messages CASCADE') await conn.execute(CreateTable(self.UserCharges)) await conn.execute(CreateTable(self.Users)) await conn.execute(CreateTable(self.Sessions)) await conn.execute(CreateTable(self.FilesInfo)) await conn.execute(CreateTable(self.Message))
async def setup_db(db_dsn, *, loop): async with aiopg.sa.create_engine(db_dsn, loop=loop) as db_engine: async with db_engine.acquire() as conn: await conn.execute(CreateTable(character_table)) await conn.execute(CreateTable(actor_table)) await conn.execute(character_table.insert().values([ dict(id=1, name='James T. Kirk', species='Human'), dict(id=2, name='Spock', species='Vulcan/Human'), dict(id=3, name='Leonard McCoy', species='Human'), ])) await conn.execute(actor_table.insert().values([ dict(id=1, character_id=1, name='William Shatner'), dict(id=2, character_id=2, name='Leonard Nimoy'), dict(id=3, character_id=3, name='DeForest Kelley'), dict(id=4, character_id=1, name='Chris Pine'), dict(id=5, character_id=2, name='Zachary Quinto'), dict(id=6, character_id=3, name='Karl Urban'), ]))
def test_create_table_column_default(self): table = Table('t1', self.metadata(), Column('x', types.Int8), Column('dt', types.DateTime, server_default=func.now()), engines.Memory()) self.assertEqual( self.compile(CreateTable(table)), 'CREATE TABLE t1 (' 'x Int8, ' 'dt DateTime DEFAULT now()) ' 'ENGINE = Memory')