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
python类CreateTable()的实例源码
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 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 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 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 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 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 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 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 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)