sqlalchemy 初步认识

1. 前言

1.1 ORM 模型

亦即对象关系映射， ie， 将关系数据库中的业务数据用对象的形式表示出来， 并通过面向对象的方式将这些对象组织起来， 最终在应用程序中创建一个“虚拟对象数据库”

1.2 SQLAlchemy 架构

稳定， 高性能

1.3 说明

这里仅对 ORM层的操作做一些记录， core层未涉及

2. 基本操作

2.1 创建数据库引擎

用下面这段代码可以创建一个 sqlite 的数据库， ps： python 内置的就是 sqlite 数据库

engine = create_engine('sqlite:///foo.db', echo=True)

echo = True 表示 显示相应执行的 sql 指令

2.2 建立表

1. 从 Base 类 派生一个对象类， 这个类与所需要创建的数据库的表格相关， tablename 表征表的名字， 其他 id， name, fullname, password 为表的字段

2. 通过 Base.metadata.create_all(engine) 来创建所有 Base 派生类所对应的数据表

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)

# 建立表
Base.metadata.create_all(engine)

2.3 操作数据库

主要通过Session 会话完成：
借助 query， filter_by, first, add_all 等指令来辅助实现

session.add(ed_user)
our_user = session.query(User).filter_by(name = 'ed').first()
# select * from users where name = 'ed' limit 1;

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")
])
session.commit()

print(session.query(User).all())

for row in session.query(User).filter(User.name.in_(['ed', 'wendy', 'jack'])):
    print(row)

for row in session.query(User).filter(~User.name.in_(['ed', 'wendy', 'jack'])):
    print(row)

print(session.query(User).filter(User.name == 'ed').count())

2.4 外键操作

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref

# 建立一个带外键的表
class Address(Base):
    __tablename__ = "addresses"
    id = Column(Integer, primary_key=True)
    email_address = Column(Integer, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

    user = relationship("User", backref=backref("addresses", order_by=id))

    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

# 建立表， 并向表中插入数据
Base.metadata.create_all(engine)
jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
jack.addresses = [
    Address(email_address='jack@google.com'),
    Address(email_address='j24@yahoo.com')
]
session.add(jack)
session.commit()

# 联合查询
for u, a in session.query(User, Address).filter(User.id==Address.user_id).\
    filter(Address.email_address=="jack@google.com").all():
    print u, a

2.5 完整代码段

# -*- coding=utf-8 -*-

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

print(sqlalchemy.__version__)

engine = create_engine('sqlite:///foo.db', echo=True)

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)

# 建立表
#Base.metadata.create_all(engine)
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
print(ed_user)

Session = sessionmaker(bind=engine)
session = Session()

# session.add(ed_user)
# our_user = session.query(User).filter_by(name = 'ed').first()
# # select * from users where name = 'ed' limit 1;
#
# 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")
# ])
# session.commit()

# print(session.query(User).all())
#
# for row in session.query(User).filter(User.name.in_(['ed', 'wendy', 'jack'])):
#     print(row)
#
# for row in session.query(User).filter(~User.name.in_(['ed', 'wendy', 'jack'])):
#     print(row)
#
# print(session.query(User).filter(User.name == 'ed').count())


from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref

class Address(Base):
    __tablename__ = "addresses"
    id = Column(Integer, primary_key=True)
    email_address = Column(Integer, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

    user = relationship("User", backref=backref("addresses", order_by=id))

    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

#Base.metadata.create_all(engine)
jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
jack.addresses = [
    Address(email_address='jack@google.com'),
    Address(email_address='j24@yahoo.com')
]
session.add(jack)
session.commit()

for u, a in session.query(User, Address).filter(User.id==Address.user_id).\
    filter(Address.email_address=="jack@google.com").all():
    print u, a

3. 使用过程中的一些小问题

3.1 pycharm 无法打开 foo.db 文件

这个原因主要是， 驱动没有安装，

在上述界面中点击 download 安装所需驱动即可

3.2 数据库连接语法

可以查看这个链接：
http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#database-urls

3.3 cannot import name create_engine

这里的主要原因是我们把文件名命名的和 模块名字重复了， 导致模块 sqlchemy 没有被正确的引入进来