def create_session(autocommit=False, autoflush=False, pool_recycle=3600):
'''
Create a new MySQL/PostgreSQL session.
Parameters
----------
autocommit : Boolean (False), see sqlalchemy documentation.
autoflush : Boolean (False), see sqlalchemy documentation.
pool_recycle : Integer (7200), causes the pool to recycle connections
after the given number of seconds has passed.
Returns
-------
session : Database session.
'''
engine = _create_engine(conf.get('DB', 'URI'),
convert_unicode=True,
pool_recycle=pool_recycle)
session = _scoped_session(
_sessionmaker(autocommit=autocommit, autoflush=autoflush, bind=engine))
# The following dumb assignments on session are just to trick pylint
# so that it doesn't report "missing attribute" every time the session is used
session.add = session.add
session.commit = session.commit
session.delete = session.delete
session.flush = session.flush
session.get_bind = session.get_bind
session.query = session.query
session.refresh = session.refresh
session.rollback = session.rollback
return session
def _prepare_engine_and_session(db_uri):
if _os.path.isfile(db_uri) or ":" not in db_uri:
db_uri = "sqlite:///" + db_uri
engine = _create_engine(db_uri)
# reflect the tables (don't manually redefine scheme)
if "description" not in Base.classes:
Base.prepare(engine, reflect=True)
session = _Session(engine)
return session
def create_test_engine() -> Engine:
return _create_engine(
f"postgresql+psycopg2://{os.getenv('POSTGRES_USER')}:"
f"{os.getenv('POSTGRES_PASSWORD')}@"
f"{os.getenv('POSTGRES_SERVER')}:"
f"{os.getenv('POSTGRES_PORT')}/"
f"{os.getenv('POSTGRES_DB')}",
future=True,
echo=True,
)
def connect(path=_config.DB_PATH):
global _engine, _session
if _engine != None: return _engine
_engine = _create_engine('sqlite:///%s' % path)
_session = _sessionmaker()
_session.configure(bind=_engine)
return _engine
def __init__(self, *args, **kwargs):
engine = _create_engine(*args, **kwargs)
self._engine = _construct_engine(engine)
self._metadata = _MetaData()
self._metadata.bind = engine
self._metadata.reflect(engine)
self._tables = {}
self.autoremove_columns = False
self.autoremove_tables = False
def create_engine(db_info, **kwargs):
"""Creates :mod:`sqlalchemy` engine.
"""
if isinstance(db_info, basestring):
db_info = {
'DB': 'sqlite',
'NAME': db_info,
}
db_url = engine_url(db_info)
if not kwargs.has_key('convert_unicode'):
kwargs['convert_unicode'] = True
return _create_engine(db_url, **kwargs)
def create_engine(db_info, **kwargs):
"""Creates :mod:`sqlalchemy` engine.
"""
if isinstance(db_info, basestring):
db_info = {
'DB':'sqlite',
'NAME':db_info,
}
db_url = engine_url(db_info)
if not kwargs.has_key('convert_unicode'):
kwargs['convert_unicode'] = True
return _create_engine(db_url, **kwargs)
def __init__(self, loc=None, debug=False):
"""Attach to a database, create if does not exist."""
if not loc:
loc = self.location
kwargs = {'echo': True} if debug else {'echo': False}
if _os.path.isfile(db_config):
print('Using configured db')
self.location = 'mysql'
db_info = json.load(db_config)
self.engine = _create_engine(
'mysql://{}:{}@{}'.format(db_info.user, db_info.passwd,
db_info.host), **kwargs)
else:
print('Using sqlite')
self.location = _os.path.abspath(location)
self.engine = _create_engine('sqlite:///{}'.format(self.location),
**kwargs)
if not _os.path.isfile(self.location):
self.create_database()
self.columns = list(T.RiskAllele.__table__.columns.keys())
def create_engine() -> Engine:
global _engine
if testing or _engine is None:
uri = settings.database_uri
if testing:
uri = settings.testing_database_uri
elif ingest:
uri = settings.ingest_database_uri
_engine = _create_engine(uri, json_serializer=json_serializer)
return _engine
def create_engine(username,
password,
host,
port,
database,
drivername='postgres',
echo=True,
**kwargs):
db_url = URL(
drivername=drivername,
username=username,
password=password,
host=host,
port=port,
database=database,
query=kwargs,
)
return _create_engine(db_url, echo=echo)
def get_session(echo=False):
"""Return a session and engine, uses config file.
Args:
echo: Echo all SQL to the console.
Returns:
session, engine: A SQLAlchemy session and engine object corresponding
to the grasp database for use in querying.
"""
db_type = _config['DEFAULT']['DatabaseType']
if db_type == 'sqlite':
engine_string = ('sqlite:///{db_file}'
.format(db_file=_config['sqlite']['DatabaseFile']))
else:
engine_string = ('{type}://{user}:{passwd}@{host}/grasp'
.format(type=db_type,
user=_config['other']['DatabaseUser'],
passwd=_config['other']['DatabasePass'],
host=_config['other']['DatabaseHost']))
engine = _create_engine(engine_string, echo=echo)
Session = _sessionmaker(bind=engine)
return Session(), engine
def connect2db(uri, echo=False):
"""Connects to an existing DB or creates a new empty DB to connect too.
The DB has to be mappable by the results package.
Parameters
----------
URI : an URI used to connect to a DB. By default set to:
'sqlite:///$USER_HOME/foo.db'.
name : the name of the existing, or newly created, DB. Default: 'foo.db'
echo : verbosity of the DB. False by default.
"""
global Session, engine
# create the engine that hooks to an existing or creates a new DB
engine = _create_engine(uri, echo=echo)
if uri[:5] == "sqlite":
engine.execute("PRAGMA FOREIGN_KEYS=ON")
# map to existing or create new tables in the DB
Base.metadata.create_all(engine)
# create a Session object so transactions can be made
Session = _sessionmaker(bind=engine)
def create_engine(self):
self.engine = _create_engine("mysql+pymysql://{}:{}@{}/{}".format(
self.user, self.pw, self.server, self.database))
def create_engine(self):
self.engine = _create_engine('impala://', creator=self._conn)
def create_engine(self):
self.engine = _create_engine('oracle://' + self.user + ':' + self.pw +
'@' + self._dsn)
def create_engine(self):
self.engine = _create_engine("mssql+pyodbc:///?odbc_connect=%s" %
self._params)
from flask import Flask from flask_sqlalchemy import SQLAlchemy #from ..models import conn conn = 'mssql+pymssql://sa:[email protected]/ZOUHUI-Dev' #print(conn) # create our little application :) app = Flask(__name__) db = SQLAlchemy(app) from sqlalchemy import create_engine as _create_engine import pymssql as _pymssql try: _engine = _create_engine(conn, echo=False, max_overflow=10, pool_size=5) print('Database engine created.') except Exception as e: print(str(e)) _sys.exit() # initialize Base from sqlalchemy.ext.declarative import declarative_base as _declarative_base Base = _declarative_base() # define session scope from contextlib import contextmanager as _contextmanager from sqlalchemy.orm import scoped_session as _scoped_session from sqlalchemy.orm import sessionmaker as _sessionmaker Session = _sessionmaker() Session.configure(bind=_engine)
def create_engine(options=DEFAULT_OPTIONS, echo=False):
connect_string =\
'{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}'\
.format(**options)
return _create_engine(connect_string, echo=echo)
def init_sqlalchemy(base_address):
global Session
db_engine = _create_engine(base_address)
Base.metadata.create_all(db_engine)
Session = _sessionmaker(bind=db_engine)
def create_engine():
"""Creates a new database engine.
The engine is """
return _create_engine(web.config.DATABASE_URL, convert_unicode=True)
def create_engine(url, echo=False):
engine = _create_engine(url, echo=echo)
metadata.create_all(engine)
engine.create_session = sessionmaker(bind=engine)
return engine
def __init__(self, subdirectory_ = None, file_stem = None):
if self._engine is None:
sqlite_url = SqliteUrl(subdirectory_, file_stem)
assert isUnicode(sqlite_url())
self._engine = _create_engine(sqlite_url(), echo=False)
def create_engine(options=DEFAULT_OPTIONS, echo=False):
connect_string =\
'{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}'\
.format(**options)
return _create_engine(connect_string, echo=echo)
def create_engine():
'''Creates a new database engine.'''
return _create_engine(web.config.DATABASE_URL, convert_unicode=True, echo=True)
def create_engine():
'''Creates a new database engine.'''
return _create_engine(web.config.DATABASE_URL, convert_unicode=True)
to right, i.e., scans have many annotations and annotations
have many contours.
For more information, see the model classes themselves.
"""
__version__ = 0.1
# Hidden stuff.
import os as _os
from sqlalchemy import create_engine as _create_engine
from sqlalchemy.orm import sessionmaker as _sessionmaker
from ._Configuration import _Configuration
_module_path = _os.path.dirname(_os.path.abspath(__file__))
_path = _os.path.join(_module_path,'db','pylidc.sqlite')
_engine = _create_engine('sqlite:///'+_path)
_session = _sessionmaker(bind=_engine)()
# Public stuff.
from .Scan import Scan
from .Annotation import Annotation
from .Annotation import _all_characteristics_
from .Contour import Contour
def query(*args):
"""
Wraps the sqlalchemy session object. Some example usage:
>>> import pylidc as pl
>>> qu = pl.query(pl.Scan).filter(pl.Scan.resolution_z <= 1.)
>>> print qu.count()
have many contours.
For more information, see the model classes themselves.
"""
from __future__ import print_function as _pf
__version__ = '0.1.2'
# Hidden stuff.
import os as _os
import pkg_resources as _pr
from sqlalchemy import create_engine as _create_engine
from sqlalchemy.orm import sessionmaker as _sessionmaker
_dbpath = _pr.resource_filename('pylidc', 'pylidc.sqlite')
_engine = _create_engine('sqlite:///' + _dbpath)
_session = _sessionmaker(bind=_engine)()
# Public stuff.
from .Scan import Scan
from .Scan import dicompath
from .Annotation import Annotation
from .Contour import Contour
def query(*args):
"""
Wraps the sqlalchemy session object. Some example usage:
>>> import pylidc as pl
>>> qu = pl.query(pl.Scan).filter(pl.Scan.slice_thickness <= 1.)
return user_directory
def _getSqliteUrl():
import os.path
sqlite_path = os.path.join(getUserDirectory(), "obomb.db")
info(sqlite_path)
sqlite_path_slash = sqlite_path.replace(os.path.sep, '/')
info(sqlite_path_slash)
sqlite_url = "sqlite:///" + sqlite_path_slash
info(sqlite_url)
return sqlite_url
#from logging import debug, info, warn, error, critical, exception
from unittest import TestCase, main
from sqlalchemy import create_engine as _create_engine
engine = _create_engine(_getSqliteUrl(), echo=False)
from sqlalchemy.orm.session import sessionmaker as _sessionmaker
Session = _sessionmaker(bind=engine, autocommit=False)
PersistentSession = _sessionmaker(bind=engine, autocommit=True)
#_metadata = MetaData()
from datetime import datetime as _datetime
from dateutil.tz import tzutc
def utcnow():
dt = _datetime.utcnow();
assert isinstance(dt, _datetime)
assert dt.tzinfo is None
dt2 = dt.replace(tzinfo=tzutc())
return dt2
class _TestConfig(TestCase):
def setUp(self):
