sqlalchemy

http://docs.sqlalchemy.org/en/devel/

sqlalchemy通过被用作ORM框架，也可以当作普通的数据库接口使用，内带数据库连接池

from sqlalchemy import create_engine

conf="dialect+driver://username:password@host:port/database"    dialect表示数据的名字，diver表式连接数据库使用的模块名

example="mysql+pymysql://user:mima@192.168.2.251:3306/mydatabase?charset=utf8"           mysql

engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')                                                postgresql

engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')                                                                     oracle

engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')                                                        sql server

engine = create_engine('sqlite:///foo.db')                                                                                                                     sqlite

 

engine=create_engine(conf,echo=True)      #echo是sqlalchemy内部的日志输出开关，默认为false

engine是一个数据库连接管理器, 创建engine时，实际上就创建了一个连接池pool和会话语法dialect

http://docs.sqlalchemy.org/en/devel/core/engines.html，sqlalchemy自带了很多数据连接引擎，个别需要安装相应模块，详见Dialectshttp://docs.sqlalchemy.org/en/devel/dialects/index.html

通过create_engine被创建时并没有实际连接数据库（表现为惰性连接），只有当engine。execute()或engine。connect()被首次执行时，才会创建实际的数据库连接。

创建个性化连接

def connect():
    return psycopg.connect(user='scott',host='localhost')

db=create_engine('postgresql://',creator=connect)

 

conn=engine.connect()

results=conn.execute('select username from users')  #ins可以是通过sqlalchemy创建的orm对象，也可以是sql语句

for row in results:
    print(username',row['username'])

conn.close()

这个conn是一个connection实例，为实际DBAPI连接的代理对象

results是代理结果实例，同DBAPI的cursor相联系，拥有大多cursor的特性。

当代理结果中的所有返回的rows被取出后，cursor就自动关闭了，例对于一个update操作不会返回row，cursor就会立刻关闭。

直接使用conn.execute(),是自动提交commit模式.

 

显式创建事务

conn=engine.connect()

trains=conn.begin()

try:

　　results=conn.execute()

　　r1=conn.execute(table.insert(),col1=7,col2='this is some data')

      trans.commit()

except:

    trans.rollback()

　raise

也可写作

with engine.begin() as connection:

    r1=conn.execute(table.insert(),col1=7,col2='this is some data')

　r2=conn.execute()

或

with connection.begin() as trans:
    r1 = connection.execute(table1.select())
    connection.execute(table1.insert(), col1=7, col2='this is some data')

r1.fetchall() 调用完一次后，再次调用就会返回一个空列表

r1.fetchmany()

r1.fetchone()

r1.lastrowid

r1.rowcount执行update或delete时返回的受影响行数

r1.close()关闭cursor 

conn.close()

Pooling连接池

https://docs.sqlalchemy.org/en/latest/core/pooling.html

连接池是一个用于维护数据库 的 长时间连接，高效复用，对并发连接数 进行管理的标准技术

可以在创建engine时就传入参数进行自定义

engine=create_engine('postgresql://me@localhost/mydb',pool_size=20,max_overflow=0)

当engine执行connect()或execute()时，就会向连接池获取一个连接，连接池的默认模式为QueuePool,根据需要创建连接的模式。

QueuePool的默认连接数是5，最大连接数是10. 应该保持一个数据库一个engine,而不是为每次连接创建一个engine

所有的sqlalchemy连接池都不会预先创建连接，直到第一次被使用。

使用不同的pool模式,通过poolclass：

from sqlalchemy.pool importQueuePool

engine=create_engine('sqlite://file.db',poolclass=QueuePool)

 

禁用连接池，使用NullPool

engine=create_engine('postgresql+pscopg2://scott:tiger@localhost/test',poolclass=NullPool)

所有的pool类接收一个creator参数，用于创建一个新连接。 create_engine()接收这个参数，用来连接数据库

import sqlalchemy.pool as pool 

import psycopg2

def getconn():

    c=psycopg2.connect(username='ed',host='127.0.0.1',dbname='test')

    return c 

engine=create_engine('postgresql+psycopg2://',creator=getconn)

单独使用pool

mypool=pool.QueuePool(getconn,max_overflow=10,pool_size=5)

cursor=conn.cursor()

cursor.execute('select foo')

conn.close()

调用完conn.close()这个连接就被关闭，返还回连接池

 连接池事件Pool Event 

连接池支持一个事件接口用于处理上一个连接，基于每一个新的连接

关闭连接的处理

连接池可以刷新所有的单个连接，和它的连接设置，将池中之前的连接设置为无效。

比较适合用于数据库重启后导致之前池中所有连接失效时连接池的连接回收。有两者方法来实现它：

悲观处理关闭（每有一个新的的连接就发出一个简单的连接测试比例select 1,一旦该次结果返回异常，就认为连接断开了，这个连接会立即被回收，该连接之前的所哟连接也会被标记为无效，都会在使用之前被回收）

 

engine = create_engine("mysql+pymysql://user:pw@host/db", pool_pre_ping=True)


乐观处理的方式之一  设置连接池的回收时长
e=create_engine("mysql://scott:tiger@localhost/test",pool_recycle=3600)
这样会使所有超过3600秒的连接被设置为无效连接并被替换。标记为无效的操作只在检查的过程的发生，而不是在每一个已被检查过的连接。 
pool_recycle是连接池自带的功能，依赖于一个engine是否在被使用。
这个设置适用于MySQL，MySQL会在间隔一个固定时间后自动关闭连接

 

在多进程中使用连接池

http://docs.sqlalchemy.org/en/devel/core/pooling.html

通常不建议在子进程中单独创建engine.

既不是在子进程中创建新的engine,也不是使用一个已存在的engine,而是调用engine.dispose(),在子进程开始connection之前。该方法会移除连接池中所有已存在的连接，重新创建所有的连接。写作如下：

TCP connections are represented as file descriptors, which usually work across process boundaries, meaning this will cause concurrent access to the file descriptor on behalf of two or more entirely independent Python interpreter states.

这意味这当前的到这个文件的连接代表这两个或者更多的完全独立的python接口状态,(可能是说，这个数据库的连接也代表了其他进程里的连接）

方法之一就是在 子进程创建连接之前调用 engine.dispose() 方法，dispose()是用于清除其他子进程中的连接，使在该进程的连接不影响其他的进程中的连接

engine=create_engine('...')

def run_in_process():
    engine.dispose()

    with eng.connect() as conn:

　　  conn.execute('...')

p=Process(tartget=run_in_process)

 另一种方式是设置连接池的事件，让其他的连接在这个子进程里自动失效，这种方式可能更合适

from sqlalchemy import event

from sqlalchemy import exc 

import os 

eng=create_engine('..')

@event.listens_for(engine,"connect")

def connect(dbapi_connection,connection_record):

　　connection_record.info['pid']=os.getpid()

@event.listens_for(engine,"checkout")

def checkout(dbapi_connection,connection_record,connection_proxy):

　　pid=os.getpid()

　　if connection_record.info['pid']!=pid:

　　　　connection_record.connection=connection_proxy.connection=None

　　　　raise exc.DisconnectionError(

　　　　　　　"Connection record belongs to pid %s,"

　　　　　　　"attempting to check out in pid %s"%(connection_record.info['pid'],pid))

 

 

 

 

将sqlalchemy内置log部分加入日志,echo=True只是用于控制台的输出。

import logging

logging.basicConfig()

logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)