Python orm.sessionmaker函数代码示例

def convert_all():
    """
    Convert all images from SQLite to MySQL
    :return:
    """

    # setup engines to each database
    mysql_engine = create_engine('mysql+pymysql://root:[email protected]:3306/plane_viewer')
    sqlite_engine = create_engine('sqlite:///images.sqlite3')

    # setup session for each database
    mysqlsessionamaker = sessionmaker(bind=mysql_engine)
    sqlitesessionmaker = sessionmaker(bind=sqlite_engine)

    mysql_session = mysqlsessionamaker()
    sqlite_session = sqlitesessionmaker()

    # retrieve all images from SQLite
    sqlite_images = sqlite_session.query(SqlitePlaneImage).all()

    # convert all images from SQLite to MySQL and attempt to load them, ignoring duiplicates
    for image in sqlite_images:
        mysql_image = convert_image_object(image)

        mysql_session.add(mysql_image)
        try:
            mysql_session.commit()
            print('Loaded', image)
        except exc.IntegrityError:
            mysql_session.rollback()
            print("Image {} already loaded".format(image))

    mysql_images = mysql_session.query(MySqlPlaneImage).all()
    print(len(mysql_images))

def edit_category(category_id):
    if request.method == "GET":
        DBSession = sessionmaker(bind=engine)
        session = DBSession()
        try:
            result = session.query(Category).filter_by(id=category_id).one()
            return render_template("edit_category.html", category=result)
        except NoResultFound:
            abort(404)
        finally:
            session.close()
    elif request.method == "POST":
        DBSession = sessionmaker(bind=engine)
        session = DBSession()
        try:
            result = session.query(Category).filter_by(id=category_id).one()
            result.name = request.form["name"]
            result.description = request.form["description"]
            session.add(result)
            session.commit()
            return redirect(url_for("get_category", category_id=category_id), code=302)
        except NoResultFound:
            abort(404)
        finally:
            session.close()

        return "test"
    else:
        abort(400)

 def init_sqlalchemy(self, scheme, connection):
     try:
         import sqlalchemy
         from sqlalchemy import create_engine, MetaData
         from sqlalchemy.orm import scoped_session, sessionmaker
         from torweb.db import CacheQuery
         import _mysql_exceptions
         from sqlalchemy import event
         from sqlalchemy.exc import DisconnectionError
         def my_on_checkout(dbapi_conn, connection_rec, connection_proxy):
             try:
                 dbapi_conn.cursor().execute('select now()')
             except _mysql_exceptions.OperationalError:
                 raise DisconnectionError
         
         engine = create_engine(
             connection,
             convert_unicode=True,
             encoding="utf-8",
             pool_recycle=3600*7,
             #echo_pool=True,
             echo=False,
         )
         event.listen(engine, 'checkout', my_on_checkout)
         metadata = MetaData(bind=engine)
         session = scoped_session(sessionmaker(bind=engine, query_cls=CacheQuery))
         sqlalchemy_sessions = [session]
         DB_Session = sessionmaker(bind=engine)
         return {"metadata":metadata, "session":session, "sqlalchemy_sessions":sqlalchemy_sessions}
         #setattr(self.app, 'metadata', metadata)
         #setattr(self.app, scheme.get('sqlalchemy', 'session'), session)
         #setattr(self.app, 'sqlalchemy_sessions', sqlalchemy_sessions)
     except Exception as e:
         print e

 def test_all_htmlpopups(self):
     from chsdi.models import models_from_name
     from chsdi.models.bod import LayersConfig
     from sqlalchemy import distinct
     from sqlalchemy.orm import scoped_session, sessionmaker
     val = True
     DBSession = scoped_session(sessionmaker())
     query = DBSession.query(distinct(LayersConfig.idBod)).filter(LayersConfig.queryable == val).filter(LayersConfig.staging == 'prod')
     # Get a list of all the queryable layers
     layers = [q[0] for q in query]
     DBSession.close()
     # Get a list of feature ids
     features = []
     for layer in layers:
         try:
             model = models_from_name(layer)[0]
             DBSession = scoped_session(sessionmaker())
             query = DBSession.query(model.primary_key_column()).limit(1)
             ID = [q[0] for q in query]
             # If tables are empty ID is an empty list
             if ID:
                 features.append((layer, str(ID[0])))
             DBSession.close()
         except Exception as e:
             print e
         finally:
             DBSession.close()
     for f in features:
         for lang in ('de', 'fr', 'it', 'rm', 'en'):
             self.testapp.get('/rest/services/all/MapServer/%s/%s/htmlPopup' % (f[0], f[1]), params={'callback': 'cb', 'lang': '%s' % lang}, status=200)

    def test_all_htmlpopups(self):
        from chsdi.models import models_from_name
        from chsdi.models.bod import LayersConfig
        from sqlalchemy import distinct
        from sqlalchemy.orm import scoped_session, sessionmaker
        val = True
        DBSession = scoped_session(sessionmaker())
        valnone = None
        query = DBSession.query(distinct(LayersConfig.layerBodId)).filter(LayersConfig.staging == 'prod').filter(LayersConfig.queryable == val).filter(LayersConfig.parentLayerId == valnone)
        features = []
        try:
            for layer in getLayers(query):
                try:
                    FeatDBSession = scoped_session(sessionmaker())
                    models = models_from_name(layer)
                    self.failUnless(models is not None and len(models) > 0, layer)
                    model = models[0]
                    query = FeatDBSession.query(model.primary_key_column()).limit(1)
                    ID = [q[0] for q in query]
                    if ID:
                        features.append((layer, str(ID[0])))
                finally:
                    FeatDBSession.close()
        finally:
            DBSession.close()

        for f in features:
            for lang in ('de', 'fr', 'it', 'rm', 'en'):
                link = '/rest/services/all/MapServer/' + f[0] + '/' + f[1] + '/htmlPopup?callback=cb&lang=' + lang
                resp = self.testapp.get(link)
                self.failUnless(resp.status_int == 200, link)

  def setup_database(self):
    # DB library imports
    from sqlalchemy import (create_engine, Table, MetaData, Column, Integer, 
                            String, Unicode, Text, UnicodeText, Date, Numeric, 
                            Time, Float, DateTime, Interval, Binary, Boolean, 
                            PickleType)
    from sqlalchemy.orm import sessionmaker, mapper
    # Create global name mappings for model()
    global column_mapping
    column_mapping = {
              'string': String,       'str': String,
             'integer': Integer,      'int': Integer, 
             'unicode': Unicode,     'text': Text,
         'unicodetext': UnicodeText, 'date': Date,
             'numeric': Numeric,     'time': Time,
               'float': Float,   'datetime': DateTime,
            'interval': Interval,  'binary': Binary,
             'boolean': Boolean,     'bool': Boolean,
          'pickletype': PickleType,
    }
 
    # Add a few SQLAlchemy types to globals() so we can use them in models
    globals().update({'Column': Column, 'Table': Table, 'Integer': Integer,
                      'MetaData': MetaData, 'mapper': mapper})
    # Ensures correct slash number for sqlite
    if self.config['db_type'] == 'sqlite':
      self.config['db_location'] = '/' + self.config['db_location']
      eng_name = self.config['db_type'] + '://' + self.config['db_location']
      self.config['db_engine'] = create_engine(eng_name)
      self.config['db_session'] = sessionmaker(bind=self.config['db_engine'])()
    elif self.config['db_type'] == 'postgres':
      eng_name = self.config['db_type'] + '://' + self.config['db_location']
      self.config['db_engine'] = create_engine(eng_name, encoding="utf8", convert_unicode=True)
      self.config['db_session'] = sessionmaker(bind=self.config['db_engine'])()

    def __init__(self, dbname='xraydata.db', read_only=True):
        "connect to an existing database"
        if not os.path.exists(dbname):
            parent, _ = os.path.split(__file__)
            dbname = os.path.join(parent, dbname)
            if not os.path.exists(dbname):
                raise IOError("Database '%s' not found!" % dbname)

        if not isxrayDB(dbname):
            raise ValueError("'%s' is not a valid X-ray Database file!" % dbname)

        self.dbname = dbname
        self.engine = make_engine(dbname)
        self.conn = self.engine.connect()
        kwargs = {}
        if read_only:
            kwargs = {'autoflush': True, 'autocommit':False}
            def readonly_flush(*args, **kwargs):
                return
            self.session = sessionmaker(bind=self.engine, **kwargs)()
            self.session.flush = readonly_flush
        else:
            self.session = sessionmaker(bind=self.engine, **kwargs)()

        self.metadata = MetaData(self.engine)
        self.metadata.reflect()
        self.tables = self.metadata.tables

        self.atomic_symbols = [e.element for e in self.tables['elements'].select(
            ).execute().fetchall()]

    def __init__(self, name=None):
        if not name:
            name = 'default'
        data = {}
        if hasattr(settings, 'DATABASES') and \
           name in settings.DATABASES:
            data = settings.DATABASES[name]
        if data.get('ENGINE', '') == '':
            raise ImproperlyConfigured('''\
The database ORM connection requires, at minimum, an engine type.''')
        if '.' in data['ENGINE']:
            data['ENGINE'] = data['ENGINE'].rsplit('.', 1)[-1]

        # django needs sqlite3 but sqlalchemy references sqlite
        if data['ENGINE'] == 'sqlite3':
            data['ENGINE'] = 'sqlite'

        self.engine = self._create_engine(data)
        ro_values = dict([(k[9:], v) for k, v in data.iteritems()
                             if k.startswith('READONLY_')])
        if len(ro_values):
            ro_data = dict(data)
            ro_data.update(ro_values)
            self.readonly_engine = self._create_engine(ro_data)
        self.metadata = MetaData(self.engine)
        self.Base = declarative_base(metadata=self.metadata)
        if hasattr(self, 'readonly_engine'):
            self._readonly_sessionmaker = \
                scoped_session(sessionmaker(bind=self.readonly_engine))
        self._sessionmaker = scoped_session(sessionmaker(bind=self.engine))

 def __init__(self, dburi):
     self.engine = create_engine(dburi, echo=False, pool_recycle=3600, echo_pool=True,
                                 connect_args={'check_same_thread':False})
     Session = sessionmaker(bind=self.engine)
     self.session = Session()
     Base.metadata.create_all(self.engine, checkfirst=True)
     self.session_factory = sessionmaker(bind=self.engine)

