SQLAlchemy SQLAlchemy

SQLAlchemy

1.1.SQLAlchemy介绍

SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上,使用关系对象映射进行数据库操作,简言之便是:将类和对象转换成SQL,然后使用数据API执行SQL并获取执行结果。

安装

pip install sqlalchemy

组成部分

Engine,框架的引擎
Connection Pooling ,数据库连接池
Dialect,选择连接数据库的DB API种类
Schema/Types,架构和类型
SQL Exprression Language,SQL表达式语言

SQLAlchemy本身无法操作数据库,其必须以来pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
    
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
    
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
    
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
    
更多:http://docs.sqlalchemy.org/en/latest/dialects/index.html

1.2.SQLAlchemy表结构

 (1)创建单表

import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index

Base = declarative_base()


class Users(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=False)
    email = Column(String(32), unique=True)
    ctime = Column(DateTime, default=datetime.datetime.now)
    extra = Column(Text, nullable=True)

    __table_args__ = (
        UniqueConstraint('id', 'name', name='uix_id_name'),    #id和name联合唯一
        Index('ix_id_name', 'name', 'email'),            #索引
    )


def init_db():
    """
    根据类创建数据库表
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收(重置)
    )

    Base.metadata.create_all(engine)


def drop_db():
    """
    根据类删除数据库表
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收(重置)
    )

    Base.metadata.drop_all(engine)


if __name__ == '__main__':
    drop_db()
    init_db()

(2)创建多个表(包含FK,M2M关系)

import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship

Base = declarative_base()


# ##################### 单表示例 #########################
class Users(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)
    age = Column(Integer, default=18)
    email = Column(String(32), unique=True)
    ctime = Column(DateTime, default=datetime.datetime.now)
    extra = Column(Text, nullable=True)

    __table_args__ = (
        # UniqueConstraint('id', 'name', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'extra'),
    )


class Hosts(Base):
    __tablename__ = 'hosts'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)
    ctime = Column(DateTime, default=datetime.datetime.now)


# ##################### 一对多示例 #########################
class Hobby(Base):
    '''爱好'''
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    hobby_id = Column(Integer, ForeignKey("hobby.id"))    #hobby指的__tablename__ = 'hobby',而不是类名Hobby

    # 与生成表结构无关,仅用于查询方便
    hobby = relationship("Hobby", backref='pers')


# ##################### 多对多示例 #########################

#第三张表要自己生成
class Server2Group(Base):
    __tablename__ = 'server2group'
    id = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))


class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)

    # 与生成表结构无关,仅用于查询方便
    servers = relationship('Server', secondary='server2group', backref='groups')


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)


def init_db():
    """
    根据类创建数据库表
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收(重置)
    )

    Base.metadata.create_all(engine)


def drop_db():
    """
    根据类删除数据库表
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收(重置)
    )

    Base.metadata.drop_all(engine)


if __name__ == '__main__':
    drop_db()
    init_db()

实例:

models.py

import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship

Base = declarative_base()


# ##################### 单表示例 #########################
class Users(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)
    age = Column(Integer, default=18)
    email = Column(String(32), unique=True)
    ctime = Column(DateTime, default=datetime.datetime.now)
    extra = Column(Text, nullable=True)

    __table_args__ = (
        # UniqueConstraint('id', 'name', name='uix_id_name'),
        # Index('ix_id_name', 'name', 'extra'),
    )


class Hosts(Base):
    __tablename__ = 'hosts'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)
    ctime = Column(DateTime, default=datetime.datetime.now)


# ##################### 一对多示例 #########################
class Hobby(Base):
    '''爱好'''
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    hobby_id = Column(Integer, ForeignKey("hobby.id"))    #hobby指的__tablename__ = 'hobby',而不是类名Hobby

    # 与生成表结构无关,仅用于查询方便
    hobby = relationship("Hobby", backref='pers')


# ##################### 多对多示例 #########################

#第三张表要自己生成
class Server2Group(Base):
    __tablename__ = 'server2group'
    id = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))


class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)

    # 与生成表结构无关,仅用于查询方便
    servers = relationship('Server', secondary='server2group', backref='groups')


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)

if __name__ == '__main__':
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收(重置)
    )

    Base.metadata.create_all(engine)   #创建

    # Base.metadata.drop_all(engine)   #删除

1.3.SQLAlchemy两种连接方式

第一种

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
import models

#1.创建连接池
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)

#2.从连接池中获取数据库连接
session = Session()

#3.执行ORM操作
obj1 = models.Users(name="derek",email='derek@163.com')
session.add(obj1)
# 提交事务
session.commit()

# 4.关闭数据库连接(将连接放回连接池)
session.close()

第二种、基于scoped_session实现线程安全

首先导入,然后只要修改session = scoped_session(Session)

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
import models

#1.创建连接池
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)

#2.从连接池中获取数据库连接
# session = Session()
session = scoped_session(Session)

#3.执行ORM操作
obj1 = models.Users(name="jack",email='jack@163.com')
session.add(obj1)
# 提交事务
session.commit()

# 4.关闭数据库连接
session.close()

1.4.增加数据

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
import models

#1.创建连接池
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)

#2.从连接池中获取数据库连接
session = Session()
# session = scoped_session(Session)

