python sqlalchemy的应用

来源：互联网发布：mac能播p2p电影浏览器编辑：程序博客网时间：2024/06/06 06:39

二十四、python sqlalchemy的应用

The Python SQL Toolkit and Object Relational Mapper

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

数据库表是一个二维表，包含多行多列。把一个表的内容用Python的数据结构表示出来的话，可以用一个list表示多行，list的每一个元素是tuple，表示一行记录，比如，包含id和name的user表：

[    ('1', 'Michael'),    ('2', 'Bob'),    ('3', 'Adam')]

Python的DB-API返回的数据结构就是像上面这样表示的。

但是用tuple表示一行很难看出表的结构。如果把一个tuple用class实例来表示，就可以更容易地看出表的结构来：

class User(object):    def __init__(self, id, name):        self.id = id        self.name = name[    User('1', 'Michael'),    User('2', 'Bob'),    User('3', 'Adam')]

这就是传说中的ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上。是不是很简单？

但是由谁来做这个转换呢？所以ORM框架应运而生。

在Python中，最有名的ORM框架是SQLAlchemy。我们来看看SQLAlchemy的用法。

首先通过easy_install或者pip安装SQLAlchemy：

验证
>>> import sqlalchemy

-----------------------order订单案例-------------------------------------

#sqlAlchemy的介绍

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, DateTime, ForeignKey

from sqlalchemy.orm import sessionmaker, relationship, backref

#大概步骤如下：

#1、创建连接

#mysql+mysqlconnector 或者 mysql+mysqldb mysql代表数据库，mysqlconnector代表连接驱动

engine = create_engine('mysql+mysqlconnector://root:root@192.168.216.7:3306/testdb?charset=utf8',echo=True) #echo是否打印sql语句

#2、定义映射

Base = declarative_base() #获取公共类，模板的顶级父类

#定义自己的实体类

class User(Base):

__tablename__ = 'users'

id = Column(Integer, nullable=True, primary_key=True,index=True)

user_name = Column(String(45), nullable=True)

orders = relationship('Order')

class Product(Base):

__tablename__ = 'products'

id = Column(Integer, nullable=True, primary_key=True, index=True)

product_name = Column(String(45), nullable=True)

orders = relationship('Order')

class Order(Base):

__tablename__ = 'orders'

id = Column(Integer,nullable=False,primary_key=True,index=True)

order_name = Column(String(45), nullable=False)

uid = Column(Integer, ForeignKey('users.id'))

pid = Column(Integer, ForeignKey('products.id'))

#3、映射实例化，创建数据库表

Base.metadata.create_all(engine)

#4、创建会话

Session = sessionmaker(engine)

session = Session()

#5、对象实例持久化

# aidon = User(user_name='aidong')

# session.add(aidon)

# pro = Product(product_name='item1')

# session.add(pro)

# session.commit()

aidon = session.query(User).filter(User.user_name== 'aidong').one()

p1 = session.query(Product).filter(Product.product_name =='item1').one()

o1 = Order(order_name='007',uid=aidon.id,pid=p1.id)

session.add(o1)

session.commit()

orders = aidon.orders

for order in orders:

print order.id, order.order_name

orders = p1.orders

for order in orders:

print order.id, order.order_name

-------------------------------------------------------------

----------------------blog博客案例--------------------------------

#Blog相关练习

from sqlalchemy import create_engine, Table

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, String, Integer, Text

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship, sessionmaker

from faker import Factory

import random

engine = create_engine("mysql+mysqldb://root:root@192.168.216.7:3306/testdb?charset=utf8", echo=True)

print engine

#获取基础

Base = declarative_base()

#创建表对应的映射类

class User(Base):

__tablename__ = 'blog_user'

id = Column(Integer, nullable=False, primary_key=True, index=True)

username = Column(String(45), nullable=False, index=True)

password = Column(String(45), nullable=False)

email = Column(String(45), nullable=False, index=True)

#用户表和文章表一对多映射

articles = relationship('Article', backref='blog_article')

