sqlalchemy

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

数据库连接

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

echo为True输出日志

各种数据库连接方式

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

1. Postgresql

   1# default
   2engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
   3# psycopg2
   4engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
   5# pg8000
   6engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
   

2. MySQL

   1# default
   2engine = create_engine('mysql://scott:tiger@localhost/foo')
   3# mysql-python
   4engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
   5# MySQL-connector-python
   6engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
   7# OurSQL
   8engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')
   

3. Oracle

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

4. SQLite

    1# sqlite://<nohostname>/<path>
    2# where <path> is relative:
    3engine = create_engine('sqlite:///foo.db')
    4#Unix/Mac - 4 initial slashes in total
    5engine = create_engine('sqlite:////absolute/path/to/foo.db')
    6#Windows
    7engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
    8#Windows alternative using raw string
    9engine = create_engine(r'sqlite:///C:\path\to\foo.db')
   10# To use a SQLite :memory: database, specify an empty URL:
   11engine = create_engine('sqlite://')
   

声明样式

 1from sqlalchemy.ext.declarative import declarative_base
 2from sqlalchemy import Column, Integer, String
 3
 4Base = declarative_base()
 5class User(Base):
 6     __tablename__ = 'users'
 7
 8     id = Column(Integer, primary_key=True)
 9     name = Column(String)
10     fullname = Column(String)
11     password = Column(String)
12
13     def __repr__(self):
14        return "<User(name='%s', fullname='%s', password='%s')>" % (
15                             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)

创建表

1Base.metadata.create_all(engine)

为表创建实例

1>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
2>>> ed_user.name
3'ed'
4>>> ed_user.password
5'edspassword'
6>>> str(ed_user.id)
7'None'

创建session

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

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

1>>> Session = sessionmaker()

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

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

插入和更新数据

插入

插入一条数据

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

插入多条数据

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

更新

1>>> ed_user.password = 'f8s7ccs'

查看

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

必须提交才能生效

1>>> session.commit()