【Python模块】sqlalchemy orm模块--基础（连接数据库，建表，增删改查）

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据库API执行SQL并获取执行结果。

安装：

pip3 install SQLAlchemy

版本检查：

import sqlalchemy
sqlalchemy.__version__

不同数据库配置：

根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作：

格式：'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
 
pymysql
    mysql+pymysql://<username>:<password>@<host>[:<port>]/<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...]

连接数据库：

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:a13896321z@127.0.0.1/school",encoding='utf-8',echo=True)

声明映射：

declaractive用来表示类与表的关系。

声明基类，将类映射到数据表，自定义数据表类必须继承这个基类。

from sqlalchemy.ext.declaractive import  declaractive_base
Base = declaractive_base()

创建表结构类：

一个表结构类必须包含一个__tablename__和primary_key的字段

字段的类型：

SmallInteger
Integer
BigInteger
Float
Numeric(precision=None, scale=None, decimal_return_scale=None, asdecimal=True)

Boolean
Enum

Date
DateTime
Time
Interval(native=True, second_precision=None, day_precision=None)

LargeBinary(length=None)
MatchType(create_constraint=True, name=None, _create_events=True)
PickleType(protocol=4, pickler=None, comparator=None)
SchemaType(name=None, schema=None, metadata=None, inherit_schema=False, quote=None, _create_events=True)
String(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)
Text(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)
Unicode(length=None, **kwargs)

# 常用的类型：
类型名        python类型            说明
Integer       int                普通整数，32位    
Float         float              浮点数    
String        str                变长字符串    
Text          str                变长字符串，对较长字符串做了优化    
Boolean       bool               布尔值    
PickleType    任何python对象      自动使用Pickle序列化

字段的选项：

Column(
nullable=True,      # 可以为空

autoincrement=True, # 数值自增

default='red'       # 指定默认值

primary_key = True  # 设为主键

ForeignKey = 'table_name.id'    # 指定外键，ForeignKey需要导入

unique = True       # 设定此字段键值唯一，不允许出现重复值。
)

建立sqlalchemy连接：

from sqlalchemy import create_engine
engine = create_engine()

声明表结构：

class School(Base):
    __tablename__ = "school"                # 这个才是表名
    id = Column(Integer, primary_key = True)
    sch_name = Column(String(32))
    sch_addr = Column(String(255))
    sch_tel = Column(Integer)
    
    def __repr__(self):
        # 查询的时候显示的是值，需不是一个内存地址。
        return "school name:{},tel:{}".format(self.sch_name,self.sch_tel)

动态添加表字段。

def add_filed(table_name,):
for i in range(3):

    setattr(table_class,'Col'+str(i),(Column('Col'+str(i), String(50),comment='Col'+str(i))))

Base.metadata.create_all(engine)

创建表：

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

增加一条记录：

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)        # 创建一个与数据库的会话，生成的对象是类
session = Session()                        # 实例化这个会话类

class AddRecord(object):
    # 交互添加记录类。
    def __init__(self,*args):
        # self.table_name = table_name
        self.school_feild = {'sch_name':'学校名：','sch_addr':'学校地址：','sch_tel':'学校电话：'}
    
    def school(self):
        f = {}
        for k,v in self.school.items():
            f[k] = input('{}'.format(v)).strip()
        return f            # 返回一个字典
        
add = AddRecord()
school_attr = add.school()    # 获取输入的值
# 下面a1是创建一条记录
a1 = School(sch_name = school_attr[sch_name],sch_addr= school_attr[sch_addr],sch_tel = school_attr[sch_tel])
session.add(a1)                # 把记录添加到数据库
session.commit()               # 提交到数据库，最终对数据库更改。

表的参数__table_args__：

__table_args__:参数需要导入。

from sqlalchemy import UniqueConstraint, PrimaryKeyConstraint, Index

 增、查、改、删：

这四个操作都需要导入sessionmaker ，用sessionmaker创建一个会话，除了建库外，所有操作都在会话中完成。

from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
session =Session()

 增：

 查：

 这三个操作都需要用到query

改：

删：

一个基本表的创建与操作：

from sqlalchemy import create_engine, ForeignKey, Table, Column, String, Integer, Boolean, Enum

from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
mysql_name = 'david'
mysql_pw = 'Yaotiao&shunv666'
mysql_server = '192.168.2.120'
database_name = 'test'


# 1、连接数据库
engine = create_engine("mysql+pymysql://{name}:{pw}@{server}/{database}".
                        format(mysql_name,mysql_pw,mysql_server,database_name),
                        encoding ='utf-8'
                        )

# 2、生成ORM基类
Base = declarative_base()


# 3、继承基类，定义表结构
class Product(Base):
    # 创建orm对象
    __tablename__ = 'products'                # 数据表名
    id = Column(Integer, primary_key = True)    # 字段，设为主键，默认不用赋值，此字段会自增
    name = Column(String(32))
    price = Column(Integer)

    def __repr__(self):
        # 打印查询结果实例显示结果，不加此项显示的是类的内存地址
        return "name:{},price:{}".format(self.name,self.price)
    
# 4、创建数据表
Base.metadata.create_all(engine)                # 在数据库中创建表，已存在则不创建


# 5、添加多条数据
product1 = Product(name = '华为100', price = 999) # 创建两个实例
product2 = Product(name = '华为400', price = 1999)
session.add_all([product1,product2 ])    # add_all把实例列表添加到数据库

# 6、添加单条数据
product = Product(name = '一条', price = 9999)
session.add(product)


# 7、查询数据

query_data = session.query(Product).all()       # 查所有，如果不加__repr__,结果是内存地址列表，使用for查看

for i in query_data:
    # 查看结果
    print (i.id,i.name,i.price)

# 8、修改一项数据
query_data = session.query(Product).filter(Product.name=='一条').first()
query_data.name = 'yitiao'        # 只改name一个数据

# 9、批量更新：把所有华为开头的产品价格改成10000
session.query(Product).filter(Product.name.like('华为%')).update({Product.price:10000},synchronize_session =False)


# 10、通过主键改内容merge：
update_name = Product(id=3,price=3651) 
session.merge(update_name)                # id 和 name不变，只修改了价格price = 3651

# 11、删除一条数据：把华为开头的价格最高的一条数据删掉
del_product = session.query(Product).fileter(Product.price.like('华为%'))).order_by(Product.price.desc()).first()
session.delete(del_product)

# 12、删除所有符合条件的数据：
session.query(Product).filter(Product.name.like('华为%')).delete(synchronize_session = False)

 问题集：https://blog.51cto.com/yishi/2335554