def test_create_table_all_together(self, compiler): table = Table('t1', MetaData(), Column('id', Integer, primary_key=True), Column('name', String), redshift_diststyle="KEY", redshift_distkey="id", redshift_sortkey=["id", "name"]) create_table = CreateTable(table) actual = compiler.process(create_table) expected = (u"\nCREATE TABLE t1 (" u"\n\tid INTEGER NOT NULL, " u"\n\tname VARCHAR, " u"\n\tPRIMARY KEY (id)\n) " u"DISTSTYLE KEY DISTKEY (id) SORTKEY (id, name)\n\n") assert expected == actual, self._compare_strings(expected, actual)
async def test_implicit_transaction_success(asyncio_engine, mytable): if ':memory:' in str(asyncio_engine._engine.url): pytest.skip(":memory: connections don't persist across threads") async with asyncio_engine.begin() as conn: assert isinstance(conn, AsyncConnection) 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 asyncio_engine.execute(mytable.select()) rows = await result.fetchall() assert len(rows) == 1
def save_model(models): prefix = '../' if 'tools' in realpath('.') else '' try: if isfile(prefix + 'src/schema.ts'): nameStr = prefix + 'src/schema-' + datetime.now().isoformat()[0:10] if isfile(nameStr + '.bkp'): i = 1 while isfile(nameStr + '(' + str(i) + ')' + '.bkp'): i += 1 nameStr = nameStr + '(' + str(i) + ')' rename(prefix + 'src/schema.ts', nameStr + '.bkp') clientModel = client_only_model(models) with open(prefix + 'src/schema.ts', 'w+') as f: f.write('''/** * F2B Schema * * This file is automatically generated. DO NOT MAKE CHANGES HERE. Use the schema builder: * cd ltc-provider-app/tools * python3 schemaBuilder.py */ ''') f.write('export const schema = ') ujson.dump(clientModel, f, indent=2, sort_keys=False) f.write(';\n\nexport const schemaSQL = `') sqliteModel = convert_sql_types(clientModel) db_engine = create_engine('sqlite:///:memory:') metadata = MetaData() for key in sqliteModel: model = sqliteModel[key] table = generateTable(key, model, metadata) f.write(str(CreateTable(table).compile(db_engine)) + ';') f.write('`;\n\n') f.write('export const version = \'' + str(uuid4()) + '\';\n') except: import sys print('Failed to save model') import traceback traceback.print_exc() print(sys.exc_info()[0])
def handle(self, options, global_options, *args): from sqlalchemy.schema import CreateTable, CreateIndex engine = get_engine(options, global_options) tables = get_sorted_tables( get_tables(global_options.apps_dir, tables=args, engine_name=options.engine, settings_file=global_options.settings, local_settings_file=global_options.local_settings)) for name, t in tables: if t.__mapping_only__: continue print "%s;" % str( CreateTable(t).compile(dialect=engine.dialect)).rstrip() for x in t.indexes: print "%s;" % CreateIndex(x)
async def set_up(): import databases from sqlalchemy.schema import CreateTable from starlette.config import Config config = Config('.env') DATABASE_URL = config('DATABASE_URL') database = databases.Database(DATABASE_URL) await database.connect() await database.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";') for table in tables: try: await database.execute(str(CreateTable(table))) except Exception as e: print(e) await database.disconnect()
def create_table(engine, table_name, column_configs, schema=None, metadata=None): try: metadata = metadata or MetaData() columns = [Column(*args, **kwargs) for args, kwargs in column_configs] table = Table(table_name, metadata, *columns, schema=schema) engine.execute(CreateTable(table)) except ProgrammingError as e: if 'already exists' in str(e): pass else: raise e
def test_autoincrement_true(self): t = Table( "foo_table", MetaData(), Column( "foo", Integer(), Identity(always=True, start=3), primary_key=True, autoincrement=True, ), ) self.assert_compile( CreateTable(t), "CREATE TABLE foo_table (" "foo INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 3)" ", PRIMARY KEY (foo))", )
def create_tables(): """Create all the tables needed for the test.""" DB_URI = f'postgresql://{C.DB_USER}:{C.DB_PASSWORD}@{C.DB_HOST}:{C.DB_PORT}/{C.DB_NAME}' if database_exists(DB_URI): drop_database(DB_URI) create_database(DB_URI) engine = create_engine(DB_URI) conn = engine.connect() models = [ mBlueprint, mCargo, mApi, mProbe, mUser_probes, mJob, mUser_cargos, mLog ] for model in models: conn.execute(CreateTable(model).compile(engine).__str__()) add_blueprint = """INSERT INTO "public"."blueprints"("uuid","repository","name","tag","link","description","public","created_at","user_id") VALUES('2a83d4be0f7011e89e4b35694e577c22','cassinyio','notebook','02946e48',NULL,NULL,TRUE,E'2017-12-16 22:46:13.515348+00',NULL);""" conn.execute(add_blueprint)
def test_other_options(self): t = Table( "foo_table", MetaData(), Column( "foo", Integer(), Identity(always=True, start=3), nullable=False, unique=True, ), ) self.assert_compile( CreateTable(t), "CREATE TABLE foo_table (" "foo INTEGER GENERATED ALWAYS AS IDENTITY (START " "WITH 3), UNIQUE (foo))", )
def test_create_column_skip(self): @compiles(CreateColumn) def skip_xmin(element, compiler, **kw): if element.element.name == "xmin": return None else: return compiler.visit_create_column(element, **kw) t = Table( "t", MetaData(), Column("a", Integer), Column("xmin", Integer), Column("c", Integer), ) self.assert_compile(CreateTable(t), "CREATE TABLE t (a INTEGER, c INTEGER)")
def test_create_table_with_identity(self, compiler): table = Table( 't1', MetaData(), Column('id', Integer, primary_key=True, info={'identity': [1, 2]}), Column('name', String), ) create_table = CreateTable(table) actual = compiler.process(create_table) expected = ( u"\nCREATE TABLE t1 (" u"\n\tid INTEGER IDENTITY(1,2) NOT NULL, " u"\n\tname VARCHAR, " u"\n\tPRIMARY KEY (id)\n)\n\n" ) assert expected == actual, self._compare_strings(expected, actual)
async def prepare_tables(pg, verbose=True): """ Get all tables from models.py, delete them and create new tables :param pg: connect to DB engine(PostgreSQL) :param verbose: set logs :return: None """ tables = [getattr(models, table) for table in models.__all__] await delete_tables(pg, tables, verbose=verbose) async with pg.acquire() as conn: for table in tables: create_expr = CreateTable(table) try: await conn.execute(create_expr) if verbose: logger.debug('DB_CREATE: %s' % table) except psycopg2.ProgrammingError as e: logger.error('DB_CREATE(ERROR): %s' % e)
def createTableFromTable(name, tableNam, engine): """copy一个已有表的结构,并创建新的表 """ metadata = MetaData(engine) BaseSQL.metadata.reflect(engine) # 获取原表对象 table = BaseSQL.metadata.tables[tableNam] # 获取原表建表语句 c = str(CreateTable(table)) print(c) print(tableNam, name) # 替换表名 c = c.replace('"', '').replace('CREATE TABLE {}'.format(tableNam), "CREATE TABLE if not exists {}".format(name)) print(c) db_conn = engine.connect() db_conn.execute(c) db_conn.close() BaseSQL.metadata.clear()
def test_transaction_rollback(self): metadata = sqlalchemy.MetaData() tbl = sqlalchemy.Table( 'mytable', metadata, sqlalchemy.Column("id", sqlalchemy.Integer(), primary_key=True), sqlalchemy.Column("num", sqlalchemy.Integer()), ) conn = self.get_connection() self.successResultOf(conn.execute(CreateTable(tbl))) trx = self.successResultOf(conn.begin()) self.successResultOf(conn.execute(tbl.insert().values(num=42))) rows = self.execute_fetchall(conn, tbl.select()) assert len(rows) == 1 self.successResultOf(trx.rollback()) rows = self.execute_fetchall(conn, tbl.select()) assert len(rows) == 0
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_create_table_with_diststyle(self, compiler): table = Table('t1', MetaData(), Column('id', Integer, primary_key=True), Column('name', String), redshift_diststyle="EVEN") create_table = CreateTable(table) actual = compiler.process(create_table) expected = ( u"\nCREATE TABLE t1 (" u"\n\tid INTEGER NOT NULL, " u"\n\tname VARCHAR, " u"\n\tPRIMARY KEY (id)\n) " u"DISTSTYLE EVEN\n\n" ) assert expected == actual, self._compare_strings(expected, actual)
async 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_unregister_engine(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)) tracer.clear() sqlalchemy_opentracing.unregister_engine(self.engine) # Further events should cause no spans at all. sel = select([self.users_table]) sqlalchemy_opentracing.set_traced(sel) self.engine.execute(sel) self.assertEqual(0, len(tracer.spans))
async def fetch_content(self): """Корутина сбора данных со страницы вакансии. Корутина получает на вход очередь c данными о вакансиях. Назначение: асинхронно собрать информацию о вакансиях и записать ее в базу данных. Сначала метод создает таблицу в базе данных для записи информации на текущую дату. Далее запускает бесконечный цикл, в котором ожидает получения элемента(списка с данными) из очереди. Получив элемент, загружает по полученной из элемента ссылке страницу с информацией о вакансии. Со страницы асинхронно получает полное описание вакансии. Полученные данные(ссылка на вакансию, название вакансии, зарплата, требования к кандидату, название компнании, тип занятости, полное описание вакансии)записываем в базу. Условием выхода из цикла является получение элемента None из очереди. Работа данного метода связана с работой метода get_links. Получив ссылку со страницы результатов поиска, метод get_links передает ссылку в метод fetch_content. Метод fetch_content начинает загрузку страницы с вакансией и сбор информации. Не дождавшись завершения процесса и получив новый элемент из очереди, метод fetch_content открывает новую страницу по ссылке и начинает сбор данных с нее. Так продолжается пока в очереди есть элементы. Таким образом одновременно идет сбор информации со всех ссылок из списка, что позволяет в разы сократить время выполнения задачи по сбору данных. """ await engine.execute( CreateTable(MoiKrug_db) ) # создаем таблицу для хранения данных о вакансии на текущую дату while True: item = await self.queue.get( ) # ждем пока появится новый элемент в очереди if item is None: # Элемент None означает конец очереди break async with get_session(self.service, self.browser) as web_session: await web_session.get(item[0]) # загружаем страницу вакансии description_object = await web_session.get_element( 'div[class=vacancy_description]') description = await description_object.get_text( ) # получаем описание вакансии async with engine.connect() as conn: async with conn.begin() as trans: await conn.execute(MoiKrug_db.insert().values( link=item[0], # записываем данные в базу title=item[1], salary=item[2], skills=item[3], company=item[4], occupation=item[5], description=description))
def get_create_table_command(table_name, engine): metadata = MetaData() # TODO: handle for non-"public" schema bind_schema = None metadata.reflect(bind=engine, schema=bind_schema) # https://stackoverflow.com/a/5605077/4709762 insp = reflection.Inspector.from_engine(engine) indexes = [] for idx in insp.get_indexes(table_name): if 'duplicates_constraint' not in idx: create_idx_text = format_create_index_command(idx, table_name) indexes.append(create_idx_text) # unfound object foo returns "KeyError: 'foo'" here tbl = metadata.tables[table_name] create_table_text = str( CreateTable(tbl).compile(dialect=postgresql.dialect())) + ";\n" create_table_text = remove_sequence(create_table_text) create_table_text = create_table_text + "\n".join(indexes) + "\n" return create_table_text
def make_comparison_table(self, measure, source_schema): key_fields = ['patient_id', 'population_id'] all_cols = [] key_table = Table(self.key_table_name(measure), self.meta, schema=self.key_schema, autoload=True) source_table = Table(self.key_table_name(measure), self.meta, schema=source_schema, autoload=True) for field in key_fields: kcol = key_table.c.get(field) all_cols.append(kcol) for col in key_table.c: if col.name.startswith(RESULT_COLUMN_PREFIX): basename=col.name[len(RESULT_COLUMN_PREFIX):] all_cols.append(source_table.c.get(col.name).label(basename)) all_cols.append(col.label(basename + EXPECTED_SUFFIX)) cnames = [] table = Table(self.comparison_table_name(measure), self.meta, schema=source_schema) for col in all_cols: table.append_column(Column(col.name, col.type)) cnames.append(col.name) print(self.terminated_statement(str(DropTable(table, bind=self.engine)))) print(self.terminated_statement(str(CreateTable(table, bind=self.engine)))) join_conds = [] for field in key_fields: scol=source_table.c.get(field) dcol = key_table.c.get(field) join_conds.append(or_( and_(scol == None, dcol == None), scol == dcol)) query = union( select(all_cols).\ select_from(key_table.\ join(source_table, and_(*join_conds), isouter=True)), select(all_cols).\ select_from(source_table.\ join(key_table, and_(*join_conds), isouter=True))) ins = table.insert().from_select(cnames, query) print(self.terminated_statement(sql_to_string(ins))) self.comparison_tables[measure] = table
def _copy_existing_table(self, conn, table_obj, number_of_rows=-1): """Copy an existing table structure with an additional suffix""" # store rows of original table table_entries = conn.execute(table_obj.select()).all() # take all data or just a subset of the original table if number_of_rows != -1: try: test_entries = random.sample(table_entries, number_of_rows) except ValueError: logging.error( "Number of rows specified in yaml file exceeds rows in table" ) raise else: # in case of no subset take the whole data set, hence all entries # in table test_entries = table_entries self._change_table_constraints(table_obj) # create table object, store it and execute it create_res = CreateTable(table_obj, bind=conn) # add result of create table statement to statement string self.string_to_store += str(create_res) + ";\n" # get column order for insert statements column_order = [col[0] for col in table_obj.columns._collection] # add subset of entries for s in test_entries: insert_res = insert(table_obj, values=s, bind=conn) s_convert = [ "'" + test + "'" if isinstance(test, str) else test for test in s ] # store insert statements in statement string self.string_to_store += ( str(insert_res) % dict(zip(column_order, s_convert)) + ";\n")
def ddl(self, dialect=None, creates=True, drops=True): """ Returns SQL to define the table. """ dialect = self._dialect(dialect) creator = CreateTable(self.table).compile(mock_engines[dialect]) creator = "\n".join(l for l in str(creator).splitlines() if l.strip()) # remove empty lines comments = "\n\n".join(self._comment_wrapper.fill("in %s: %s" % (col, self.comments[col])) for col in self.comments) result = [] if drops: result.append(self._dropper(dialect) + ';') if creates: result.append("%s;\n%s" % (creator, comments)) for child in self.children.values(): result.append(child.ddl(dialect=dialect, creates=creates, drops=drops)) return '\n\n'.join(result)
def test_on_null(self): t = Table( "foo_table", MetaData(), Column( "foo", Integer(), Identity(always=False, on_null=True, start=42, order=True), ), ) text = " ON NULL" if testing.against("oracle") else "" self.assert_compile( CreateTable(t), ( "CREATE TABLE foo_table (foo INTEGER GENERATED BY DEFAULT" + text + " AS IDENTITY (START WITH 42 ORDER))" ), )
def _create_new_table(self, conn, table_name, table_config, n_rows): columns = [] for key, val in table_config.get("column_names").items(): col_type = val.get("type") col_obj = Column(key, getattr(sqlalchemy, col_type)) columns.append(col_obj) # create table instance table_obj = Table(table_name, self._get_meta_data(conn), *columns) # prepare Create table with bind of current connection and execute it create_res = CreateTable(table_obj, bind=conn) conn.execute(create_res) # add result of create table statement to statement string self.string_to_store += str(create_res) + ";\n" val_dict = {} for key, val in table_config.get("column_names").items(): val_dict[key] = get_random_list(val, n_rows) # get column order for insert statements column_order = [col[0] for col in table_obj.columns._collection] test_entries = self.rearrange_dict_for_insert(column_order, val_dict, n_rows) # add subset of entries for s in test_entries: insert_res = insert(table_obj, values=s, bind=conn) s_convert = [ "'" + test + "'" if isinstance(test, str) else test for test in s ] # store insert statements in statement string self.string_to_store += ( str(insert_res) % dict(zip(column_order, s_convert)) + ";\n") # conn.execute(insert_res) conn.execute(table_obj.insert(s))
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)
async def create(conn: Database, data): data = jsonable_encoder(data) transaction = await conn.transaction() id = False try: id = await conn.execute(query=Asset.__table__.insert(), values=data["base"]) model = AssetHI.model(point_id=id) # register to metadata for all pump_unit if data["base"]["asset_type"] == 0: await conn.execute(str(CreateTable(model.__table__).compile(meta_engine))) await transaction.commit() return True except Exception as e: # print(e) if id: query = Asset.__table__.delete().where(Asset.__table__.c.id == id) await conn.execute( query=str(query.compile(compile_kwargs={"literal_binds": True})) ) await transaction.commit() return False
async def async_create_database(loop, conf): """ Need fixes: * correct way to retain creation order * remove try/except """ db = await create_engine(**conf, loop=loop) tables = [CreateTable(table).compile(db) for table in (metadata.tables['companies'], metadata.tables['accounts'], metadata.tables['software'], metadata.tables['software_orders'], metadata.tables['software_order_items'])] async with db.acquire() as conn: for table_create_stmt in tables: await conn.execute(table_create_stmt.string) db.close() await db.wait_closed()
def get_pool(): pool = yield from create_engine(dsn) connection = yield from pool.acquire() try: result = yield from connection.execute( 'SELECT tablename FROM pg_tables ' 'WHERE schemaname=%s', ('public', )) existing_table_names = {name[0] for name in result} print('Existing tables:', existing_table_names) for name, table in tables.metadata.tables.items(): if name not in existing_table_names: create_statement = CreateTable(table) print(create_statement.compile(dialect=dialect)) yield from connection.execute(create_statement) finally: connection.close() return pool
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)