def test_migration_check_success(self, tmpfile, caplogger): ddb = Database(provider="sqlite", filename=str(tmpfile)) init_onetable(ddb) s1 = Schema(ddb) s1.version = "0.5.0" v1 = s1.schema def check_cb(check_db): with db_session: assert ( check_db.execute( "select * from sqlite_master").fetchone()[4] == 'CREATE TABLE "bla" ("key" TEXT NOT NULL PRIMARY KEY, "texta" TEXT, "textb" TEXT, "textc" TEXT)' ) m = MakeMigrations(tmpfile, Version("1.3.2"), migrations) assert m(check_cb, lambda x: True) assert "Error" not in caplogger.read() ddb2 = Database(provider="sqlite", filename=str(tmpfile)) s2 = Schema(ddb2) assert ( s2.schema == """CREATE TABLE "bla" ("key" TEXT NOT NULL PRIMARY KEY, "texta" TEXT, "textb" TEXT, "textc" TEXT)\n""" ) assert s2.version == Version("1.3.2")
def clean_core(args): major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) schema = args.name db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', **args.connection) db_config.generate_mapping() with db_session: config = db_config.Config.get(name=schema, version=major_version) if not config: raise KeyError('schema not exists or version incompatible') config = config.config for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', **args.connection) db.generate_mapping() with db_session: db.execute(f'TRUNCATE TABLE "{schema}"."MoleculeSearchCache", ' f'"{schema}"."ReactionSearchCache" RESTART IDENTITY')
def update_core(args): major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) schema = args.name db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db_config.generate_mapping() with db_session: config = db_config.Config.get(name=schema, version=major_version) if not config: raise KeyError('schema not exists or version incompatible') config = config.config for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db.generate_mapping() with db_session: db.execute(init_session.replace('{schema}', schema)) db.execute(merge_molecules.replace('{schema}', schema)) db.execute(insert_molecule.replace('{schema}', schema)) db.execute(after_insert_molecule.replace('{schema}', schema)) db.execute(delete_molecule.replace('{schema}', schema)) db.execute(insert_reaction.replace('{schema}', schema)) db.execute(search_similar_molecules.replace('{schema}', schema)) db.execute(search_substructure_molecule.replace('{schema}', schema)) db.execute(search_similar_reactions.replace('{schema}', schema)) db.execute(search_substructure_reaction.replace('{schema}', schema)) db.execute( search_substructure_fingerprint_molecule.replace( '{schema}', schema)) db.execute( search_similar_fingerprint_molecule.replace('{schema}', schema)) db.execute(search_reactions_by_molecule.replace('{schema}', schema)) db.execute(search_mappingless_reaction.replace('{schema}', schema))
def index_core(args): major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) schema = args.name db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db_config.generate_mapping() with db_session: config = db_config.Config.get(name=schema, version=major_version) if not config: raise KeyError('schema not exists or version incompatible') config = config.config for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db.generate_mapping() with db_session: db.execute( f'CREATE INDEX idx_moleculestructure__smlar ON "{schema}"."MoleculeStructure" USING ' 'GIST (fingerprint _int4_sml_ops)') db.execute( f'CREATE INDEX idx_moleculestructure__subst ON "{schema}"."MoleculeStructure" USING ' 'GIN (fingerprint gin__int_ops)') db.execute( f'CREATE INDEX idx_reactionindex__smlar ON "{schema}"."ReactionIndex" USING ' 'GIST (fingerprint _int4_sml_ops)') db.execute( f'CREATE INDEX idx_reactionindex__subst ON "{schema}"."ReactionIndex" USING ' 'GIN (fingerprint gin__int_ops)')
def define_model(): db = Database() class Entry(db.Entity): date_added = Required(datetime) cam_entries = Set('CamLogEntry') class CamLogEntry(db.Entity): entry = Required(Entry) cam = Required(str) loc_x = Optional(float) loc_y = Optional(float) mean = Required(float) max = Required(float) fit_result = Optional(lambda: FitResult) class FitResult(db.Entity): cam_entry = Required(CamLogEntry) x0 = Required(float) y0 = Required(float) sigma_x = Required(float) sigma_y = Required(float) theta = Required(float) A = Required(float) off = Required(float) return db #, Entry, CamLogEntry, FitResult
def all_stock_candles(self): start_date = self.app.pargs.start_date end_date = self.app.pargs.end_date loop_count = self.app.pargs.loop_count file_name = self.app.pargs.file_name is_short = self.app.pargs.is_short self.app.log.info('start_date: {}, end_date: {}, loop: {}, file_name: {}, is_short: {}'.format(start_date, end_date, loop_count, file_name, is_short)) data_conf = self.app.config.get('moontrader', 'data') db_ebest = Database() db_adapter.define_korea_stock(db_ebest) db_adapter.bind(db_ebest, data_conf['dir'], DB_FILE_NAME) db_adapter.init(db_ebest) codes = db_adapter.get_stock_codes() param_start = ' --start {}'.format(start_date) if start_date else '' param_end = ' --end {}'.format(end_date) if end_date else '' param_loop = ' --loop {}'.format(loop_count) if loop_count else '' if is_short: param_start = ' --start {yesterday}' param_end = ' --end {today}' with open(file_name, 'w') as sfile: for code in codes: cmd = 'moontrader stocks -s candle {}{}{}{}'.format(code.cd, param_start, param_end, param_loop) print(cmd) sfile.write(cmd + '\n')
def __init__(self, data): db_type = data.get('type', 'postgres') db_user = data.get('user', 'dameng') db_password = data.get('password', 'hello') db_host = data.get('host', 'localhost') db_name = data.get('name', 'store') self.db = Database(db_type, user=db_user, password=db_password, host=db_host, database=db_name) body = dict(__doc__='docstring', create_at=Required(datetime, sql_default='CURRENT_TIMESTAMP', default=lambda: datetime.utcnow()), update_at=Required(datetime, sql_default='CURRENT_TIMESTAMP', default=lambda: datetime.utcnow()), key=Required(str, index=True, unique=True), value=Required(Json, volatile=True)) table = data.get("table", "Store") if table[0].islower(): table = table[0].upper() + table[1:] self.Store = type(table, (self.db.Entity, ), body) self.db.generate_mapping(create_tables=True, check_tables=True) self.ids = set()
def define_db(*args, **kwargs): set_sql_debug(app.debug, True) db = Database() db.bind(*args, **kwargs) define_entities(db) db.generate_mapping(create_tables=True) return db
def __init__( self, filename: Union[str, Path], # chemin vers la ddb actual_version: Union[ Version, str] = None, # version actuelle (dans les sources) migrations: dict = None, # pool de migrations ): # migrations = migrations self.actual_version = (actual_version if isinstance( actual_version, Version) else Version(actual_version)) self.old_file = Path(filename) # ddb à faire migrer # création d'une base temporaire pour effectuer les migrations tmp = tempfile.NamedTemporaryFile(suffix=".sqlite", delete=False) tmp.close() self.tmp_file = Path(tmp.name) shutil.copy(self.old_file, self.tmp_file) # duplication de la DDB # outils pour migrations self.tmp_db = Database(provider="sqlite", filename=tmp.name) self.schema = Schema(file=self.tmp_db) if self.schema.version == self.actual_version: logger.info(f"version {self.actual_version}: No migration needed") return self.migrator = Migrator(self.tmp_db, self.actual_version, migrations) logger.info( f"starting migrations from version {self.schema.version} to {self.actual_version}" )
def install(app, db=None): app.cliarguments.append(DatabaseCLI) if db is None: db = Database() app.db = db @app.when def ready(app): settings = app.settings if 'db' not in settings: raise ValueError( 'Please provide db.url configuration entry, for example: ' \ 'postgres://:@/dbname' ) url = uri.parse(settings.db.url) db.bind(**url) db.generate_mapping(create_tables=True) @app.when def shutdown(app): app.db.disconnect() if app.db.provider is not None: app.db.provider.disconnect() app.db.provider = None app.db.schema = None return db
def pony_setup(request, app, tmpdir, realdburl): pytest.importorskip("pony") from pony.orm import Database, Optional, Required, Set from pony.orm.core import SetInstance SetInstance.append = SetInstance.add db = Database() class Role(db.Entity): name = Required(str, unique=True) description = Optional(str, nullable=True) users = Set(lambda: User) class User(db.Entity): email = Required(str) fs_uniquifier = Required(str, nullable=False) username = Optional(str) security_number = Optional(int) password = Optional(str, nullable=True) last_login_at = Optional(datetime) current_login_at = Optional(datetime) tf_primary_method = Optional(str, nullable=True) tf_totp_secret = Optional(str, nullable=True) tf_phone_number = Optional(str, nullable=True) us_totp_secrets = Optional(str, nullable=True) us_phone_number = Optional(str, nullable=True) last_login_ip = Optional(str) current_login_ip = Optional(str) login_count = Optional(int) active = Required(bool, default=True) confirmed_at = Optional(datetime) roles = Set(lambda: Role) def has_role(self, name): return name in {r.name for r in self.roles.copy()} if realdburl: db_url, db_info = _setup_realdb(realdburl) pieces = urlsplit(db_url) db.bind( provider=pieces.scheme.split("+")[0], user=pieces.username, password=pieces.password, host=pieces.hostname, database=pieces.path[1:], ) else: app.config["DATABASE"] = {"name": ":memory:", "engine": "pony.SqliteDatabase"} db.bind("sqlite", ":memory:", create_db=True) db.generate_mapping(create_tables=True) def tear_down(): if realdburl: _teardown_realdb(db_info) request.addfinalizer(tear_down) return PonyUserDatastore(db, User, Role)
def __init__(self, token): self.db = Database() logging.basicConfig( format='%(asctime)s - %(name)s - %(levelname)s - %(message)s', level=logging.INFO) self.handler = Handler(token)
def sector_map(self): sector = self.app.pargs.sector self.connect_ebest() if self.app.pargs.save: data_conf = self.app.config.get('moontrader', 'data') db_ebest = Database() db_adapter.define_korea_stock(db_ebest) db_adapter.bind(db_ebest, data_conf['dir'], DB_FILE_NAME) db_adapter.init(db_ebest) db_adapter.delete_stock_sector_code_map(sector) start_code = '' while True: response = self.ebest.stock_prices_by_sector(sector, start_code) rows = response.content self.app.log.info(rows) if self.app.pargs.save: db_adapter.insert_stock_sector_code_map(sector, rows) if (len(rows) < 40): break else: start_code = rows[-1]['shcode'] time.sleep(0.2)
def perform_ponyorm_benchmark(database, conn_str, args, benchmark_result): host, user, password, db = get_metadata_from_conn_str(conn_str) db = Database(database, host=host, user=user, passwd=password, db=db) class Person(db.Entity): name = Required(unicode) addresses = Set("Address") class Address(db.Entity): address = Required(unicode) person = Required(Person) db.generate_mapping(create_tables=True) test_data = test_data_from_args(args) assert test_data if 'ponyorm' not in benchmark_result: benchmark_result['ponyorm'] = dict() if database not in benchmark_result['ponyorm']: benchmark_result['ponyorm'][database] = dict() test_aspects = ['insert', 'read', 'update', 'delete'] __builtin__.__dict__.update(locals()) timeit_funcs = [ '_{0}_{1}_data(test_data, Person, Address, db)'.format( 'ponyorm', test_aspect) for test_aspect in test_aspects ] for index, tf in enumerate(timeit_funcs): rst = timeit.timeit(tf, number=args.num_repeats) benchmark_result['ponyorm'][database][test_aspects[index]] = rst
def save_candle_data(self, rows, code, need_init=False): if need_init: data_conf = self.app.config.get('moontrader', 'data') db_candle = Database() db_adapter.define_korea_stock(db_candle) db_adapter.bind(db_candle, data_conf['dir'], DB_FILE_NAME) db_adapter.init(db_candle) db_adapter.save_stock_candles(rows, code)
def save_candle_data(self, rows, need_init=False): if need_init: data_conf = self.app.config.get('moontrader', 'data') db_dart = Database() db_adapter.define_dart(db_dart) db_adapter.bind(db_dart, data_conf['dir'], DB_FILE_NAME) db_adapter.init(db_dart) db_adapter.save_dart_disclosures(rows)
def test_version_set(): db = Database(provider="sqlite", filename=":memory:") s = Schema(file=db) assert s.version == Version("0") s.version = Version("12.34.56") with db_session: assert s.db.execute("PRAGMA user_version").fetchone()[0] == 123456 assert s.version == Version("12.34.56")
def _bind(db_file: Path, create_tables=False) -> Database: db = Database() _define_entities(db) db.bind(provider="sqlite", filename=str(db_file)) db.generate_mapping(create_tables=create_tables) return db
def model(): _db = Database() class TestModel(_db.Entity, NapMixin): pass _db.bind('sqlite', ':memory:') _db.generate_mapping(create_tables=True) return TestModel
def init_core(args): db = Database() LazyEntityMeta.attach(db, database='CGRdb_config') db.bind('postgres', **args.connection) db.generate_mapping(create_tables=True) with db_session: db.execute('CREATE EXTENSION IF NOT EXISTS intarray') db.execute('CREATE EXTENSION IF NOT EXISTS plpython3u')
def __init__(self): self.connection_manager = ConnectionManager(self) self.plugin_manager = PluginManager(self) self.database_identifier = DatabaseIdentifier(self) self.connection = None self.export = None self.output = None self.db = Database() Core.instance = self
def test_migrations_same_version_is_cancelled(selfn, tmpfile, caplogger): ddb = Database(provider="sqlite", filename=str(tmpfile)) init_onetable(ddb) s1 = Schema(ddb) s1.version = "1.3.4" m = MakeMigrations(tmpfile, Version("1.3.4"), migrations) assert not hasattr(m, "migrator") m(lambda: True, lambda: True) assert "No migration needed" in caplogger.read()
def test_restore_backup(self, tmpfile): ddb = Database(provider="sqlite", filename=str(tmpfile)) init_onetable(ddb) ddb.disconnect() bck_db = tmpfile.read_bytes() m = MakeMigrations(tmpfile, Version("1.3.2"), {"1.3.2": "AZEZRT ERTERT"}) assert not m(lambda x: True, lambda x: True) assert tmpfile.read_bytes() == bck_db
def memory_db(): from mycartable.database import init_database logger.disable("") db = init_database(Database()) add_database_to_types(db) logger.enable("") return db
def main(): db = Database(provider='sqlite', filename=':memory:') appdb.bind_to(db) db.generate_mapping(create_tables=True) with db_session(): john, created = get_or_create(Person, {'name': 'John Swag'}, {'type': PersonType.Student}) print('Hello, {} ({}, created: {})'.format(john.name, john.type, created))
def init_core(args): db = Database() LazyEntityMeta.attach(db, database='CGRdb_config') db.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db.generate_mapping(create_tables=True)
def main_init_database(filename=None, prod=False): # init database first settings = QSettings() logger.info(f"ficher settings : {settings.fileName()}") newdb = Database() create_db = False import mycartable.database if prod: from mycartable.defaults.files_path import ROOT_DATA filename = settings.value("General/ddb_path", ROOT_DATA / "mycartable.ddb") create_db = True else: QStandardPaths.setTestModeEnabled(True) # filename.unlink() filename = Path(tempfile.gettempdir()) / "devddbmdk.sqlite" # filename = "/home/jimmy/Documents/MyCartable/mycartable.ddb" # filename = ":memory:" # filename = ":memory:" create_db = True from mycartable.migrations.migrations import make_migrations if filename != ":memory:" and Path(filename).is_file(): migrate_res = make_migrations(filename) if not migrate_res: from mycartable.defaults.files_path import LOGFILE raise SystemError(f"voir dans {LOGFILE}") mycartable.database.db = newdb db = mycartable.database.init_database(newdb, filename=filename, create_db=create_db) if not prod: from tests.factory import Faker with db_session: db.Configuration.add("annee", 2019) try: f = Faker(db) m = f.f_matiere(groupe=2019) ac = f.f_activite(matiere=m) p = f.f_page(activite=ac) f.f_textSection(page=p) except: pass return mycartable.database.db
def cli(ctx: click.Context, log_level, config_path: Path): set_root_logger_level(log_level) if ctx.invoked_subcommand != 'csv': # Doesn't need to initialise the db with config_path.open('r') as fp: config = json.load(fp) ctx.meta.update(config) db = Database(**config['db']) init_orm(db) ctx.obj = db
def _create_db_schemata(database_file, *schemata): from pony.orm import Database db = Database('sqlite', database_file) models = {} for schema in schemata: # Build db.Entity objects for the side effect of registering them # with the ORM models[schema] = type(schema.__name__, (db.Entity, ), _pony_schema_from_sheets_schema(schema)) db.generate_mapping(create_tables=True) return db, models
class DataFactory: _INT64 = 2 ** 63 - 1 _config = FrozenConfig() _db = Database() _db.bind(provider='postgres', host=_config.postgres.host, port=_config.postgres.port, user=_config.postgres.user, passwd=_config.postgres.password, database='api') class Hitokoto(_db.Entity): """ ORM 中的表结构,这里的变量名即存放进数据库的列名 不能为这个 class 重写 str 或 repr 方法 因为 pony 存放的时候会调用这个方法 """ id = PrimaryKey(int, size=64) length = Required(int) info = Required(Json) origin = Required(str) _db.generate_mapping(create_tables=True) with db_session: _amount = Hitokoto.select(lambda p: p.id).count() @classmethod @db_session def _insert(cls, id_: int, hitokoto: str, source: str, origin: str): try: cls.Hitokoto(id=id_, info=dict(hitokoto=hitokoto, source=source), length=len(hitokoto), origin=origin) commit() except TransactionIntegrityError: has_duplicated = f'已重复({cls._amount}):{id_, hitokoto}' print(reprlib.repr(has_duplicated)) except Exception as e: import traceback traceback.print_exc() else: has_inserted = f'已插入({cls._amount}):{id_, hitokoto}' cls._amount += 1 print(reprlib.repr(has_inserted)) @classmethod def fmt_data(cls, c: Dict[str, str], url: str) -> None: hitokoto_tmp = c.get('hitokoto') or c.get('text') or c.get('HITO') source = c.get('source') or c.get('from') or c.get('SOURCE') hitokoto = sub(r'[\xa0-\xad]', '', hitokoto_tmp) origin = url.split('.')[1] # 去除一些无用字符,避免无用字符对计算 hash 产生的影响 fmt_hitokoto = sub( r'[,,。.“” …!、!?:’;\\‘?「/」—-♬《》⋯『』()]', '', hitokoto) id_ = xxh64(fmt_hitokoto).intdigest() - cls._INT64 source and origin and cls._insert(id_, hitokoto, source, origin)