def create_session():
    # cloud_usage
    engine_csu = create_engine(config.get('main', 'cloud_usage_uri'))
    metadata_csu = MetaData(engine_csu)
    account_csu = Table('account', metadata_csu, autoload=True)
    mapper(AccountCsU, account_csu)
    usage_event_csu = Table('usage_event', metadata_csu, autoload=True)
    mapper(UsageEventCsU, usage_event_csu)
    cloud_usage_csu = Table('cloud_usage', metadata_csu, autoload=True)
    mapper(CloudUsageCsU, cloud_usage_csu)
    # cloud database
    engine_cs = create_engine(config.get('main', 'cloud_uri'))
    metadata_cs = MetaData(engine_cs)
    account_cs = Table('account', metadata_cs, autoload=True)
    mapper(AccountCs, account_cs)
    disk_offering_cs = Table('disk_offering', metadata_cs, autoload=True)
    mapper(DiskOfferingCs, disk_offering_cs)
    template_cs = Table('vm_template', metadata_cs, autoload=True)
    mapper(TemplateCs, template_cs)
    guest_os_cs = Table('guest_os', metadata_cs, autoload=True)
    mapper(GuestOSCs, guest_os_cs)

    SessionCs = sessionmaker(bind=engine_cs)
    session_cs = SessionCs()
    SessionCsU = sessionmaker(bind=engine_csu)
    session_csu = SessionCsU()

    # setup our dedicated model
    engine = create_engine(config.get('main', 'rbc_usage_uri'))
    Session = sessionmaker(bind=engine)
    session = Session()
    return (session_csu, session_cs, session)