#articles = relationship('Article', backref='author') #这样就自定义一个关系，不需要在Article中定义relation咯

#用户表和用户信息表一对一映射

userinfo = relationship('UserInfo', backref='blog_user', uselist=False) #不使用集合uselist=

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.username)

class Article(Base):

__tablename__ = 'blog_article'

id = Column(Integer, primary_key=True, nullable=True, index=True)

title = Column(String(255), nullable=False)

content = Column(Text)

# 用户表和文章表一对多映射

user_id = Column(Integer, ForeignKey('blog_user.id'))

author = relationship('User')

#文章中存储有类别id，并且是外键

cate_id = Column(Integer, ForeignKey('bolg_categories.id'))

#文章和标签是多对多的关系

tags = relationship('Tag', secondary='blog_article_tag', backref='blog_article') #secondary=必须是映射表名

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.title)

class UserInfo(Base):

__tablename__ = 'blog_userinfo'

id = Column(Integer, primary_key=True, nullable=True, index=True)

name = Column(String(45))

qq = Column(String(11))

phone = Column(String(11))

like = Column(String(45))

#用户信息和用户表中的一对一的关系;定义方法和一对多相同，只是需要在多的以一方添加userlist=False

user_id = Column(Integer, ForeignKey('blog_user.id'))

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.title)

class Category(Base):

__tablename__ = 'bolg_categories'

id = Column(Integer,primary_key=True, nullable=False, index=True)

name = Column(String(45), nullable=False)

#一个类别在多个文章中(一对多)

articles = relationship('Article', backref='category')

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.name)

#一遍博客通常有一个分类，好几个标签。标签与博客之间就是一个多对多的关系。

# 多对多关系不能直接定义，需要分解成俩个一对多的关系，为此，需要一张额外的表来协助完成

article_tag = Table(

'blog_article_tag', Base.metadata,

Column('article_id', Integer, ForeignKey('blog_article.id')),

Column('tag_id', Integer, ForeignKey('blog_tags.id'))

)

class Tag(Base):

__tablename__ = 'blog_tags'

id = Column(Integer, primary_key=True, index=True)

name = Column(String(45), nullable=False)

def __repr__(self):

return '%s(%r)' % (self.__class__.name, self.name)

if __name__ == '__main__':

#映射实例化，创建表

Base.metadata.create_all(engine)

#获取和mysql的Session,自定义一个Session类

Session = sessionmaker(bind=engine)

session = Session()

#使用faker进行数据填充

faker = Factory.create()

#定义一个列表生成器

userFakers = [User(username=faker.name(), password=faker.word(), email=faker.email()) for i in range(10)]

session.add_all(userFakers)

#faker中没有phone、qq.所以就随便模拟

for i in userFakers:

userInfoFaker = UserInfo(name=i.username, qq=random.randint(1, 99999999999), phone=random.randint(13000000000, 99999999999), like=faker.word())

session.add(userInfoFaker)

categoryFakers = [Category(name=faker.word()) for i in range(5)]

session.add_all(categoryFakers)

tagFakers = [Tag(name=faker.word()) for i in range(20)]

session.add_all(tagFakers)

for i in range(100):

article = Article(title=faker.sentence(),

content=''.join(faker.sentences(nb=random.randint(10, 20))),

author=random.choice(userFakers),

category=random.choice(categoryFakers)

)

for tag in random.sample(tagFakers, random.randint(2, 5)):

article.tags.append(tag)

session.add(article)

#提交sessions

#session.commit()

#查询 get(n)、filter_by()根据一个字段条件查找 filter()根据多个条件给我、all() 获取所有

user1 = session.query(User).get(10)

user2 = session.query(User).filter_by(username='Linda Tran').first()

user3 = session.query(User).filter(User.username == 'Linda Tran').first()

user4 = session.query(User).all()

print user1

print user2

print '====', user3

print user4

#更新某个字段数据

a = session.query(Article).get(10)

a.title = 'my test blog'

session.add(a)

#删除

session.delete(a)

session.commit()

--------------------------------------------------------------

阅读全文

0 0