SQLAlchemy

Connecting

连接到数据库的书写规范:

engine = create_engine('dialect+driver://username:password@host:port/database')

sqlite缓存模式

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)

mysql连接实例:

engine = create_engine('mysql://root:password@localhost/testsqlalchemy',encoding="utf-8", echo=True)

s1.png s2.png

MySQL-connector-python

engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

Declare a Mapping

数据库与类映射的基类:

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

定义一个表(类):

from sqlalchemy import Column, Integer, String
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)
    def __repr__(self):
    return "<User(name='%s', fullname='%s', password='%s')>" % (
                           self.name, self.fullname, self.password)

注意:创建的表(类)名字以及字段必须是数据库的表本身存在的,否则产生错误

Create a Schema(创建一个图表)

User.__table__ 
Table('users', MetaData(bind=None),
            Column('id', Integer(), table=<users>, primary_key=True,        nullable=False),
            Column('name', String(), table=<users>),
            Column('fullname', String(), table=<users>),
            Column('password', String(), table=<users>), schema=None)

每个个table对象是MetaData的一个实例,故可以使用.metadata属性.
可以使用MetaData.create_all()方法连接到数据库.

Base.metadata.create_all(engine) 

生成一个字段,类型为string,长度限制在50:

 Column(String(50))

sequence通常关联在主键,代表一个表的名称和结构参数.

some_table = Table(
'some_table', metadata,
Column('id', Integer, Sequence('some_table_seq'),
primary_key=True)
)

Create an Instance of the Mapped Class(实例化一个表)

>>> ed_user = User(name='ed', fullname='Ed Jones',     password='edspassword')
>>> ed_user.name
'ed'
>>> ed_user.password
'edspassword'
>>> str(ed_user.id)
'None'