def copy_stations_to_sqlite(src_dsn, dest_dsn):
    src_sesh = sessionmaker(bind=create_engine(src_dsn))()
    dest_sesh = sessionmaker(bind=create_engine(dest_dsn))()

    net = Network(name='MoTIe')
    dest_sesh.add(net)
    dest_sesh.flush()

    q = src_sesh.query(Station).join(History).join(
        Network).filter(Network.name == 'MoTIe')
    for stn in q.all():
        histories = [History(station_name=hist.station_name)
                     for hist in stn.histories]
        new_obj = Station(native_id=stn.native_id,
                          network_id=net.id, histories=histories)
        dest_sesh.add(new_obj)
    dest_sesh.commit()

    q = src_sesh.query(Variable).join(Network).filter(Network.name == 'MoTIe')
    for var in q.all():
        v = Variable(name=var.name, standard_name=var.standard_name,
                     cell_method=var.cell_method, network_id=net.id,
                     unit=var.unit)
        dest_sesh.add(v)
    dest_sesh.commit()

def init_sqlalchemy(settings):
    # master
    master_url = settings['sqlalchemy.url']
    connect_kwargs = settings.get('sqlalchemy.connect_kwargs')
    kwargs = {}
    if connect_kwargs is not None:
        if isinstance(connect_kwargs, str):
            connect_kwargs = json.loads(connect_kwargs)
        for k, v in connect_kwargs.items():
            kwargs[k] = v
    engine = create_engine(master_url, **kwargs)

    sm = orm.sessionmaker(bind=engine, extension=ZopeTransactionExtension())
    meta.Session = orm.scoped_session(sm)
    meta.metadata.bind = engine

    # slaves
    slaves_url = settings.get('sqlalchemy.slaves', [])
    slaves = []
    for url in slaves_url:
        slave = create_engine(url, **kwargs)
        sm = orm.sessionmaker(bind=slave, extension=ZopeTransactionExtension())
        slaves.append(orm.scoped_session(sm))

    if slaves:
        slave = random.choice(slaves)
        meta.BaseObject.query = slave.query_property(orm.Query)
    else:
        meta.BaseObject.query = meta.Session.query_property(orm.Query)

