def init_db_thread():
global app_DB_path
engine = create_engine(u'sqlite:///{0}'.format(app_DB_path), echo=False)
Session = scoped_session(sessionmaker())
Session.configure(bind=engine)
return Session()
def set_session_metadata(session: orm.Session, metadata: dict):
if isinstance(session, orm.Session):
session.info[TEMPORAL_METADATA_KEY] = metadata
elif isinstance(session, orm.sessionmaker):
session.configure(info={TEMPORAL_METADATA_KEY: metadata})
else:
raise ValueError('Invalid session')
def init_schema():
for provider in Provider:
dbs = provider_map_category.get(provider)
if dbs:
for store_category in dbs:
engine = get_db_engine(provider, store_category)
category_map_db.get(store_category).metadata.create_all(engine)
# create index for 'timestamp','security_id','code','report_period
for table_name, table in iter(category_map_db.get(store_category).metadata.tables.items()):
index_list = []
with engine.connect() as con:
rs = con.execute("PRAGMA INDEX_LIST('{}')".format(table_name))
for row in rs:
index_list.append(row[1])
for col in ['timestamp', 'security_id', 'code', 'report_period']:
if col in table.c:
column = eval('table.c.{}'.format(col))
index = schema.Index('{}_{}_index'.format(table_name, col), column)
if index.name not in index_list:
index.create(engine)
for cols in [('timestamp', 'security_id'), ('timestamp', 'code')]:
if (cols[0] in table.c) and (col[1] in table.c):
column0 = eval('table.c.{}'.format(col[0]))
column1 = eval('table.c.{}'.format(col[1]))
index = schema.Index('{}_{}_{}_index'.format(table_name, col[0], col[1]), column0, column1)
if index.name not in index_list:
index.create(engine)
Session = get_db_session_factory(provider, store_category)
Session.configure(bind=engine)
def init_db(app_db_path):
# Open session for database connection
global session
global app_DB_path
app_DB_path = app_db_path
engine = create_engine(u'sqlite:///{0}'.format(app_db_path), echo=False)
Session = scoped_session(sessionmaker())
Session.configure(bind=engine)
session = Session()
if os.path.exists(app_db_path):
Base.metadata.create_all(engine)
migrate_Database(session)
clean_database(session)
else:
Base.metadata.create_all(engine)
create_admin_user(session)
create_anonymous_user(session)
if cli.user_credentials:
username, password = cli.user_credentials.split(':')
user = session.query(User).filter(func.lower(User.nickname) == username.lower()).first()
if user:
user.password = generate_password_hash(password)
if session_commit() == "":
print("Password for user '{}' changed".format(username))
sys.exit(0)
else:
print("Failed changing password")
sys.exit(3)
else:
print("Username '{}' not valid, can't change password".format(username))
sys.exit(3)
def init_schema():
for store_category in StoreCategory:
engine = get_db_engine(store_category)
store_map_base.get(store_category).metadata.create_all(engine)
Session = get_db_session_factory(store_category)
Session.configure(bind=engine)
def use_db():
# Import the DB Models to be created.
from FootballORM.model import my_model
Base.prepare(engine, reflect=True)
Session = sessionmaker(bind=engine)
Session.configure(bind=engine) # once engine is available
session = Session()
return session
def vertical_partition_session_simple():
engine1 = create_engine('postgresql://db1')
engine2 = create_engine('postgresql://db1')
Session = sessionmaker(twophase=True)
# bind User operations to engine 1, Account operations to engine 2
Session.configure(binds={User: engine1, Account: engine2})
return Session()
def init_schema():
for provider in Provider:
dbs = provider_map_category.get(provider)
if dbs:
for store_category in dbs:
engine = get_db_engine(provider, store_category)
category_map_db.get(store_category).metadata.create_all(engine)
Session = get_db_session_factory(provider, store_category)
Session.configure(bind=engine)
def prep():
with resources.path("vatsimlib.data", "vatsim.db") as sqlite_filepath:
engine = create_engine("sqlite:///vatsimdata.db")
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
do_mapping()
def init_db():
# Import the DB Models to be created.
from FootballORM.model import my_model
Base.prepare(engine, reflect=True)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
Session.configure(bind=engine) # once engine is available
session = Session()
session.commit()
def list_state():
"""List all states"""
engine = create_engine('mysql+mysqldb://{}:{}@localhost/{}'.format(
sys.argv[1], sys.argv[2], sys.argv[3]),
pool_pre_ping=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
query = session.query(State).order_by(State.id)
for row in query.all():
print("{}: {}".format(row.id, row.name))
session.close()
def list_state_city():
"""Prints all City objects from DB"""
engine = create_engine('mysql+mysqldb://{}:{}@localhost/{}'.format(
sys.argv[1], sys.argv[2], sys.argv[3]),
pool_pre_ping=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
query = session.query(
State, City).filter(State.id == City.state_id).order_by(City.id)
for row in query:
print("{}: ({}) {}".format(row[0].name, row[1].id, row[1].name))
session.close()
def state_contain():
"""Name state that contain the letter a"""
engine = create_engine('mysql+mysqldb://{}:{}@localhost/{}'.format(
sys.argv[1], sys.argv[2], sys.argv[3]),
pool_pre_ping=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
query = session.query(State).order_by(State.id).filter(
State.name.contains("%a%"))
for row in query:
print("{}: {}".format(row.id, row.name))
session.close()
def update_state():
"""Update a state"""
engine = create_engine('mysql+mysqldb://{}:{}@localhost/{}'.format(
sys.argv[1], sys.argv[2], sys.argv[3]),
pool_pre_ping=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
query = session.query(State).order_by(State.id).filter(State.id == 2)
for row in query:
row.name = "New Mexico"
session.commit()
session.close()
def first_state():
"""Print first state"""
engine = create_engine('mysql+mysqldb://{}:{}@localhost/{}'.format(
sys.argv[1], sys.argv[2], sys.argv[3]),
pool_pre_ping=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
try:
query = session.query(State).order_by(State.id).first()
print("{}: {}".format(query.id, query.name))
except:
print("Nothing")
session.close()
def delete_state():
"""Delete a state"""
engine = create_engine(
'mysql+mysqldb://{}:{}@localhost/{}'.format(sys.argv[1],
sys.argv[2],
sys.argv[3]),
pool_pre_ping=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
query = session.query(State).filter(State.name.contains("%a%"))
query.delete(synchronize_session='fetch')
session.commit()
session.close()
def init_db(app_db_path):
# Open session for database connection
global session
engine = create_engine(u'sqlite:///{0}'.format(app_db_path), echo=False)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
if os.path.exists(app_db_path):
Base.metadata.create_all(engine)
migrate_Database(session)
clean_database(session)
else:
Base.metadata.create_all(engine)
create_admin_user(session)
create_anonymous_user(session)
def get_state():
"""Prints the id of the argument (state)"""
engine = create_engine('mysql+mysqldb://{}:{}@localhost/{}'.format(
sys.argv[1], sys.argv[2], sys.argv[3]),
pool_pre_ping=True)
Base.metadata.create_all(bind=engine)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
query = session.query(State).order_by(State.id)
count = 0
for row in query:
if row.name == sys.argv[4]:
count = row.id
if count != 0:
print(count)
else:
print("Not found")
session.close()
def __init__(self, db_string, json_folder, logger, ref_regions):
if not db_string:
raise ValueError("Не указан путь к БД")
self._db_engine = db.create_engine(db_string)
self._s_json_folder = json_folder
self._logger = logger
self._ref_regions = ref_regions
self._vac_found = []
# Открываем БД
try:
self._db_connection = self._db_engine.connect()
except Exception as ex:
logger.error(
f"Error during establishing connection to PostgreSQL database\n {ex}"
)
raise ex
Session = sessionmaker()
Session.configure(bind=self._db_engine)
self._session = Session()
def create_session(cls, db='gis', engine=None):
"""
Start a session.
:param engine:
:return:
"""
# if a engine in passed used it, else use the one
# at the top of the this module
engine = engine if engine else ENGINE
if engine:
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
else:
# fallback: create a new engine
engine = start_postgre_engine(db, False)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
return session
def create_session(cls, db='gis', engine=None):
"""
Start a session.
:param engine:
:return:
"""
# if a engine in passed used it, else use the one
# at the top of the this module
engine = engine if engine else ENGINE
if engine:
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
else:
# fallback: create a new engine
engine = start_postgre_engine(db, False)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
return session
def __init__(self):
Base = automap_base()
engine = create_engine("sqlite:///sqlcell.db")
Base.prepare(engine, reflect=True)
self.classes = Base.classes
self.tables = Base.metadata.tables.keys()
self.Sqlcell = Base.classes.sqlcell
self.Engines = Base.classes.engines
self.Hooks = Base.classes.hooks
Session = sessionmaker(autoflush=False)
Session.configure(bind=engine)
self.session = Session()
dbs = self.session.query(self.Engines).all()
self.db_info = {}
for row in dbs:
engine = row.engine
if row.db:
self.db_info[row.db] = engine
if row.alias:
self.db_info[row.alias] = engine
self.db_info[engine] = engine
self.db_info[row.host] = engine
# coding: UTF-8 from sqlalchemy.orm import Session, sessionmaker from ..models import engine Session = sessionmaker() Session.configure(bind=engine) session = Session()
def get():
"""
需要分类的id
参数:
type_id
"""
parser = reqparse.RequestParser()
parser.add_argument('type_id', type=str, required=True, help=u'酒吧类型type_id必须。')
args = parser.parse_args()
resp_suc = {}
resp_suc['list'] = []
resp_suc['hot_list'] = []
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
if args['type_id']:
result_count = session.query(PubPicture).\
join(Pub).\
join(PubTypeMid).\
filter(PubTypeMid.pub_type_id == int(args['type_id']), Pub.recommend == 1).\
group_by(PubPicture.pub_id).count()
if result_count > 1:
results = session.query(PubPicture). \
join(Pub). \
join(PubTypeMid).\
filter(PubTypeMid.pub_type_id == int(args['type_id']), Pub.recommend == 1).\
group_by(PubPicture.pub_id)
for result in results:
if result:
result_pic = flatten(result, result)
resp_suc['hot_list'].append(result_pic)
else:
result = session.query(PubPicture). \
join(Pub). \
join(PubTypeMid).\
filter(PubTypeMid.pub_type_id == int(args['type_id']), Pub.recommend == 1).\
group_by(PubPicture.pub_id).first()
if result:
result_pic = flatten(result, result)
resp_suc['hot_list'].append(result_pic)
pub_type_count = PubTypeMid.query.filter(PubTypeMid.pub_type_id == int(args['type_id'])).count()
if pub_type_count > 1:
pub_types = PubTypeMid.query.filter(PubTypeMid.pub_type_id == int(args['type_id']))
for pub_type in pub_types:
pub_count = Pub.query.filter(Pub.id == pub_type.pub_id).count()
if pub_count > 1:
pubs = Pub.query.filter(Pub.id == pub_type.pub_id)
for pub in pubs:
pub_picture = PubPicture.query.filter(PubPicture.pub_id == pub.id).first()
pub_pic = flatten(pub, pub_picture)
resp_suc['list'].append(pub_pic)
else:
pub = Pub.query.filter(Pub.id == pub_type.pub_id).first()
pub_picture = PubPicture.query.filter(PubPicture.pub_id == pub.id).first()
pub_pic = flatten(pub, pub_picture)
resp_suc['list'].append(pub_pic)
else:
pub_type = PubTypeMid.query.filter(PubTypeMid.pub_type_id == int(args['type_id'])).first()
pub = Pub.query.filter(Pub.id == pub_type.pub_id).first()
pub_picture = PubPicture.query.filter(PubPicture.pub_id == pub.id).first()
pub_pic = pickler.flatten(pub)
pub_pic['pic_path'] = pub_picture.rel_path + pub_picture.pic_name
resp_suc['list'].append(pub_pic)
resp_suc['status'] = 0
return resp_suc
else:
resp_suc['message'] = 'error'
resp_suc['status'] = 1
return resp_suc
#!/usr/bin/python
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import sessionmaker, relationship, Session
engine = create_engine("postgresql://*****:*****@127.0.0.1/admssh")
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
Base = declarative_base()
class Usuarios(Base):
__tablename__ = 'usuarios'
id = Column(Integer, primary_key=True)
nome = Column(String)
email = Column(String)
senha = Column(String)
def __init__(self, nome="", email="", senha=""):
self.nome = nome
self.email = email
self.senha = senha
class Servidores(Base):
__tablename__ = 'servidores'
from app.db_definitions import (
Observation,
SeenBird,
KeyBird,
Species,
Hotspot,
# Period,
AnalysisConfig,
HotspotConfig,
)
logging.basicConfig(level=logging.DEBUG)
ENGINE = create_engine("sqlite:///data/vagrant_db.db")
Session = sessionmaker()
Session.configure(bind=ENGINE)
def sp_name_from_index(session: Session, sp_index: int) -> str:
"""Returns the species name associated with the supplied species index in the databse."""
q = session.query(Species).filter(Species.SpIndex == sp_index).limit(1)
return q.first().CommonName
def sp_index_from_name(session: Session, sp_name: str) -> int:
"""Returns the species index associated with the supplied species name in the databse."""
q = session.query(Species).filter(Species.CommonName == sp_name).limit(1)
return q.first().SpIndex
def obs_dict_from_db(session: Session, loc_id: str, period: int) -> dict:
def create_session(engine: Engine) -> Session:
Session = sessionmaker()
Session.configure(bind=engine)
return Session()
