sqlalchemy

系统的学一下sqlalchemyA,不过没有中文文档,只能用我的渣英文能力慢慢看原文档了

数据库连接

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

echo为True输出日志

各种数据库连接方式

dialect+driver://username:password@host:port/database

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. Oracle

   engine = create_engine('oracle://scott:[email protected]:1521/sidname')
   engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')
   

4. SQLite

   # sqlite://<nohostname>/<path>
   # where <path> is relative:
   engine = create_engine('sqlite:///foo.db')
   #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')
   # To use a SQLite :memory: database, specify an empty URL:
   engine = create_engine('sqlite://')
   

声明样式

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()
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)

样式参数说明

	说明
**\_tablename\_\_**	表名

字段类型及说明

字段类型及文档

1. Generic Types

   字段类型	说明
   BigInteger	大数
   Boolean(createconstraint=True, name=None, _createevents=True)	布尔
   Data
   DateTime(timezone=False)	时间
   Enum(*enums, **kw)	枚举
   Float(precision=None, asdecimal=False, decimalreturnscale=None, **kwargs)	浮点型
   Integer	整型
   Interval(native=True, secondprecision=None, dayprecision=None)	时间间隔
   LargeBinary(length=None)	二进制大对象
   MatchType(createconstraint=True, name=None, _createevents=True)
   Numeric(precision=None, scale=None, decimalreturnscale=None, asdecimal=True)
   PickleType(protocol=2, pickler=None, comparator=None)
   SchemaType(name=None, schema=None, metadata=None, inheritschema=False, quote=None, _createevents=True)
   SmallInteger	小整型
   String(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False)	字符串
   Text(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False)	文本对象
   Time(timezone=False)	时间
   Unicode(length=None, **kwargs)
   UnicodeText(length=None, **kwargs)

2. SQL Standard Types

   字段类型	说明
   BIGINT
   BINARY(length=None)
   BLOB(length=None)
   BOOLEAN(createconstraint=True, name=None, _createevents=True)
   CHAR(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False)
   CLOB(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False)
   DATE
   DATETIME(timezone=False)
   DECIMAL(precision=None, scale=None, decimalreturnscale=None, asdecimal=True)
   FLOAT(precision=None, asdecimal=False, decimalreturnscale=None, **kwargs)
   INT
   INTEGER
   NCHAR(length=None, **kwargs)
   NVARCHAR(length=None, **kwargs)
   NUMERIC(precision=None, scale=None, decimalreturnscale=None, asdecimal=True)¶
   REAL(precision=None, asdecimal=False, decimalreturnscale=None, **kwargs)
   SMALLINT
   TEXT(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False
   TIME(timezone=False)
   TIMESTAMP(timezone=False)
   VARBINARY(length=None)
   VARCHAR(length=None, collation=None, convertunicode=False, unicodeerror=None, _warnonbytestring=False)

创建表

Base.metadata.create_all(engine)

为表创建实例

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

创建session

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

你也可以先创建Session,在创建应用前

>>> Session = sessionmaker()

当你创建了应用,可以这样配置

>>> Session.configure(bind=engine)  # once engine is available

插入和更新数据

插入

插入一条数据

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
>>> session.add(ed_user)

插入多条数据

>>> session.add_all([
...     User(name='wendy', fullname='Wendy Williams', password='foobar'),
...     User(name='mary', fullname='Mary Contrary', password='xxg527'),
...     User(name='fred', fullname='Fred Flinstone', password='blah')])

更新

>>> ed_user.password = 'f8s7ccs'

查看

>>> session.dirty
IdentitySet([<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>])
>>> session.new  # doctest: +SKIP
IdentitySet([<User(name='wendy', fullname='Wendy Williams', password='foobar')>,
<User(name='mary', fullname='Mary Contrary', password='xxg527')>,
<User(name='fred', fullname='Fred Flinstone', password='blah')>])

必须提交才能生效

>>> session.commit()