20180202之engine,URL,base,session

1. SQLAlchemy版本信息检查

import sqlalchemy
print(sqlalchemy.__version__)

2. 数据库链接
1. 创建engine

from sqlalchemy import create_engine
engin=create_engine("dialect+driver://username:password@host:port/database")

2. 数据库URL支持
1. Postgresql:

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

2. MySQL:

# default
engine = create_engine('mysql://scott:tiger@localhost/foo')
# mysql-python
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
# MySQL-connector-python
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
# OurSQL
engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')

3. Oracl:

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

4. Microsoft SQL Server:

   # pyodbc
   engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')
   # pymssql
   engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

5. SQLite:

   #Unix/Mac - 4 initial slashes in total
   engine = create_engine('sqlite:////absolute/path/to/foo.db')
   #Windows
   engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
   #Windows alternative using raw string
   engine = create_engine(r'sqlite:///C:\path\to\foo.db')
   #Memony SQLite database
   engine = create_engine('sqlite://')

3. Declarative方法对象
1. 基类创建

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

2. 基于基类创建映射类

from sqlalchemy import Column,Integer,String
class User(Base):
    __tablename__="user"
    id=Column(Integer,primary_key=True)
    name=Column(String)

3. 通过映射类创建实例

user = User(name='Huangy',fullname='Huangya', password='123.com')

4. 将映射类同步到数据库

   #创建数据库
   Base.metadata.create_all(engine)

4. Session，用于ORM与数据库的链接，创建session的时候需绑定数据库engine

from sqlalchemy.orm import sessionmaker
Session=sessionmaker(bind=engine)

1. 需要session时，再初始化

#当需要和数据库链接的时候，再初始化一个session对象
session=Session()

2. 虽然以上操作session和engine已经关联，但是无任何链接，当使用的时候，再从engine维护的链接池中检索是否存在链接，若存在则保持，直到close或更改。