def init_test_database(self): ''' TEST 를 위한 Database 를 메모리 상에 만든다. 물론 Session 도. ''' engine = db.create_engine('sqlite://', convert_unicode=True, encoding='utf-8', echo=False) self.session = sessionmaker(bind=engine, autoflush=True, autocommit=False) Base.metadata.create_all(engine)
def test_loader_inserts_food_element( # noqa clirunner, monkeypatch, tmpdir, listfile): # Mocking for Nurungji-only food list monkeypatch.setattr( koreafood.requests, 'get', lambda _: MockHTTPResponse(listfile.read())) monkeypatch.setattr( koreafood.requests, 'Session', lambda: MockHTTPSession(post=_mock_foodlist_analysis_page)) food = koreafood.get_food_list().next() monkeypatch.setattr( koreafood, 'get_food_list', lambda: [food]) # Trigger SUT command url = 'sqlite:///'+tmpdir.join('new.db').strpath res = clirunner.invoke(loader, [url]) # Test result assert res.exit_code == 0 session = sessionmaker(create_engine(url))() mfood = session.query(koreafood_model.Food).one() assert mfood.name == food.name assert mfood.category_big == food.category_big assert mfood.category_small == food.category_small assert len(mfood.aliments) == 1
def setUp(self): print "setup.." #engine = create_engine('sqlite:///data/test.db', echo=True) engine = create_engine('sqlite://', echo=True) Session = sessionmaker(bind=engine) self.session = Session() ImageModel.metadata.create_all(engine)
def session_cm(): session = sessionmaker(bind=db_engine)() try: yield session except Exception, e: # logger.warn(traceback.format_exc()) raise
def create_session(self, engine=None, shard=False): class_ = QKShardSession if shard else QKSession factory = sessionmaker( db=self, bind=engine, class_=class_, autocommit=False, autoflush=False ) return _scoped_session(factory, scopefunc=self.scopefunc)
def Session_Adapter_Add(data_information): Session = sessionmaker() Session.configure(bind=engine) session = Session() session.add(data_information) session.flush() session.commit()
def migrate_tanks(self, old_tank_list): tank_list = Tag.objects.filter(tag_type=TagType.TANK) tank_title_map = dict([(t.name, t) for t in tank_list]) new_id = Tag.objects.all().aggregate(Max("id"))["id__max"] or 10000 new_id = max(new_id, 10000) session = sessionmaker(bind=SQL_ENGINE)() for old_tank in old_tank_list: if old_tank.title in tank_title_map: LegacyTank.objects.create(id=old_tank.id, tag=tank_title_map[old_tank.title]) continue manga = session.query(OldManga).filter(OldManga.tank_id == old_tank.id).order_by(OldManga.tank_chp)[:] if len(manga) > 0: cover = manga[0].cover else: cover = None new_id += 1 tag = Tag( id=new_id, tag_type=TagType.TANK, name=old_tank.title, slug=old_tank.slug, created_by_id=self.user.id, created_on=old_tank.date_created, cover=self.get_file(cover), ) tag.save(updated_by=None) tank_title_map[old_tank.title] = tag LegacyTank.objects.create(id=old_tank.id, tag=tag)
def __init__(self): ''' Constructor ''' self.base = declarative_base() self.SessionFactory = sessionmaker(bind=self.engine) self.session = self.SessionFactory()
def tile_exists(target_url, bg_color, title, type, image_uri, enhanced_image_uri, locale, conn=None, *args, **kwargs): """ Return the id of a tile having the data provided """ from splice.environment import Environment env = Environment.instance() if conn is not None: sm = sessionmaker(bind=conn) session = sm() else: session = env.db.session # we add order_by in the query although it shouldn't be necessary # this is because of a previous bug where duplicate tiles could be created results = ( session .query(Tile.id) .filter(Tile.target_url == target_url) .filter(Tile.bg_color == bg_color) .filter(Tile.title == title) .filter(Tile.image_uri == image_uri) .filter(Tile.enhanced_image_uri == enhanced_image_uri) .filter(Tile.locale == locale) .order_by(asc(Tile.id)) .first() ) if results: return results[0] return results
def main(keep_saved, config_location): if not config_location: config_location = os.path.dirname(os.path.abspath(__file__)) + "\\config.ini" try: open(config_location) except IOError: print "Problem opening config.ini at " + config_location engine = create_engine(make_connection(config_location), echo=False) Session = sessionmaker(bind=engine) session = Session() Base = declarative_base() class FeverItems(Base): __tablename__ = 'fever_items' id = Column(INTEGER, primary_key=True) description = Column(LONGTEXT) is_saved = Column(TINYINT) records = session.query(FeverItems.description).filter_by(is_saved=1) contents = [] for record in records: contents.append(record) if not keep_saved: session.query(FeverItems).filter_by(is_saved=1).update({"is_saved": 0}) session.commit() return contents if __name__ == "__main__": main(bool(sys.argv[1]), str(sys.argv[2]))
def __init__(self, name, url, scope_func): self._name = name self._url, engine_params = self._parse_url(url) self._engine = sqlalchemy.create_engine(self._url, **engine_params) self._session_maker = sessionmaker(self.engine, class_=Session, expire_on_commit=False) self._scoped_session_maker = scoped_session(self._session_maker, scopefunc=scope_func) self.Model = declarative_base()
def __init__(self, database, config): self.name = database self._config = config url = Database.build_url(database, config) self._engine = sa.create_engine(url) self._metadata = sa.MetaData(bind=self._engine, naming_convention=kNamingConvention) self._sessionmaker = sessionmaker(bind=self._engine)
def __init__(self, db_url, echo=False, drop_all=False): ''' Constructor ''' self._clients = [] self._pending = [] logging.info("connecting to %s",db_url) params = dict(echo=echo) if 'mysql' in db_url: params['encoding']='utf-8' params['pool_recycle']=3600 params['isolation_level']='READ COMMITTED' self._engine = create_engine(db_url, **params) self._Session = sessionmaker(bind=self._engine, extension=self._SESSION_EXTENSIONS_, **self._SESSION_KWARGS_) if drop_all is True: with self.session as session: self._drop_all_(session) self._create_all_() if drop_all is True: with self.session as session: session.add(model.Issue(id=1, name="repeat idea", type = model.Issue.TYPE[0])) session.add(model.Issue(id=2, name="speed", type = model.Issue.TYPE[1])) session.commit()
def __init__(self, engine): ''' Constructor ''' self.engine = engine klass = sessionmaker(bind=self.engine) self.session = klass()
def __init__(self, url, pool_size=sqlalchemy_dao.POOL_DEFAULT): if pool_size == sqlalchemy_dao.POOL_DISABLED: self._engine = engine.create_engine(url, poolclass=NullPool) else: self._engine = engine.create_engine(url, pool_size=pool_size, pool_recycle=3600, max_overflow=sys.maxsize) self._Session = session.sessionmaker(bind=self._engine, class_=self.session_class) # pylint: disable=invalid-name
def add_period(self, period): if isinstance(period, Period): DBSession = sessionmaker(bind=self.engine) session = DBSession() session.add(period) session.commit()
def _create_session(self, engine): Base.metadata.create_all(engine) # Distinguish sessions by thread. session = scoped_session(sessionmaker( extension=ZopeTransactionExtension())) session.configure(bind=engine) return session
def persist_bundle_sensor(self): from madmex.persistence.driver import persist_bundle folder = '/LUSTRE/MADMEX/staging/madmex_antares/test_ingest/556_297_041114_dim_img_spot' from sqlalchemy import create_engine from sqlalchemy.orm.session import sessionmaker from madmex.mapper.bundle.spot5 import Bundle #from madmex.configuration import SETTINGS dummy = Bundle(folder) #dummy.target = '/LUSTRE/MADMEX/staging/' target_url = getattr(SETTINGS, 'TEST_FOLDER') print target_url #TODO please fix me, horrible hack dummy.target = target_url persist_bundle(dummy) my_database = getattr(SETTINGS, 'ANTARES_TEST_DATABASE') klass = sessionmaker(bind=create_engine(my_database)) session = klass() query = 'SELECT count(*) FROM product WHERE uuid=\'%s\';' % dummy.uuid_id try: result_set = session.execute(query) for row in result_set: self.assertGreater(row['count'], 0) session.delete(dummy.get_database_object()) session.commit() for file_name in dummy.get_files(): full_path = os.path.join(target_url, os.path.basename(file_name)) self.assertTrue(os.path.isfile(full_path)) os.remove(full_path) except: session.rollback() raise finally: session.close()
def _create_engine(self, url): echo = self.configuration.get('echo') if echo: echo = 'debug' engine = self.dialect.create_engine(url, self.schema, echo=echo) return engine, sessionmaker(bind=engine)
def write_table(self, table, db_cols, hstore_col_name): logger.debug('pg_copy_writer starting for project %s, table %s' % (self.project,table['name'])) prefix = self.project csvdir = conf.TMP_FILES_DIR csvfilename = '%s-%s.csv' % (prefix, table['name']) csv_writer = CsvPlainWriter(csvdir) try: csv_writer.write_table(table, csvfilename, db_cols, hstore_col_name) except EmptyTableException: logger.info('pg_copy_writer stopping, no rows to write to csv file') return csvfile = os.path.join(csvdir, csvfilename) with open(csvfile, 'r') as csv_file: abspath = os.path.abspath(csv_file.name) headings = csv_file.readline() engine = sqlalchemy.create_engine(conf.SQLALCHEMY_DB_URL) session = sessionmaker(bind=engine) copy_file = open(abspath,'r') copy_sql = "COPY %s (%s) FROM STDIN DELIMITER ',' CSV HEADER" % (table['name'], headings) logger.debug('starting pg_copy') # need to use raw psycopg here to copy from stdin because with RDS not allowing admin users, can't copy from a file. raw_conn = engine.raw_connection() raw_cur = raw_conn.cursor() raw_cur.copy_expert(copy_sql, copy_file) raw_conn.commit() raw_cur.close() raw_conn.close()
def persist_bundle(self): from madmex.persistence.driver import persist_bundle from sqlalchemy import create_engine from sqlalchemy.orm.session import sessionmaker from madmex.util import remove_file dummy = DummyBundle() persist_bundle(dummy) my_database = getattr(SETTINGS, 'ANTARES_TEST_DATABASE') klass = sessionmaker(bind=create_engine(my_database)) session = klass() query = 'SELECT count(*) FROM product WHERE uuid=\'%s\';' % dummy.uuid_id print query try: result_set = session.execute(query) for row in result_set: self.assertGreater(row['count'], 0) # Delete object from database. session.delete(dummy.get_database_object()) session.commit() for file_name in dummy.get_files(): full_path = os.path.join(dummy.get_output_directory(), os.path.basename(file_name)) self.assertTrue(os.path.isfile(full_path)) # Remove file from filesystem. remove_file(full_path) except: session.rollback() raise finally: session.close()
def test_intersecting_genes(self): '''Test retrieving genes intersecting a base-pair position segment.''' # print repr(User.__table__) #@UndefinedVariable Session = sessionmaker(bind=self.engine) session = Session() # I believe all of the following are equivalent statements and all are prepared statements chrom = 1 (start, end) = (62151773, 62516683) expected = [('NM_032027', 'TM2D1'), ('NM_176877', 'INADL')] assert_equal([(gene.name, gene.name2) for gene in session.query(Gene). filter(and_(Gene.chrom == 'chr%s' % (chrom,), greatest(Gene.txStart, start) <= least(Gene.txEnd, end))). order_by(Gene.txStart)], expected) assert_equal([(gene.name, gene.name2) for gene in session.query(Gene). filter('chrom = :chrom and greatest(txStart, :start) <= least(txEnd, :end)'). params(chrom='chr%s' % (chrom,), start=start, end=end). order_by(Gene.txStart)], expected) assert_equal([(gene.name, gene.name2) for gene in self.dao.intersecting_genes(chrom, start, end)], expected) session.close()
def init_database(self): ''' Database 와 여기에 접속할 수 있는 Session 을 만든다. ''' Base.metadata.create_all(self.get_engine()) self.session = sessionmaker(bind=self.get_engine(), autoflush=True, autocommit=False)()
def db_searchreplace(db_name, db_user, db_password, db_host, search, replace ): engine = create_engine("mysql://%s:%s@%s/%s" % (db_user, db_password, db_host, db_name )) #inspector = reflection.Inspector.from_engine(engine) #print inspector.get_table_names() meta = MetaData() meta.bind = engine meta.reflect() Session = sessionmaker(engine) Base = declarative_base(metadata=meta) session = Session() tableClassDict = {} for table_name, table_obj in dict.iteritems(Base.metadata.tables): try: tableClassDict[table_name] = type(str(table_name), (Base,), {'__tablename__': table_name, '__table_args__':{'autoload' : True, 'extend_existing': True} }) # class tempClass(Base): # __tablename__ = table_name # __table_args__ = {'autoload' : True, 'extend_existing': True} # foo_id = Column(Integer, primary_key='temp') for row in session.query(tableClassDict[table_name]).all(): for column in table_obj._columns.keys(): data_to_fix = getattr(row, column) fixed_data = recursive_unserialize_replace( search, replace, data_to_fix, False) setattr(row, column, fixed_data) #print fixed_data except Exception, e: print e
def InitDb(*args, **kwds): """ Create a new DBAPI connection pool. The most common and only required argument is the connection URL. The URL can either be a string or a `sqlalchemy.engine.url.URL`. This method has not return value and needs to be called only once per process. See `sqlalchemy.engine.create_engine`. """ global _Base global _db global _session if len(args) > 0: # inject the foreign key pragma when using SQLite databases to ensure integrity # http://docs.sqlalchemy.org/en/rel_0_8/dialects/sqlite.html#foreign-key-support if (isinstance(args[0], str) and args[0].startswith('sqlite')) or \ (isinstance(args[0], URL) and args[0].get_dialect() == 'sqlite'): @event.listens_for(engine.Engine, "connect") def set_sqlite_pragma(dbapi_connection, _): cursor = dbapi_connection.cursor() cursor.execute("PRAGMA foreign_keys=ON") cursor.close() _db = engine.create_engine(*args, **kwds) _Base.metadata.create_all(_db) _session = session.sessionmaker(bind=_db) logger.debug("DB bound to %s", _db) return None
def _run_indexer(options): logging.info("Starting indexer %s:%s ..." % (options.host, options.port)) # initialize crawler service = WaveformIndexer((options.host, options.port), MyHandler) service.log = logging try: # prepare paths if ',' in options.data: paths = options.data.split(',') else: paths = [options.data] paths = service._prepare_paths(paths) if not paths: return # prepare map file if options.mapping_file: with open(options.mapping_file, 'r') as f: data = f.readlines() mappings = parse_mapping_data(data) logging.info("Parsed %d lines from mapping file %s" % (len(data), options.mapping_file)) else: mappings = {} # create file queue and worker processes manager = multiprocessing.Manager() in_queue = manager.dict() work_queue = manager.list() out_queue = manager.list() log_queue = manager.list() # spawn processes for i in range(options.number_of_cpus): args = (i, in_queue, work_queue, out_queue, log_queue, mappings) p = multiprocessing.Process(target=worker, args=args) p.daemon = True p.start() # connect to database engine = create_engine(options.db_uri, encoding=native_str('utf-8'), convert_unicode=True) metadata = Base.metadata # recreate database if options.drop_database: metadata.drop_all(engine, checkfirst=True) metadata.create_all(engine, checkfirst=True) # initialize database + options _session = sessionmaker(bind=engine) service.session = _session service.options = options service.mappings = mappings # set queues service.input_queue = in_queue service.work_queue = work_queue service.output_queue = out_queue service.log_queue = log_queue service.paths = paths service._reset_walker() service._step_walker() service.serve_forever(options.poll_interval) except KeyboardInterrupt: quit() logging.info("Indexer stopped.")
def setUp(self): engine = create_engine("sqlite:///:memory:", echo=True) Session = sessionmaker(bind=engine) self.session = Session() Base = declarative_base() class Post(Base): __tablename__ = "objectA" __searchable__ = ["title", "body"] id = Column(Integer, primary_key=True) title = Column(Text) body = Column(UnicodeText) created = Column(DateTime, default=datetime.datetime.utcnow()) def __repr__(self): return "{0}(title={1})".format(self.__class__.__name__, self.title) self.Post = Post Base.metadata.create_all(engine) self.index_manager = whooshalchemy.IndexService(session=self.session) self.index_manager.register_class(Post)
def _ensure_database_exists(self): if not os.path.exists(SETTINGS_DIR): os.makedirs(SETTINGS_DIR) self._db_engine = create_engine("sqlite:///{}".format(DATABASE_FILE)) self._db_engine.connect() self._scoped_session = scoped_session(sessionmaker(self._db_engine)) DBModelBase.metadata.create_all(self._db_engine)
def tearDown(self): self.db_session.close() database.db_session = scoped_session( sessionmaker()) # restore original db_session for following test cases Base.metadata.drop_all(bind=self.db_engine)
def get_session(self): if not self.sessionmaker: engine = self.get_db_engine() self.sessionmaker = scoped_session(sessionmaker(bind=engine)) session = self.sessionmaker() session.rollback() return session
def main(): """Runs an infinite loop, calling DB update functions on each iteration. getStaticData is called once, getDynamicData every 5 mins, and getWeatherData every 30 mins""" # add static data (once-off) getStaticData() Session = sessionmaker(bind=engine) session = Session() getStaticData() getCurrentData() getForecastData() session.close() counter = 0 while(True): # New DB session for each iteration Session = sessionmaker(bind=engine) session = Session() getDynamicData() # update weather every 30 minutes if counter % 6 == 0: getWeatherData() if counter % 12 == 0: getForecastData() session.close() counter += 1 print("sleeping now", counter) # 300 seconds - execution time for one iteration (~55s) time.sleep(245)
def get_ip_proxy(): engine = create_engine('mysql+mysqlconnector://' + MYSQL_USER + ':' + MYSQL_PASSWD + '@' + MYSQL_HOST + ':3306/' + MYSQL_DBNAME) DBSession = sessionmaker(bind=engine) session = DBSession() ##进行查询 # item = session.query(ProxyItem).filter(ProxyItem.id=='4').one() items = session.query(ProxyItem).filter(ProxyItem.isvalid=='1') ipList = [] for item in items: ip = {'ip':item.ip, 'port':item.port, 'user_pass':''} ipList.append(ip) session.close() return ipList
def main(argv=sys.argv): if len(argv) < 2: usage(argv) config_uri = argv[1] options = parse_vars(argv[2:]) setup_logging(config_uri) logging.getLogger('sqlalchemy.engine').setLevel(logging.ERROR) settings = get_appsettings(config_uri, options=options) engine = engine_from_config(settings, 'sqlalchemy.') Session = sessionmaker(extension=ZopeTransactionExtension()) # noqa session = Session(bind=engine) configure_es_from_config(settings) with transaction.manager: fill_index(session)
def __init__( self, *, namespace: str = "remoulade-state", encoder: Optional[Encoder] = None, client: Optional[sessionmaker] = None, max_size: int = 2000000, url: Optional[str] = None, ): self.url = url or DEFAULT_POSTGRES_URI super().__init__(namespace=namespace, encoder=encoder) self.client = client or sessionmaker(create_engine(self.url)) self.init_db() self.max_size = max_size self.lock = threading.Lock()
def _get_db_session(self, bind_key=None, access=None, echo=True, **kwargs): db_alias = bind_key or self.__class__._db_alias if db_alias not in _database._connections: engine = self._get_engine(bind_key=bind_key, echo=echo, access=access) self.__class__._connections[db_alias] = scoped_session(sessionmaker( bind=engine, class_=DBSession, expire_on_commit=False, )) connections_ = self.__class__._connections[db_alias] session = connections_(**kwargs) log.debug(u"DB<%s>: initiated as %s <%s>", id(session), db_alias, session.bind.pool.status()) return session
class DBConnectionManager: '''This class handles connection to the database.''' Base = declarative_base() conn_url = os.environ.get('DB_CONN_URL') engine = create_engine(conn_url) Session = sessionmaker(bind=engine) @staticmethod def get_db(): '''Returns a session. :return: The session :rtype: Session ''' return Session()
def __init__(self, url=None, echo=False): self.engine = self.init_engine(url) self.add_engine_pidguard(self.engine) session_factory = sessionmaker(bind=self.engine) Session = scoped_session(session_factory) self.session = session_factory() Base.metadata.create_all(self.engine) self.session.commit()
def _connect_to_db(transcript): """This method will connect to the database using the information contained in the JSON configuration. :param transcript: the Transcript instance :type transcript: Mikado.loci_objects.transcript.Transcript """ transcript.engine = dbutils.connect( transcript.json_conf, transcript.logger) transcript.sessionmaker = sessionmaker() transcript.sessionmaker.configure(bind=transcript.engine) transcript.session = transcript.sessionmaker()
def create_database_session(connection_info, connection_type="sqlite:///", autocommit=False, pool_size=5) -> tuple[scoped_session, Engine]: kwargs = {} if connection_type == "mysql+mysqldb://": kwargs["pool_size"] = pool_size kwargs["pool_pre_ping"] = True kwargs["max_overflow"] = -1 engine = sqlalchemy.create_engine( f'{connection_type}{connection_info}?charset=utf8mb4', **kwargs) session_factory = sessionmaker(bind=engine, autocommit=autocommit) Session = scoped_session(session_factory) return Session, engine
def predict(self, t: str, target_ric: str) -> List[str]: # Connect to Postgres engine = create_engine(self.config.db_uri) SessionMaker = sessionmaker(bind=engine) pg_session = SessionMaker() rics = self.config.rics \ if target_ric in self.config.rics \ else [target_ric] + self.config.rics alignments = load_alignments_from_db(pg_session, rics, t, self.seqtypes) # Write the prediction data self.config.dir_output.mkdir(parents=True, exist_ok=True) dest_alignments = self.config.dir_output / Path('alignment-predict.json') with dest_alignments.open(mode='w') as f: writer = jsonlines.Writer(f) writer.write(alignments.to_dict()) predict_iter = create_dataset(self.config, self.device, self.vocab, rics, self.seqtypes) self.model.eval() batch = next(iter(predict_iter)) times = batch.time tokens = batch.token raw_short_field = stringify_ric_seqtype(Code.N225.value, SeqType.RawShort) latest_vals = [x for x in getattr(batch, raw_short_field).data[:, 0]] raw_long_field = stringify_ric_seqtype(Code.N225.value, SeqType.RawLong) latest_closing_vals = get_latest_closing_vals(batch, raw_long_field, times) loss, pred, attn_weight = self.model(batch, batch.batch_size, tokens, times, self.criterion, Phase.Test) i_eos = self.vocab.stoi[SpecialToken.EOS.value] pred_sents = [remove_bos([self.vocab.itos[i] for i in takeuntil(i_eos, sent)]) for sent in zip(*pred)] return replace_tags_with_vals(pred_sents[0], latest_closing_vals[0], latest_vals[0])
def tile_exists(target_url, bg_color, title, typ, image_uri, enhanced_image_uri, locale, frecent_sites, time_limits, frequency_caps, adgroup_name, explanation, check_inadjacency, channel_id, conn=None, *args, **kwargs): """ Return the id of a tile having the data provided """ from splice.environment import Environment env = Environment.instance() if conn is not None: sm = sessionmaker(bind=conn) session = sm() else: session = env.db.session # we add order_by in the query although it shouldn't be necessary # this is because of a previous bug where duplicate tiles could be created results = ( session .query(Tile.id, Tile.adgroup_id) .filter(Tile.target_url == target_url) .filter(Tile.bg_color == bg_color) .filter(Tile.title == title) .filter(Tile.type == typ) .filter(Tile.image_uri == image_uri) .filter(Tile.enhanced_image_uri == enhanced_image_uri) .filter(Adgroup.locale == locale) .filter(Adgroup.start_date == time_limits.get('start')) .filter(Adgroup.end_date == time_limits.get('end')) .filter(Adgroup.start_date_dt == time_limits.get('start_dt')) .filter(Adgroup.end_date_dt == time_limits.get('end_dt')) .filter(Adgroup.frequency_cap_daily == frequency_caps['daily']) .filter(Adgroup.frequency_cap_total == frequency_caps['total']) .filter(Adgroup.name == adgroup_name) .filter(Adgroup.explanation == explanation) .filter(Adgroup.check_inadjacency == check_inadjacency) .filter(Adgroup.channel_id == channel_id) .join(Adgroup.tiles) .order_by(asc(Tile.id)) ) if results: for tile_id, adgroup_id in results: # now check frecent sites for this tile db_frecents = get_frecent_sites_for_tile(tile_id, conn) if db_frecents == sorted(set(frecent_sites)): return tile_id, adgroup_id return None, None
def main() -> None: output_dir = Path('output') output_dir.mkdir(parents=True, exist_ok=True) engine = create_engine('sqlite://') SessionMaker = sessionmaker(bind=engine) session = SessionMaker() create_tables(engine) result_csv = Path('results/result.csv') results = [] with result_csv.open(mode='r') as f: reader = csv.reader(f, delimiter=',') next(reader) for fields in reader: metric = fields[0] noun = fields[1] verb = fields[2] vote = fields[3] user_id = fields[4] results.append(Result(metric, noun, verb, vote, user_id).to_dict()) session.execute(Result.__table__.insert(), results) with Path('resources/translation.json').open(mode='r') as f: t = json.load(f) ja_rows = calc_means(session) with Path('output/mean-ja.csv').open(mode='w') as ja: ja_writer = csv.writer(ja) ja_writer.writerows(ja_rows) with Path('output/mean-en.csv').open(mode='w') as en: en_writer = csv.writer(en) en_writer.writerows([(t[j[0]], t[j[1].replace('X', '')], j[2], j[3]) for j in ja_rows]) for (pos, metric) in itertools.product(['noun', 'verb'], list(Metric)): ja_rows = sort(session, pos, metric) with Path('output/{}s-sorted-by-{}-ja.csv'.format( pos, metric.value)).open(mode='w') as ja: ja_writer = csv.writer(ja) ja_writer.writerows(ja_rows) with Path('output/{}s-sorted-by-{}-en.csv'.format( pos, metric.value)).open(mode='w') as en: en_writer = csv.writer(en) en_writer.writerows([(t[j[0].replace('X', '')], j[1]) for j in ja_rows])
def __init__(self, **kwargs): self.db_name = kwargs['database'] self.host = kwargs['host'] self.port = int(kwargs.get('port', 5432)) self.username = kwargs['username'] self.password = kwargs['password'] self.schema = kwargs['schema'] self.metadata = None self.engine = None self.session_factory = None self.Base = None url_template = '{db_type}://{user}:{passwd}@{host}/{database}' db_url = url_template.format(db_type='postgresql+psycopg2', user=self.username, passwd=self.password, host=self.host, port=self.port, database=self.db_name) retries = 0 connected = False while not connected and retries < 3: try: self.engine = sqla.create_engine(db_url, echo=False) self.metadata = MetaData(schema=self.schema) self.Base = automap_base(bind=self.engine, metadata=self.metadata) self.Base.prepare(self.engine, reflect=True) self.metadata.reflect(bind=self.engine) self.session_factory = sessionmaker(bind=self.engine, autoflush=False, autocommit=False) connected = True print('### Connected to PostgreSQL DB.', file=sys.stderr) except Exception as err: print(err) print(err.__class__.__name__) print(err.__dict__) time.sleep(1) retries += 1 if not connected: raise Exception( '!!! Unable to connect to PostgreSQL db on host %s at port %s.' % (self.host, self.port))
async def initialize(self): """Initializes database connections by creating the SQLAlchemy engine and session objects.""" # see https://github.com/PyMySQL/PyMySQL/issues/644 # /usr/local/lib/python3.6/dist-packages/pymysql/cursors.py:170: Warning: (1300, "Invalid utf8mb4 character string: '800363'") warnings.filterwarnings(action="ignore", message=".*Invalid utf8mb4 character string.*") if not self.db_url: self.db_url = await self.get_config_value(CONFIG_DB_URL, env="ACE_DB_URL", default=self.db_url) if not self.db_kwargs: self.db_kwargs = await self.get_config_value(CONFIG_DB_KWARGS, default=self.db_kwargs) get_logger().info(f"connecting to {self.db_url} ({self.db_kwargs})") self.engine = create_async_engine(self.db_url, **self.db_kwargs) self.async_session = sessionmaker(self.engine, expire_on_commit=False, class_=AsyncSession) await super().initialize()
def load_folder(path, processor): engine = get_engine() Session = sessionmaker(bind=engine) session = Session() try: for root, dirs, files in os.walk(path): for file in files: if not file == 'README': f = os.path.join(root, file) processor(session, f) session.commit() except: session.rollback() raise finally: session.close()
def loader(url): u"""농식품종합정보시스템 식품 정보(식단명 및 재료)를 DB에 저장합니다. :param URL: 저장할 데이터베이스 URL (ex. mysql://scott@tiger:example.com/dbname) """ engine = create_engine(url) Session = scoped_session(sessionmaker(engine)) sess = Session() Base.metadata.bind = engine Base.metadata.create_all() for food in koreafood.get_food_list(): mfood = koreafood.food_to_model(sess, food) sess.merge(mfood) sess.commit()
def __init__(self, app): self.app = app self.config = app.config try: # Check if we have valid db config self.config.DB.type is True except AttributeError: raise MiyagiDbError( 'No DB config found.' 'Please provide the needed parameters inside the "DB" key in the config file.' ) self.SQLAlchemyBase = SQLAlchemyBase self.db_engine = create_engine(self.config.db_uri, echo=True) self.session_maker = sessionmaker(autoflush=False) self.session_maker.configure(bind=self.db_engine)
def set_session(env_setting='local'): # 2. Load db config file_dir = os.path.dirname(os.path.realpath(__file__)) file_name = "alembic_%s.ini" % env_setting db_config_file = os.path.join(file_dir, file_name) config = ConfigParser.ConfigParser() config.read(db_config_file) # 3. Set SQLAlchemy db engine db_engine = config.get('alembic', 'sqlalchemy.url') # 4. Configure SQLAlchemy session engine = create_engine(db_engine) Session = sessionmaker() Session.configure(bind=engine) return Session()
def books(): if 'key_word' in request.args: key_word = request.args.get("key_word") session = sessionmaker(engine)() books = session.execute(f""" SELECT * FROM "Book" WHERE name LIKE '%{key_word}%' OR author LIKE '%{key_word}%'; """) session.commit() else: books = db.execute("""SELECT * FROM "Book";""") db.commit() return render_template("books_table.html", object_list=books)
def get_rules(): engine = create_engine('mysql+mysqlconnector://' + MYSQL_USER + ':' + MYSQL_PASSWD + '@' + MYSQL_HOST + ':3306/' + MYSQL_DBNAME) DBSession = sessionmaker(bind=engine) session = DBSession() # new_user = User(id='4', name='Huangyi') # session.add(new_user) # session.commit() #session.close() ##进行查询 # item = session.query(ProxyItem).filter(ProxyItem.id=='4').one() item = session.query(ProxyRule)[0] print 'type:', type(item) print 'object:', item.name + item.start_urls + item.sub_link session.close()
def get_states_names(): ''' ''' engine = create_engine( 'postgresql://*****:*****@reddbase.conabio.gob.mx:5432/madmex_database' ) session = sessionmaker(engine)() query = 'SELECT nom_ent FROM vectordata.country_mexico_2012' try: result = session.execute(query) except Exception: LOGGER.error('Not expected error in host insertion.') raise finally: session.close() return result
def __init__(self, inputs): self.connODM = conection().conODM() self.connORM = conection().conORM() self.status = 200 self.msg = "0" self.type = "text/plain" Session = sessionmaker(bind=self.connORM) self.session = Session() if inputs["company"] in self.endpoints: #This just will apply to systems registred on the endpoint handle targetCls=self.endpoints[inputs["company"]]() retVal=getattr(targetCls, "process")(inputs) self.msg= retVal["value"] self.type=retVal["type"] self.status=retVal["status"]
def upgrade(migrate_engine): conn = migrate_engine.connect() Session = sessionmaker(bind=migrate_engine) session = Session() for s in session.query(Study).all(): fg = FieldGroup(name=s.name) session.add(fg) s.field_group = fg session.add(s) upd = fields.update().where(fields.c.study_id == s.id).values(field_group_id=fg.id) conn.execute(upd) session.commit()
def setup(app): global dburl, engine, meta, sess, questions, assignments, assignment_questions, courses, competency app.connect("env-before-read-docs", reset_questions) app.connect("build-finished", finalize_updates) # the `qbank` option is for the QuestionBank # it allows us to populate the database from the question bank # but we don't care about populating chapter and subchapter tables and others # so we commit each question. app.add_config_value("qbank", False, "html") try: dburl = get_dburl() engine = create_engine(dburl, client_encoding="utf8", convert_unicode=True) Session = sessionmaker() engine.connect() Session.configure(bind=engine) sess = Session() except Exception as e: # psycopg2.OperationalError dburl = None engine = None sess = None print(e) print( "Skipping all DB operations because environment variables not set up" ) else: # If no exceptions are raised, then set up the database. meta = MetaData() questions = Table("questions", meta, autoload=True, autoload_with=engine) assignments = Table("assignments", meta, autoload=True, autoload_with=engine) assignment_questions = Table("assignment_questions", meta, autoload=True, autoload_with=engine) courses = Table("courses", meta, autoload=True, autoload_with=engine) competency = Table("competency", meta, autoload=True, autoload_with=engine)
def init_db(db_url: str, clean_open_orders: bool = False) -> None: """ Initializes this module with the given config, registers all known command handlers and starts polling for message updates :param db_url: Database to use :param clean_open_orders: Remove open orders from the database. Useful for dry-run or if all orders have been reset on the exchange. :return: None """ kwargs = {} # Take care of thread ownership if in-memory db if db_url == 'sqlite://': kwargs.update({ 'connect_args': { 'check_same_thread': False }, 'poolclass': StaticPool, 'echo': False, }) try: engine = create_engine(db_url, **kwargs) except NoSuchModuleError: raise OperationalException( f"Given value for db_url: '{db_url}' " f"is no valid database URL! (See {_SQL_DOCS_URL})") # https://docs.sqlalchemy.org/en/13/orm/contextual.html#thread-local-scope # Scoped sessions proxy requests to the appropriate thread-local session. # We should use the scoped_session object - not a seperately initialized version Trade._session = scoped_session( sessionmaker(bind=engine, autoflush=True, autocommit=True)) Trade.query = Trade._session.query_property() Order.query = Trade._session.query_property() PairLock.query = Trade._session.query_property() previous_tables = inspect(engine).get_table_names() _DECL_BASE.metadata.create_all(engine) check_migrate(engine, decl_base=_DECL_BASE, previous_tables=previous_tables) # Clean dry_run DB if the db is not in-memory if clean_open_orders and db_url != 'sqlite://': clean_dry_run_db()
def connect(self, user=None, password=None, host=None, database=None, type=None, driver=None): if self.engine: self._connection.close() self._connection = None self.engine.dispose() self.engine = None user = user or self.config.get("user") password = password or self.config.get("user") database = database or self.config.get("database") host = host or self.config.get("host") type = type or self.config.get("type") driver = driver or self.config.get("driver") pool_size = self.config.get("pool_size", 5) params = { "user": user, "password": password, "database": database, "host": host, "type": type, "driver": driver, } connect_url = "" if user: if not password: connect_url = "{type}+{driver}://{user}@{host}/{database}" else: connect_url = "{type}+{driver}://{user}:{password}@{host}/{database}" else: connect_url = "{type}+{driver}://{host}/{database}" self.engine = create_engine(connect_url.format(**params), encoding='utf8', pool_size=pool_size) if self.config.get("use_threadlocal", False): self.engine.pool._use_threadlocal = True self._connection = self.engine.connect() self._connection.execute("COMMIT") self.Session = sessionmaker(bind=self.engine)
def __init__(self, **kwargs): self.host = kwargs['host'] self.db_name = kwargs['database'] self.port = kwargs['port'] self.username = kwargs['username'] self.schema = kwargs['schema'] password = kwargs['password'] self.metadata = None self.engine = None self.session_factory = None self.Base = None url_template = '{db_type}://{user}:{passwd}@{host}:{port}/{database}' db_url = url_template.format(db_type='redshift+psycopg2', user=self.username, passwd=password, host=self.host, port=self.port, database=self.db_name) retries = 0 connected = False while not connected and retries < 3: try: print('### Connecting to Redshift DB...', file=sys.stderr) self.engine = sqla.create_engine(db_url) #self.Base.prepare(self.engine, reflect=True) self.session_factory = sessionmaker(bind=self.engine, autoflush=False, autocommit=False) print('### created session factory', file=sys.stderr) connected = True print('### Connected to Redshift DB.', file=sys.stderr) except Exception as err: print(err, file=sys.stderr) print(err.__class__.__name__, file=sys.stderr) print(err.__dict__, file=sys.stderr) time.sleep(1) retries += 1 if not connected: raise Exception( '!!! Unable to connect to Redshift db on host %s at port %s.' % (self.host, self.port))
def create_session(): """.""" Session = sessionmaker() if DB_TYPE == 'sqlite': engine = create_engine('sqlite:///{}'.format(main_db_details['path'])) elif DB_TYPE == 'mysql': engine = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format( main_db_details['username'], main_db_details['password'], main_db_details['host'], main_db_details['port'], main_db_details['name'])) Session.configure(bind=engine) return Session()
def initialize_database_seed_values(database_connection_string): ''' Performs any application-specific data initialization that must be run on application startup vs during database migration. Parameters ---------- database_connection_string: string The connection string that will be used to connect to the database and perform any runtime seeding. ''' Session = sessionmaker() engine = create_engine(database_connection_string) Session.configure(bind=engine) session = Session() _populate_configuration_table(session)
def init_scibot(database): dburi = config.dbUri(user='******', database=database) #dburi = dbUri('postgres') engine = create_engine(dburi) init(engine, should_create=True, authority='scicrunch') Session = sessionmaker() Session.configure(bind=engine) session = Session() file = Path(__file__).parent / '../sql/permissions.sql' with open(file.as_posix(), 'rt') as f: sql = f.read() #args = dict(database=database) # FIXME XXX evil replace sql_icky = sql.replace(':database', f'"{database}"') session.execute(sql_icky) session.commit()