def initialize_sql(settings):

    global DBSession
    global sqlalchemy_url
    global DBSession
    global Base
    global engine
    global Session
    global session
    global tmp_dir
    global extended

    sqlalchemy_url = settings['sqlalchemy.url']
    DBSession = scoped_session(sessionmaker())
    Base = declarative_base()
    engine = create_engine(sqlalchemy_url)
    Session = sessionmaker(bind=engine)
    session = Session()
    tmp_dir = settings['tmp_dir']

    # Extended attributes
    if settings['extended'] == 'true':
        extended = True
    else:
        extended = False

    DBSession.configure(bind=engine)
    Base.metadata.bind = engine

    def test_merge_load(self):
        Parent = self.classes.Parent

        sess = sessionmaker()()
        sess2 = sessionmaker()()
        p1 = sess.query(Parent).get(1)
        p1.children

        # preloading of collection took this down from 1728 to 1192
        # using sqlite3 the C extension took it back up to approx. 1257
        # (py2.6)

        @profiling.function_call_count(variance=0.10,
                                versions={'2.5':1050, '2.6':1050,
                                        '2.6+cextension':1005, 
                                        '2.7':1005,
                                        '3':1050}
                            )
        def go():
            p2 = sess2.merge(p1)
        go()

        # one more time, count the SQL

        sess2 = sessionmaker(testing.db)()
        self.assert_sql_count(testing.db, go, 2)

def get_data():
    engine = db_connect()
    create_deals_table(engine)
    Session = sessionmaker(bind=engine)
    sess = Session()
    if request.method == 'POST':
        search_code = request.form['naics']
        if not search_code:
            rfps_nasa = sess.query(RFP).all()
        else:
            rfps_nasa = sess.query(RFP).filter(RFP.NAICS_code == search_code).all()
    else:
        rfps_nasa = sess.query(RFP).all()

    engine = db_connect()
    create_deals_table(engine)
    Session = sessionmaker(bind=engine)
    sess = Session()
    if request.method == 'POST':
        search_code = request.form['naics']
        if not search_code:
            rfps_fbo = OpportunityDetail.query.all()
        else:
            code = NAICSChild.query.filter_by(code=search_code).first()
            rfps_fbo = OpportunityDetail.query.filter_by(naics_code=code)\
                .all()
    else:
        rfps_fbo = OpportunityDetail.query.all()

    res = []
    for r in rfps_nasa:
        res.append(r.to_json())
    for r in rfps_fbo:
        res.append(r.to_json())
    return str(res)