Connecting
連接到數(shù)據(jù)庫的書寫規(guī)范:
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
數(shù)據(jù)庫與類映射的基類:
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)
注意:創(chuàng)建的表(類)名字以及字段必須是數(shù)據(jù)庫的表本身存在的,否則產(chǎn)生錯誤
Create a Schema(創(chuàng)建一個圖表)
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()方法連接到數(shù)據(jù)庫.
Base.metadata.create_all(engine)
生成一個字段,類型為string,長度限制在50:
Column(String(50))
sequence通常關(guān)聯(lián)在主鍵,代表一個表的名稱和結(jié)構(gòu)參數(shù).
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'