# #3.执行ORM操作
# obj1 = models.Users(name="jack",email='jack@163.com')
# session.add(obj1)
# # 提交事务
# session.commit()

#批量增加,里面是列表
session.add_all([
    models.Users(name="jack1",email='jack1@163.com'),
    models.Users(name="jack2",email='jack2@163.com'),
])
session.commit()

# 4.关闭数据库连接
session.close()

1.5.查看和删除数据

#查看数据
user_list = session.query(models.Users).all()
for row in user_list:
    print(row.id)
    print(row.name)
    print(row.email)
    print(row.ctime)
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
import models

#1.创建连接池
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)

#2.从连接池中获取数据库连接
session = Session()
# session = scoped_session(Session)

# #3.执行ORM操作
# obj1 = models.Users(name="jack",email='jack@163.com')
# session.add(obj1)
# # 提交事务
# session.commit()

# #批量增加,里面是列表
# session.add_all([
#     models.Users(name="jack1",email='jack1@163.com'),
#     models.Users(name="jack2",email='jack2@163.com'),
# ])
# session.commit()

#查看数据
user_list = session.query(models.Users).all()
for row in user_list:
    print(row.id)
    print(row.name)
    print(row.email)
    print(row.ctime)

# 4.关闭数据库连接
session.close()

查看的结果:

添加过滤条件

user_list = session.query(models.Users).filter(models.Users.id > 2)   #id大于2的

 删除数据

#删除数据
session.query(models.Users).filter(models.Users.id > 4).delete()
session.commit()
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
import models

#1.创建连接池
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)

#2.从连接池中获取数据库连接
session = Session()
# session = scoped_session(Session)

# #3.执行ORM操作
# obj1 = models.Users(name="jack",email='jack@163.com')
# session.add(obj1)
# # 提交事务
# session.commit()

# #批量增加,里面是列表
# session.add_all([
#     models.Users(name="jack1",email='jack1@163.com'),
#     models.Users(name="jack2",email='jack2@163.com'),
# ])
# session.commit()

#查看数据
# user_list = session.query(models.Users).all()
# user_list = session.query(models.Users).filter(models.Users.id > 2)   #id大于2的
# for row in user_list:
#     print(row.id)
#     print(row.name)
#     print(row.email)
#     print(row.ctime)

#删除数据
session.query(models.Users).filter(models.Users.id > 4).delete()
session.commit()


# 4.关闭数据库连接
session.close()

1.6.修改数据

#修改数据
session.query(models.Users).filter(models.Users.id == 4).update({'name':'Tom'})
session.query(models.Users).filter(models.Users.id == 1).update({'name': models.Users.name + "099"}, synchronize_session=False)
session.query(models.Users).filter(models.Users.id == 3).update({"age": models.Users.age + 1}, synchronize_session="evaluate") 
session.commit()

结果:

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JavaEdge

单例模式(Singleton Pattern)百媚生1 动机2 定义结构分析优点缺点适用场景应用总结实现方式1、懒汉式(非线程安全)2、懒汉式(线程安全)4、双重检验锁模式(double checke

38110
来自专栏IT杂记

INET_ATON()函数在MySQL5.6版本和5.7版本的差异

问题 ### The error occurred while setting parameters ### SQL: insert into t_gatewa...

2779
来自专栏

C++实现线程安全的单例模式

在某些应用环境下面,一个类只允许有一个实例,这就是著名的单例模式。单例模式分为懒汉模式,跟饿汉模式两种。 首先给出饿汉模式的实现 template <class...

2117
来自专栏happyJared

设计模式入门:单例模式

  单例模式属于创建型模式,是一种较为简单的设计模式,但也是最容易让人犯错的。在不同的单例模式实现中,首先要确保构造函数是私有的,然后提供一个静态入口(方法)用...

1552
来自专栏乐沙弥的世界

mongoDB 文档查询

1、文档查询db.users.find()等价于db.users.find( {} ) 2、基于and运算符的多个组合条件可以省略and运算符的多个组合条件...

1691
来自专栏GreenLeaves

存储过程详解

存储过程简介 什么是存储过程:存储过程可以说是一个记录集吧,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表...

22110
来自专栏代码世界

Django中ORM介绍和字段及其参数

ORM介绍 ORM概念   对象关系映射(Object Relational Mapping,简称ORM)模式是一种为了解决面向对象与关系数据库存在的互不匹配的...

4448
来自专栏静默虚空的博客

[设计模式]单例模式

简介 单例模式(Singleton Pattern)保证一个类只有一个实例,并提供一个访问它的全局访问点。 单例模式是一种对象创建型模式 (可参考 设计模式 ...

2049
来自专栏跟着阿笨一起玩NET

C#设计模式学习笔记-单例模式

  最近在学设计模式,学到创建型模式的时候,碰到单例模式(或叫单件模式),现在整理一下笔记。

902
来自专栏菩提树下的杨过

Lucene.Net 删除索引DeleteDocuments的注意事项

Lucene.Net 在删除索引时,经常会出现代码成功执行,但索引并未正直删除的现象,总结一下,要注意以下因素: 1.在创建Term时,注意Term的key一定...

1896

扫码关注云+社区

领取腾讯云代金券