我们从Python开源项目中，提取了以下47个代码示例，用于说明如何使用sqlalchemy.schema.CreateTable()。
def create_table(self): if not self.schema: self.schema = DB_ETL_SCHEMA if not self.create_schema(): return False logger.info('try to create table {} in {}'.format( self.sql_table_name, self.schema )) if self.exist_table(): return True table = self.get_sql_table_object(need_columns=True) db_table = self.local_engine.execute(CreateTable(table)) for index in table.indexes: self.local_engine.execute(CreateIndex(index)) return db_table
def print_create_table(tables): app.config.from_object('config.default') database.init_app(app) engine = database.session.get_bind() for class_name in tables: cls = get_class(class_name) for c in cls.__table__.columns: if not isinstance(c.type, Enum): continue t = c.type sql = str(CreateEnumType(t).compile(engine)) click.echo(sql.strip() + ';') for index in cls.__table__.indexes: sql = str(CreateIndex(index).compile(engine)) click.echo(sql.strip() + ';') sql = str(CreateTable(cls.__table__).compile(engine)) click.echo(sql.strip() + ';')
def test_implicit_transaction_failure(engine, mytable): await engine.execute(CreateTable(mytable)) with pytest.raises(RuntimeError): async with engine.begin() as conn: assert isinstance(conn, AsyncioConnection) await conn.execute(mytable.insert()) result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1 raise RuntimeError # Transaction should have been rolled back automatically result = await engine.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 0
def test_implicit_transaction_commit_failure(engine, mytable): # Patch commit to raise an exception. We can then check that a) the # transaction is rolled back, and b) that the exception is reraised. patch_commit = patch.object( AsyncioTransaction, 'commit', side_effect=RuntimeError) # Patch a coroutine in place of AsyncioTransaction.rollback that calls # a Mock which we can later check. mock_rollback = Mock() async def mock_coro(*args, **kwargs): mock_rollback(*args, **kwargs) patch_rollback = patch.object(AsyncioTransaction, 'rollback', mock_coro) with pytest.raises(RuntimeError): with patch_commit, patch_rollback: async with engine.connect() as conn: await conn.execute(CreateTable(mytable)) async with conn.begin() as trans: await conn.execute(mytable.insert()) assert mock_rollback.call_count == 1
def test_transaction_rollback(engine, mytable): async with engine.connect() as conn: await conn.execute(CreateTable(mytable)) trans = await conn.begin() await conn.execute(mytable.insert()) result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1 await trans.rollback() result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 0
def test_transaction_context_manager_failure(engine, mytable): async with engine.connect() as conn: await conn.execute(CreateTable(mytable)) with pytest.raises(RuntimeError): async with conn.begin() as trans: await conn.execute(mytable.insert()) result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1 raise RuntimeError result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 0
def test_begin_nested(engine, mytable): async with engine.connect() as conn: await conn.execute(CreateTable(mytable)) async with conn.begin() as trans1: await conn.execute(mytable.insert()) async with conn.begin_nested() as trans2: assert isinstance(trans2, AsyncioTransaction) await conn.execute(mytable.insert()) await trans2.rollback() await trans1.commit() result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1
def create_table(request, sa_table, database, loop, create_entries): async def f(rows): create_expr = CreateTable(sa_table) async with database.acquire() as conn: await conn.execute(create_expr) values = create_entries(rows) query1 = sa_table.insert().values(values) await conn.execute(query1) await conn.execute('commit;') return sa_table yield f async def fin(): drop_expr = DropTable(sa_table) async with database.acquire() as conn: await conn.execute(drop_expr) await conn.execute('commit;') loop.run_until_complete(fin())
def test_traced(self): tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, False, False) sqlalchemy_opentracing.register_engine(self.engine) creat = CreateTable(self.users_table) sqlalchemy_opentracing.set_traced(creat) self.engine.execute(creat) self.assertEqual(1, len(tracer.spans)) self.assertEqual(tracer.spans[0].operation_name, 'create_table') self.assertEqual(tracer.spans[0].is_finished, True) self.assertEqual(tracer.spans[0].tags, { 'component': 'sqlalchemy', 'db.statement': 'CREATE TABLE users (id INTEGER NOT NULL, name VARCHAR, PRIMARY KEY (id))', 'db.type': 'sql', 'sqlalchemy.dialect': 'sqlite', }) self.assertEqual(False, sqlalchemy_opentracing.get_traced(creat))
def test_traced_all_engines(self): # Don't register the engine explicitly. tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, trace_all_engines=True, trace_all_queries=False) creat = CreateTable(self.users_table) sqlalchemy_opentracing.set_traced(creat) self.engine.execute(creat) # Unregister the main Engine class before doing our assertions, # in case we fail. sqlalchemy_opentracing.unregister_engine(Engine) self.assertEqual(1, len(tracer.spans)) self.assertEqual('create_table', tracer.spans[0].operation_name) self.assertEqual(True, tracer.spans[0].is_finished)
def test_traced_transaction_nested(self): tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, False, False) sqlalchemy_opentracing.register_engine(self.engine) creat = CreateTable(self.users_table) ins = self.users_table.insert().values(name='John Doe') sel = select([self.users_table]) parent_span = DummySpan('parent span') conn = self.engine.connect() with conn.begin() as trans: sqlalchemy_opentracing.set_parent_span(conn, parent_span) conn.execute(creat) with conn.begin() as trans2: conn.execute(ins) conn.execute(sel) self.assertEqual(3, len(tracer.spans)) self.assertEqual(True, all(map(lambda x: x.is_finished, tracer.spans))) self.assertEqual(True, all(map(lambda x: x.child_of == parent_span, tracer.spans))) self.assertEqual(['create_table', 'insert', 'select'], map(lambda x: x.operation_name, tracer.spans))
def test_traced_after_transaction(self): tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, False, False) sqlalchemy_opentracing.register_engine(self.engine) creat = CreateTable(self.users_table) conn = self.engine.connect() with conn.begin() as tx: sqlalchemy_opentracing.set_traced(conn) conn.execute(creat) self.assertEqual(1, len(tracer.spans)) # Do something right after with this connection, # no tracing should happen. tracer.clear() ins = self.users_table.insert().values(name='John Doe') with conn.begin() as tx: conn.execute(ins) self.assertEqual(0, len(tracer.spans))
def test_traced_clear_connection(self): tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, False, False) sqlalchemy_opentracing.register_engine(self.engine) creat = CreateTable(self.users_table) ins = self.users_table.insert().values(name='John Doe') conn = self.engine.connect() with conn.begin() as tx: sqlalchemy_opentracing.set_traced(conn) conn.execute(creat) # Stop tracing from this point. sqlalchemy_opentracing.clear_traced(conn) conn.execute(ins) self.assertEqual(1, len(tracer.spans)) self.assertEqual('create_table', tracer.spans[0].operation_name)
def _insert_datapoint(self): """Insert first datapoint in the database. Args: None Returns: None """ # Insert if db_datapoint.idx_datapoint_exists(1) is False: record = Datapoint( id_datapoint=general.encode(self.reserved), agent_label=general.encode(self.reserved), agent_source=general.encode(self.reserved) ) print(CreateTable(record.__table__)) database = db.Database() database.add(record, 1047)
def create_table(self, table): if util.sqla_07: table.dispatch.before_create(table, self.connection, checkfirst=False, _ddl_runner=self) self._exec(schema.CreateTable(table)) if util.sqla_07: table.dispatch.after_create(table, self.connection, checkfirst=False, _ddl_runner=self) for index in table.indexes: self._exec(schema.CreateIndex(index))
def test_implicit_transaction_success(engine, mytable): async with engine.begin() as conn: assert isinstance(conn, AsyncioConnection) await conn.execute(CreateTable(mytable)) await conn.execute(mytable.insert()) result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1 # Transaction should have been committed automatically result = await engine.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1
def test_has_table(engine, mytable): assert not await engine.has_table('mytable') await engine.execute(CreateTable(mytable)) assert await engine.has_table('mytable')
def test_table_names(engine, mytable): assert await engine.table_names() == [] await engine.execute(CreateTable(mytable)) assert await engine.table_names() == ['mytable']
def test_transaction_commit(engine, mytable): async with engine.connect() as conn: trans = await conn.begin() await conn.execute(CreateTable(mytable)) await conn.execute(mytable.insert()) result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1 await trans.commit() result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1
def test_transaction_context_manager_success(engine, mytable): async with engine.connect() as conn: await conn.execute(CreateTable(mytable)) async with conn.begin() as trans: await conn.execute(mytable.insert()) result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1 result = await conn.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1
def test_rowcount(engine, mytable): await engine.execute(CreateTable(mytable)) await engine.execute(mytable.insert()) await engine.execute(mytable.insert()) result = await engine.execute(mytable.delete()) assert result.rowcount == 2
def test_inserted_primary_key(engine, mytable): await engine.execute(CreateTable(mytable)) result = await engine.execute(mytable.insert()) assert result.inserted_primary_key == [1]
def setup(self): """Setting up SQL table, if it not exists.""" try: engine = await self.db created = False if not await engine.has_table(self.table_name): # create table logger.info("Creating SQL table [{}]".format(self.table_name)) items = self._get_table() await engine.execute(CreateTable(items)) # create indeces conn = await engine.connect() await conn.execute( "CREATE INDEX `lb_last_updated` ON `{}` (`source_id` DESC,`updated` DESC);".format(self.table_name)) await conn.execute( "CREATE INDEX `lb_post` ON `{}` (`target_id` DESC,`post_id` DESC);".format(self.table_name)) await conn.close() created = True # create control table if not already created. if self.control_table_name and not await engine.has_table(self.control_table_name): # create table logger.info("Creating SQL control table [{}]".format(self.control_table_name)) items = self._get_control_table() await engine.execute(CreateTable(items)) created = True return created except Exception as exc: logger.error("[DB] Error when setting up SQL table: {}".format(exc)) return False
def preapre_tables(pg): tables = [db.question, db.choice] async with pg.acquire() as conn: for table in reversed(tables): drop_expr = DropTable(table) try: await conn.execute(drop_expr) except psycopg2.ProgrammingError: pass async with pg.acquire() as conn: for table in tables: create_expr = CreateTable(table) await conn.execute(create_expr)
def prepare_tables(pg): tables = [db.post, db.tag, db.comment] 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_traced_none(self): tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, False, False) sqlalchemy_opentracing.register_engine(self.engine) creat = CreateTable(self.users_table) self.engine.execute(creat) self.assertEqual(0, len(tracer.spans))
def test_traced_all_queries(self): tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, False, trace_all_queries=True) sqlalchemy_opentracing.register_engine(self.engine) creat = CreateTable(self.users_table) self.engine.execute(creat) self.assertEqual(1, len(tracer.spans))
def test_traced_error(self): tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, False, False) sqlalchemy_opentracing.register_engine(self.engine) creat = CreateTable(self.users_table) self.engine.execute(creat) self.assertEqual(0, len(tracer.spans)) sqlalchemy_opentracing.set_traced(creat) try: self.engine.execute(creat) except OperationalError: pass # Do nothing - it's responsibility of OT to finish tracing it. self.assertEqual(1, len(tracer.spans)) self.assertEqual(tracer.spans[0].is_finished, True) self.assertEqual(tracer.spans[0].tags, { 'component': 'sqlalchemy', 'db.statement': 'CREATE TABLE users (id INTEGER NOT NULL, name VARCHAR, PRIMARY KEY (id))', 'db.type': 'sql', 'sqlalchemy.dialect': 'sqlite', 'sqlalchemy.exception': 'table users already exists', 'error': 'true', }) self.assertEqual(False, sqlalchemy_opentracing.get_traced(creat))
def test_traced_rollback(self): tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, False, False) sqlalchemy_opentracing.register_engine(self.engine) creat = CreateTable(self.users_table) ins = self.users_table.insert().values(name='John Doe') # Don't trace this. self.engine.execute(creat) parent_span = DummySpan('parent span') conn = self.engine.connect() try: with conn.begin() as tx: sqlalchemy_opentracing.set_parent_span(conn, parent_span) conn.execute(ins) conn.execute(creat) except OperationalError: pass self.assertEqual(2, len(tracer.spans)) self.assertEqual(True, all(map(lambda x: x.is_finished, tracer.spans))) self.assertEqual(True, all(map(lambda x: x.child_of == parent_span, tracer.spans))) self.assertEqual(['insert', 'create_table'], map(lambda x: x.operation_name, tracer.spans)) self.assertEqual(['false', 'true'], map(lambda x: x.tags.get('error', 'false'), tracer.spans))
def test_traced_after_rollback(self): tracer = DummyTracer() sqlalchemy_opentracing.init_tracing(tracer, False, False) sqlalchemy_opentracing.register_engine(self.engine) creat = CreateTable(self.users_table) # Create a table, but don't trace it conn = self.engine.connect() with conn.begin() as tx: conn.execute(creat) try: with conn.begin() as tx: sqlalchemy_opentracing.set_traced(conn) conn.execute(creat) except OperationalError: pass self.assertEqual(1, len(tracer.spans)) # Do something right after with this connection, # no tracing should happen. tracer.clear() ins = self.users_table.insert().values(name='John Doe') with conn.begin() as tx: conn.execute(ins) self.assertEqual(0, len(tracer.spans))
def test_traced_property(self): stmt_obj = CreateTable(self.users_table) sqlalchemy_opentracing.set_traced(stmt_obj) self.assertEqual(True, sqlalchemy_opentracing.get_traced(stmt_obj))
def test_has_parent(self): span = DummySpan() stmt = CreateTable(self.users_table) sqlalchemy_opentracing.set_parent_span(stmt, span) self.assertEqual(True, sqlalchemy_opentracing.has_parent_span(stmt)) self.assertEqual(span, sqlalchemy_opentracing.get_parent_span(stmt))
def test_has_parent_none(self): stmt = CreateTable(self.users_table) sqlalchemy_opentracing.set_traced(stmt) self.assertEqual(False, sqlalchemy_opentracing.has_parent_span(stmt)) self.assertEqual(None, sqlalchemy_opentracing.get_parent_span(stmt))
def sql_schema(self): from sqlalchemy.schema import CreateTable return str(CreateTable(self.table).compile(self.pd_sql.connectable))
def create_table(table_name, load_postgis, json_table_schema, if_not_exists, indexes_fields, connection_string): if load_postgis: load_postgis_support() creds = re.match(carto_connection_string_regex, connection_string).groups() statement = CreateTable(get_table(table_name, json_table_schema)) str_statement = statement.compile(dialect=postgresql.dialect()) if if_not_exists: str_statement = str(str_statement).replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS') carto_sql_call(creds, str_statement) if indexes_fields: create_indexes(creds, table_name, indexes_fields)
def run(self): """Setup database. Args: None Returns: None """ # Initialize key variables use_mysql = True pool_size = 25 max_overflow = 25 config = self.config mappings = [Agent, Department, Device, Billcode, DeviceAgent, Datapoint, AgentName] # Create DB connection pool if use_mysql is True: # Add MySQL to the pool engine = create_engine( URL, echo=False, encoding='utf8', max_overflow=max_overflow, pool_size=pool_size, pool_recycle=3600) # Try to create the database shared.print_ok('Attempting to create database tables') try: sql_string = ( 'ALTER DATABASE %s CHARACTER SET utf8mb4 ' 'COLLATE utf8mb4_general_ci') % (config.db_name()) engine.execute(sql_string) except: log_message = ( 'Cannot connect to database %s. ' 'Verify database server is started. ' 'Verify database is created. ' 'Verify that the configured database authentication ' 'is correct.') % (config.db_name()) log.log2die(1046, log_message) # Apply schemas shared.print_ok('Generating Schemas.') with open('infoset.sql', 'w') as infoset_mysql: for mapping in mappings: print(CreateTable(mapping.__table__)) infoset_mysql.write(str(CreateTable(mapping.__table__))) infoset_mysql.close() # Insert database entries self._insert_agent_device() self._insert_billcode() self._insert_department() self._insert_datapoint() self._insert_config()
