云顶娱乐官方入口-www.40081.com-云顶娱乐网址4118112
做最好的网站
您的位置:云顶娱乐官方入口 > 云顶娱乐网址4118112 > 通过orm将编程语言的对象模型和数据库的关系模

通过orm将编程语言的对象模型和数据库的关系模

2019-10-07 21:12

本节内容

  1. ORM介绍
  2. sqlalchemy安装
  3. sqlalchemy基本使用
  4. 多外键关联
  5. 多对多关系
  6. 表结构设计作业   

Python开发【十二章】:ORM sqlalchemy,ormsqlalchemy

一、对象映射关系(ORM)

orm英文全称object relational mapping,就是对象映射关系程序,简单来说我们类似python这种面向对象的程序来说一切皆对象,但是我们使用的数据库却都是关系型的,为了保证一致的使用习惯,通过orm将编程语言的对象模型和数据库的关系模型建立映射关系,这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了,而不用直接使用sql语言

优点:

  • 隐藏了数据访问细节,“封闭”的通用数据库交互,ORM的核心。他使得我们的通用数据库交互变得简单易行,并且完全不用考虑该死的SQL语句。快速开发,由此而来
  • ORM使我们构造固化数据结构变得简单易行

缺点:

  • 无可避免的,自动化意味着映射和关联管理,代价是牺牲性能(早期,这是所有不喜欢ORM人的共同点)。现在的各种ORM框架都在尝试使用各种方法来减轻这块(LazyLoad,Cache),效果还是很显著的

 

二、SQLAlchemy

在Python中,最有名的ORM框架是SQLAlchemy。用户包括openstack\Dropbox等知名公司或应用

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:[email protected]:port/dbname[?key=value&key=value...]

更多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html

注:支持连接MySQL、Oracles数据库

安装:

pip install SQLAlchemy
pip install pymysql  
#由于mysqldb依然不支持py3,所以这里我们用pymysql与sqlalchemy交互

1、基本使用

查看mysql基础知识移步-》》http://www.cnblogs.com/lianzhilei/p/5993282.html

下面就开始让你见证orm的nb之处,盘古开天劈地之前,我们创建一个表是这样的:

CREATE TABLE user (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    password VARCHAR(64),
    PRIMARY KEY (id)
)

这只是最简单的sql表,如果再加上外键关联什么的,一般程序员的脑容量是记不住那些sql语句的,于是有了orm,实现上面同样的功能,代码如下:

# 创建表结构

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8",echo=True)      #echo=True 打印程序运行详细信息

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = "user"      #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

Base.metadata.create_all(engine)    #创建表结构

# 打印输出
# 2016-10-26 08:42:02,619 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
# 2016-10-26 08:42:02,619 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,622 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
# 2016-10-26 08:42:02,622 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,624 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
# 2016-10-26 08:42:02,624 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,649 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
# 2016-10-26 08:42:02,649 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,651 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
# 2016-10-26 08:42:02,651 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,652 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) 
# COLLATE utf8_bin AS anon_1
# 2016-10-26 08:42:02,652 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,655 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
# 2016-10-26 08:42:02,655 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,657 INFO sqlalchemy.engine.base.Engine ROLLBACK
# 2016-10-26 08:42:02,660 INFO sqlalchemy.engine.base.Engine
# CREATE TABLE user (
#  id INTEGER NOT NULL AUTO_INCREMENT,
#  name VARCHAR(32),
#  password VARCHAR(64),
#  PRIMARY KEY (id)
# )
#
#
# 2016-10-26 08:42:02,660 INFO sqlalchemy.engine.base.Engine {}
# 2016-10-26 08:42:02,904 INFO sqlalchemy.engine.base.Engine COMMIT

 查询表结构:

mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32) | YES  |     | NULL    |                |
| password | varchar(64) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec) 

你说,娘那个腚的,并没有感觉代码量变少啊,呵呵, 孩子莫猴急,好戏在后面

除上面的创建之外,还有一种创建表的方式,虽不常用,但还是看看吧

图片 1

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper

metadata = MetaData()

user = Table('user', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50)),
            Column('fullname', String(50)),
            Column('password', String(12))
        )

class User(object):
    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

mapper(User, user) #the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function

另一种方式

 

2、创建数据

最基本的表我们创建好了,那我们开始用orm创建一条数据试试

# 创建表数据

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8")

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = "user"      #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

#Base.metadata.create_all(engine)    #创建表结构

Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例,而是类
Session = Session_class()   #生成Session实例

user_obj = User(name="lzl",password="123456")   #生成你要创建的数据对象
print(user_obj.name,user_obj.id)  #此时还没创建对象呢,不信你打印一下id发现还是None

Session.add(user_obj) #把要创建的数据对象添加到这个session里, 一会统一创建
print(user_obj.name,user_obj.id) #此时也依然还没创建

Session.commit()    #现此才统一提交,创建数据

# lzl None
# lzl None

我擦,写这么多代码才创建一条数据,你表示太tm的费劲了,正要转身离开,我拉住你的手不放开,高潮快来了。。

 

3、查询数据

# 查询

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8")

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = "user"      #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例,而是类
Session = Session_class()   #生成Session实例

my_user = Session.query(User).filter_by(name="lzl").first()
print(my_user)          #my_user此时是一个对象
#<__main__.User object at 0x03EFC6D0>

print(my_user.id,my_user.name,my_user.password)
# 1 lzl 123456

不过刚才上面的显示的内存对象对址你是没办法分清返回的是什么数据的,除非打印具体字段看一下,如果想让它变的可读,只需在定义表的类下面加上这样的代码:

def __repr__(self):
  return "<User(name='%s',  password='%s')>" % (
  self.name, self.password)

print(my_user)
#<User(name='lzl',  password='123456')>

获取所有数据:

#当前数据表信息

mysql> select * from user;
+----+------------+----------+
| id | name       | password |
+----+------------+----------+
|  1 | lianzhilei | 123456   |
|  2 | lzl        | 123456   |
|  3 | lzl        | 123456   |
|  6 | alex       | 34567    |
|  7 | alex       | 34567    |
|  8 | wupeiqi    | 33422    |
+----+------------+----------+
6 rows in set (0.00 sec)

print(Session.query(User.name,User.id).all() )
# [('lianzhilei', 1), ('lzl', 2), ('lzl', 3), ('alex', 6), ('alex', 7), ('wupeiqi', 8)]

多条件查询:

# 多条件查询

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8")

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = "user"      #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例,而是类
Session = Session_class()   #生成Session实例

objs = Session.query(User).filter(User.id>3).filter(User.id<8).all()
print(objs)
# [<User(name='alex',  password='34567')>, <User(name='alex',  password='34567')>]

统计:

# 统计

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8")

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = "user"      #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例,而是类
Session = Session_class()   #生成Session实例

print(Session.query(User).filter(User.name.like("l%")).count())
#3

分组:

# 分组

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8")

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = "user"      #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例,而是类
Session = Session_class()   #生成Session实例

print(Session.query(func.count(User.name),User.name).group_by(User.name).all())
#[(2, 'alex'), (1, 'lianzhilei'), (2, 'lzl'), (1, 'wupeiqi')]

相当于原生sql为

SELECT count(user.name) AS count_1, user.name AS user_name
FROM user GROUP BY user.name

 

4、修改

# 修改

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8")

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = "user"      #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __str__(self):
        return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例,而是类
Session = Session_class()   #生成Session实例

my_user = Session.query(User).filter_by(name="lzl").first()
print(my_user)
#<User(name='lzl',  password='123456')>

my_user.name = "lianzhilei"

Session.commit()    #提交数据


mysql> select * from user;
+----+------------+----------+
| id | name       | password |
+----+------------+----------+
|  1 | lianzhilei | 123456   |
|  2 | lzl        | 123456   |
|  3 | lzl        | 123456   |
+----+------------+----------+
3 rows in set (0.00 sec)

 

5、回滚

# 回滚

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8")

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = "user"      #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

Session_class = sessionmaker(bind=engine)   #Session_class现在不是实例,而是类
Session = Session_class()   #生成Session实例

my_user = Session.query(User).filter_by(id = 1).first()
my_user.name = "Jack"
print(my_user)
# <User(name='Jack',  password='123456')>

Rain_user = User(name="Rain",password="12345")
Session.add(Rain_user)
print(Session.query(User).filter(User.name.in_(["Jack","Rain"])).all()) # 这时看session里有你刚添加和修改的数据
#[<User(name='Jack',  password='123456')>, <User(name='Rain',  password='12345')>]

Session.rollback()

print(Session.query(User).filter(User.name.in_(["Jack","Rain"])).all())  #再查就发现刚才添加的数据没有了。
#[]

 

 6、外键关联

我们先创建个study_record表与student进行关联

# 外键关联

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.0.59/lzl",
                       encoding="utf-8")

Base = declarative_base()

class Student(Base):
    __tablename__ ="student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_date = Column(DATE,nullable=False)

    def __repr__(self):
        return "<%s name:%s>"%(self.id,self.name)

class StudyRecord(Base):
    __tablename__ = "study_record"
    id = Column(Integer,primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey("student.id")) #关联student表里的id

    my_student = relationship("Student",backref="my_study_record") # Student为关联的类 

    def __repr__(self):
        return "<%s name:%s>" % (self.id, self.name)

Base.metadata.create_all(engine)

Session_class = sessionmaker(bind=engine)
session = Session_class()

s1 = Student(name="lzl",register_date="2016-10-26")
s2 = Student(name="alex",register_date="2015-10-26")
s3 = Student(name="eric",register_date="2014-10-26")
s4 = Student(name="rain",register_date="2013-10-26")

r1 = StudyRecord(day=1,status="YES",stu_id=1)
r2 = StudyRecord(day=2,status="No",stu_id=1)
r3 = StudyRecord(day=3,status="YES",stu_id=1)
r4 = StudyRecord(day=1,status="YES",stu_id=2)

session.add_all([s1,s2,s3,s4,r1,r2,r3,r4])
session.commit()

注:my_student = relationship("Student",backref="my_study_record")这个nb,允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项

查询:

# 外键查询

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.0.59/lzl",
                       encoding="utf-8")

Base = declarative_base()

class Student(Base):
    __tablename__ ="student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_date = Column(DATE,nullable=False)

    def __repr__(self):
        return "<id:%s name:%s>"%(self.id,self.name)

class StudyRecord(Base):
    __tablename__ = "study_record"
    id = Column(Integer,primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey("student.id")) #关联student表里的id

    my_student = relationship("Student",backref="my_study_record") # Student为关联的类

    def __repr__(self):
        return "<name:%s day:%s status:%s>" % (self.my_student.name,self.day,self.status)

Base.metadata.create_all(engine)

Session_class = sessionmaker(bind=engine)
session = Session_class()

stu_obj = session.query(Student).filter(Student.name=="lzl").first()
print(stu_obj)
#<id:1 name:lzl>

print(stu_obj.my_study_record)
#[<name:lzl day:1 status:YES>, <name:lzl day:2 status:No>, <name:lzl day:3 status:YES>]

  

 7、多外键关联

下表中,Customer表有2个字段都关联了Address表,首先先创建表结构

# 多外键关联

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

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8",echo= True)

Base =  declarative_base()

class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    billing_address_id = Column(Integer,ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address",foreign_keys=[billing_address_id]) #必须写foreign_keys
    shipping_address = relationship("Address",foreign_keys=[shipping_address_id])

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String(32))
    city = Column(String(32))
    state = Column(String(32))

Base.metadata.create_all(engine)


mysql> desc address;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| street | varchar(32) | YES  |     | NULL    |                |
| city   | varchar(32) | YES  |     | NULL    |                |
| state  | varchar(32) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc customer;
+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| id                  | int(11)     | NO   | PRI | NULL    | auto_increment |
| name                | varchar(32) | YES  |     | NULL    |                |
| billing_address_id  | int(11)     | YES  | MUL | NULL    |                |
| shipping_address_id | int(11)     | YES  | MUL | NULL    |                |
+---------------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

生成数据:

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

a1 = Address(street="Tiantongyuan",city="ChangPing",state="BJ")
a2 = Address(street="Wudaokou",city="HaiDian",state="BJ")
a3 = Address(street="Yanjiao",city="LangFang",state="HB")

session.add_all([a1,a2,a3])
c1 = Customer(name="lzl",billing_address_id=1,shipping_address_id=2)
c2 = Customer(name="Alex",billing_address_id=3,shipping_address_id=3)

session.add_all([c1,c2])

session.commit()

查询数据:

class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    billing_address_id = Column(Integer,ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address",foreign_keys=[billing_address_id]) #必须写foreign_keys
    shipping_address = relationship("Address",foreign_keys=[shipping_address_id])

    def __repr__(self):
        return "<name:%s billing_add:%s shipping_add:%s>"%(self.name,self.billing_address.street,
                                                           self.shipping_address.street)


#Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

cus_obj = session.query(Customer).filter_by(name="lzl").first()
print(cus_obj)
# <name:lzl billing_add:Tiantongyuan shipping_add:Wudaokou>

  

 8、多对多关联

现在来设计一个能描述“图书”与“作者”的关系的表结构,需求是

创建表结构:

#一本书可以有多个作者,一个作者又可以出版多本书


from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl",
                       encoding="utf-8")

Base = declarative_base()

#创建book_m2m_author表,表不用用户操作,系统自动维护,自动添加数据
book_m2m_author = Table('book_m2m_author', Base.metadata,
                        Column('book_id',Integer,ForeignKey('books.id')),
                        Column('author_id',Integer,ForeignKey('authors.id')),
                        )

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    #关联Author类,secondary表示通过book_m2m_author表进行查询关联数据,backref反向查询也一样
    authors = relationship('Author',secondary=book_m2m_author,backref='books')


    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    def __repr__(self):
        return self.name

Base.metadata.create_all(engine)

创建表数据: 

#创建数据

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

b1 = Book(name="learn python with Alex",pub_date="2014-05-02")
b2 = Book(name="learn linux with Alex",pub_date="2015-05-02")
b3 = Book(name="learn go with Alex",pub_date="2016-05-02")

a1 = Author(name="Alex")
a2 = Author(name="Jack")
a3 = Author(name="Rain")

#关键来了,创建关联关系
b1.authors = [a1,a3]
b3.authors = [a1,a2,a3]

session.add_all([b1,b2,b3,a1,a2,a3])
session.commit()


mysql> select * from book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       1 |         1 |
|       2 |         1 |
|       2 |         2 |
|       1 |         3 |
|       2 |         3 |
+---------+-----------+
5 rows in set (0.00 sec)

mysql> select * from authors;
+----+------+
| id | name |
+----+------+
|  1 | Alex |
|  2 | Jack |
|  3 | Rain |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from books;
+----+------------------------+------------+
| id | name                   | pub_date   |
+----+------------------------+------------+
|  1 | learn python with Alex | 2014-05-02 |
|  2 | learn go with Alex     | 2016-05-02 |
|  3 | learn linux with Alex  | 2015-05-02 |
+----+------------------------+------------+
3 rows in set (0.00 sec)

查询:

author_obj = session.query(Author).filter_by(name="Alex").first()
print(author_obj,author_obj.books)

book_obj = session.query(Book).filter_by(id=2).first()
print(book_obj,book_obj.authors)


# Alex [learn python with Alex, learn go with Alex]
# learn go with Alex [Alex, Jack, Rain]

  

9、多对多删除

删除数据时不用管boo_m2m_authors , sqlalchemy会自动帮你把对应的数据删除

通过书删除作者

author_obj =s.query(Author).filter_by(name="Jack").first()

book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()

book_obj.authors.remove(author_obj) #从一本书里删除一个作者
s.commit()

直接删除作者 

删除作者时,会把这个作者跟所有书的关联关系数据也自动删除

author_obj =s.query(Author).filter_by(name="Alex").first()
# print(author_obj.name , author_obj.books)
s.delete(author_obj)
s.commit()

 

10、中文

engine = create_engine("mysql+pymysql://root:[email protected]@192.168.20.219/lzl?charset=utf8")


#修改查看数据库字符编码
mysql> alter database lzl character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql>  show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |

 

http://www.bkjia.com/Pythonjc/1168708.htmlwww.bkjia.comtruehttp://www.bkjia.com/Pythonjc/1168708.htmlTechArticlePython开发【十二章】:ORM sqlalchemy,ormsqlalchemy 一、对象映射关系(ORM) orm英文全称object relational mapping,就是对象映射关系程序,简单来说我...

一、ORM介绍

如果写程序用pymysql和程序交互,那是不是要写原生sql语句。如果进行复杂的查询,那sql语句就要进行一点一点拼接,而且不太有重用性,扩展不方便。而且写的sql语句可能不高效,导致程序运行也变慢。 
为了避免把sql语句写死在代码里,有没有一种方法直接把原生sql封装好了并且以你熟悉的方式操作,像面向对象那样? 
  
orm(object relational mapping),就是对象映射关系程序,简单来说我们类似python这种面向对象的程序来说一切皆对象,但是我们使用的数据库却都是关系型的,为了保证一致的使用习惯,通过orm将编程语言的对象模型和数据库的关系模型建立映射关系,这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了,而不用直接使用sql语言。

ORM 相当于把数据库也给你实例化了,在代码操作mysql中级又加了orm这一层。 
orm的优点:

  1. 隐藏了数据访问细节,“封闭”的通用数据库交互,ORM的核心。他使得我们的通用数据库交互变得简单易行,并且完全不用考虑该死的SQL语句。快速开发,由此而来。
  2. ORM使我们构造固化数据结构变得简单易行。

缺点:

  1. 无可避免的,自动化意味着映射和关联管理,代价是牺牲性能(早期,这是所有不喜欢ORM人的共同点)。现在的各种ORM框架都在尝试使用各种方法来减轻这块(LazyLoad,Cache),效果还是很显著的。

二、sqlalchemy安装

在Python中,最有名的ORM框架是SQLAlchemy。用户包括openstack\Dropbox等知名公司或应用,主要用户列表http://www.sqlalchemy.org/organizations.html#openstack

安装sqlalchemy

pip install SQLAlchemy
pip install pymysql
# 由于mysqldb依然不支持py3,所以这里我们用pymysql与sqlalchemy交互

 

Centos7 安装mariadb-server

yum -y install mariadb-server  # 安装
systemctl start  mariadb.service  # 启动
mysqladmin -uroot -p password  # 设密码
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '123456' WITH GRANT OPTION;  # 授权
flush privileges;

三、sqlalchemy基本使用

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...]

 

1、创建表

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
# 创建实例,并连接test库
engine = create_engine("mysql+pymysql://root:123456@localhost/test",
                                    encoding='utf-8', echo=True)
# echo=True 显示信息
Base = declarative_base()  # 生成orm基类

class User(Base):
    __tablename__ = 'user'  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

Base.metadata.create_all(engine) #创建表结构 (这里是父类调子类)

运行,显示相关信息,包括生成的sql语句:

CREATE TABLE user (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(32), 
    password VARCHAR(64), 
    PRIMARY KEY (id)
)

 

并没有感觉代码量变少啊, 莫着急,好戏在后面

除上面的创建之外,还有一种创建表的方式

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper

metadata = MetaData()

user = Table('user', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50)),
            Column('fullname', String(50)),
            Column('password', String(12))
        )

class User(object):
    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

mapper(User, user)  # 类User 和 user关联起来
# the table metadata is created separately with the Table construct, 
# then associated with the User class via the mapper() function
# 如果数据库里有,就不会创建了。

 

最基本的表我们创建好了,那我们开始用orm创建一条数据试试

2、插入一条数据

from sqlalchemy import create_engine
from sqlalchemy import Table, MetaData, Column, Integer, String
from sqlalchemy.orm import mapper, sessionmaker
# 创建实例,并连接test库
engine = create_engine("mysql+pymysql://root:123456@localhost/test",
                                    encoding='utf-8', echo=True)

metadata = MetaData()

user = Table('user', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50)),
            Column('password', String(12))
        )

class User(object):
    def __init__(self, name, id, password):
        self.id = id
        self.name = name
        self.password = password
# the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function
mapper(User, user)

# 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session_class = sessionmaker(bind=engine)  # 实例和engine绑定
Session = Session_class()  # 生成session实例,相当于游标

user_obj = User(id=27,name="fgf",password="123456")  # 生成你要创建的数据对象
print(user_obj.name,user_obj.id)  # 此时还没创建对象呢,不信你打印一下id发现还是None

Session.add(user_obj)  # 把要创建的数据对象添加到这个session里, 一会统一创建
print(user_obj.name,user_obj.id) #此时也依然还没创建

Session.commit() #现此才统一提交,创建数据

 

写这么多代码才创建一条数据,你表示太tm的费劲了,正要转身离开,我拉住你的手不放开,高潮还没到。。

3.1、查询

my_user = Session.query(User).filter_by(name="fgf").first()  # 查询
print(my_user)

此时你看到的输出是这样的应该

<__main__.User object at 0x7f0a5a3dea20>

 

sqlalchemy把返回的数据映射成一个对象啦,这样你调用每个字段就可以跟调用对象属性一样啦,like this..

print(my_user.id,my_user.name,my_user.password)
# 输出
27 fgf 123456

 

不过刚才显示的内存对象对址没办法分清返回的是什么数据的,除非打印具体字段看一下,如果想让它变的可读,只需在定义表的类下面加上这样的代码

def __repr__(self):
    return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

 

from sqlalchemy import create_engine
from sqlalchemy import Table, MetaData, Column, Integer, String
from sqlalchemy.orm import mapper, sessionmaker
# 创建实例,并连接test库
engine = create_engine("mysql+pymysql://root:123456@localhost/test",
                                    encoding='utf-8', echo=True)

metadata = MetaData()

user = Table('user', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50)),
            Column('password', String(12))
        )
class User(object):
    def __init__(self, name, id, password):
        self.id = id
        self.name = name
        self.password = password
    def __repr__(self):
        return "<User(name='%s',  password='%s')>" % (self.name, self.password)

mapper(User, user)
# 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session_class = sessionmaker(bind=engine)
Session = Session_class()  # 生成session实例

my_user = Session.query(User).filter_by(name="fgf").first()  # 查询第一个
# my_user = Session.query(User).filter_by().all()  # 查询所有
print(my_user)
# print(my_user.id,my_user.name,my_user.password)

# Session.commit()  #查询不需要commit

 

获取所有数据

print(Session.query(User.name,User.id).all() )

 

3.2、多条件查询

filter_by与filter

my_user1 = Session.query(User).filter(User.id>2).all()
my_user2 = Session.query(User).filter_by(id=27).all()  # filter_by相等用‘=’
my_user3 = Session.query(User).filter(User.id==27).all()  # filter相等用‘==’

print(my_user1,'n',my_user2,'n',my_user3)

 

多条件查询

objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()

 

上面2个filter的关系相当于 user.id >1 AND user.id <7 的效果

4、修改

my_user = Session.query(User).filter_by(name="fgf").first()
my_user.name = "fenggf"  # 查询出来之后直接赋值修改
my_user.passwork = "123qwe"
Session.commit()

 

5、回滚

my_user = Session.query(User).filter_by(id=1).first()
my_user.name = "Jack"

fake_user = User(name='Rain', password='12345')
Session.add(fake_user)

print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() )  #这时看session里有你刚添加和修改的数据

Session.rollback() #此时你rollback一下

print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。

# Session
# Session.commit()

 

6、统计和分组

统计 count

Session.query(User).filter(User.name.like("f%")).count()  # mysql不区分大小写

 

分组 group_by

from sqlalchemy import func
print(Session.query(User.name,func.count(User.name)).group_by(User.name).all() )

 

删除 del

7.1、外键关联

准备工作:先创建一个表,再插入数据

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
# 创建实例,并连接test库
engine = create_engine("mysql+pymysql://root:123456@localhost/test",
                                    encoding='utf-8', echo=True)

Base = declarative_base()  # 生成orm基类

class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    stu_id = Column(Integer)
    age = Column(Integer)  # 整型
    gender = Column(Enum('M','F'),nullable=False)

Base.metadata.create_all(engine) #创建表结构 (这里是父类调子类)

# 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session_class = sessionmaker(bind=engine)
Session = Session_class()  # 生成session实例
stu_obj = Student(stu_id=27, age=22, gender="M")
Session.add(stu_obj)
Session.commit() #现此才统一提交,创建数据

 

连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

# 以下两种 必须表之间有外键关联才能查
ret = session.query(Person).join(Favor).all()  
ret = session.query(Person).join(Favor, isouter=True).all()

 

第一种示例:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Enum
from sqlalchemy.orm import sessionmaker
# 创建实例,并连接test库
engine = create_engine("mysql+pymysql://root:123456@localhost/test",
                                    encoding='utf-8')
Base = declarative_base()  # 生成orm基类

class User(Base):
    __tablename__ = 'user'  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
    def __repr__(self):
        return "[%s name:%s]" %(self.id, self.name)

class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    stu_id = Column(Integer)
    age = Column(Integer)  # 整型
    gender = Column(Enum('M','F'),nullable=False)
    def __repr__(self):
        return "[%s stu_id:%s sex:%s]" %(self.stu_id, self.age, self.gender)

Session_class = sessionmaker(bind=engine)
Session = Session_class()  # 生成session实例

res = Session.query(User, Student).filter(User.id == Student.stu_id).all()
print(res)

 

外键关联实现

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Enum,DATE,Integer, String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship

engine = create_engine("mysql+pymysql://root:123456@localhost/test",
                                    encoding='utf-8')
Base = declarative_base()  # 生成orm基类

class Stu2(Base):
    __tablename__ = "stu2"
    id = Column(Integer, primary_key=True)
    name = Column(String(32),nullable=False)
    register_date = Column(DATE,nullable=False)
    def __repr__(self):
        return "<%s name:%s>" % (self.id, self.name)

class StudyRecord(Base):
    __tablename__ = "study_record"
    id = Column(Integer, primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey("stu2.id"))  #------外键关联------
    #这个nb,允许你在user表里通过backref字段反向查出所有它在stu2表里的关联项数据
    stu2 = relationship("Stu2", backref="my_study_record")  # 添加关系,反查(在内存里)
    def __repr__(self):
        return "<%s day:%s status:%s>" % (self.stu2.name, self.day,self.status)

Base.metadata.create_all(engine)  # 创建表结构

Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
session = Session_class()  # 生成session实例 #cursor

s1 = Stu2(name="A",register_date="2014-05-21")
s2 = Stu2(name="J",register_date="2014-03-21")
s3 = Stu2(name="R",register_date="2014-02-21")
s4 = Stu2(name="E",register_date="2013-01-21")

study_obj1 = StudyRecord(day=1,status="YES", stu_id=1)
study_obj2 = StudyRecord(day=2,status="NO", stu_id=1)
study_obj3 = StudyRecord(day=3,status="YES", stu_id=1)
study_obj4 = StudyRecord(day=1,status="YES", stu_id=2)

session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4])  # 创建
session.commit()

stu_obj = session.query(Stu2).filter(Stu2.name=="a").first()  # 查询
# 在stu2表,查到StudyRecord表的记录
print(stu_obj.my_study_record)  # 查询A一共上了几节课

 

可以查看创建命令:

show create table study_record;

 

7.2、多外键关联

多外键关联,并且关联同一个表。 
下表中,Customer表有2个字段都关联了Address表 
cat orm_many_fk.py

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

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    # 账单地址和邮寄地址 都关联同一个地址表
    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String(64))
    city = Column(String(64))
    state = Column(String(64))
    def __repr__(self):
        return self.street

engine = create_engine("mysql+pymysql://root:123456@localhost/test",
                                    encoding='utf-8')
Base.metadata.create_all(engine)  # 创建表结构

 

MariaDB [test]> show create table customer;
| customer | CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `billing_address_id` int(11) DEFAULT NULL,
  `shipping_address_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `billing_address_id` (`billing_address_id`),
  KEY `shipping_address_id` (`shipping_address_id`),
  CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`billing_address_id`) REFERENCES `address` (`id`),
  CONSTRAINT `customer_ibfk_2` FOREIGN KEY (`shipping_address_id`) REFERENCES `address` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

MariaDB [test]> desc customer;
+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| id                  | int(11)     | NO   | PRI | NULL    | auto_increment |
| name                | varchar(64) | YES  |     | NULL    |                |
| billing_address_id  | int(11)     | YES  | MUL | NULL    |                |
| shipping_address_id | int(11)     | YES  | MUL | NULL    |                |
+---------------------+-------------+------+-----+---------+----------------+

 

正常写的时候,表结构单独写一个模块。添加数据

import orm_many_fk
from sqlalchemy.orm import sessionmaker

Session_class = sessionmaker(bind=orm_many_fk.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
session = Session_class()  # 生成session实例 #cursor

addr1 = orm_many_fk.Address(street="Tiantongyuan", city="ChangPing", state="BJ")
addr2 = orm_many_fk.Address(street="Wudaokou", city="Haidian", state="BJ")
addr3 = orm_many_fk.Address(street="Yanjiao", city="LangFang", state="HB")

session.add_all([addr1,addr2,addr3])
c1 =  orm_many_fk.Customer(name="Fgf", billing_address= addr1,shipping_address=addr2)
c2 =  orm_many_fk.Customer(name="Jack", billing_address= addr3,shipping_address=addr3)

session.add_all([c1,c2])
session.commit()

obj = session.query(orm_many_fk.Customer).filter(orm_many_fk.Customer.name=="Fgf").first()
print(obj.name,obj.billing_address,obj.shipping_address)  # 查询

 

7.3 多对多关系

现在来设计一个能描述“图书”与“作者”的关系的表结构,需求是

  1. 一本书可以有好几个作者一起出版
  2. 一个作者可以写好几本书 
    此时你会发现,用之前学的外键好像没办法实现上面的需求了 
    那怎么办呢? 此时,我们可以再搞出一张中间表,就可以了 
    这样就相当于通过book_m2m_author表完成了book表和author表之前的多对多关联 
    双向一对多,就是多对多。 
    用orm如何表示呢?
# 创建表结构
from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
# 第三张表 自己创建。不需要手动管理,orm自动维护
book_m2m_author = Table('book_m2m_author', Base.metadata,
                        Column('book_id',Integer,ForeignKey('books.id')),
                        Column('author_id',Integer,ForeignKey('authors.id')),
                        )
class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    # book表不知道第三张表,所以关联一下第三张表
    authors = relationship('Author',secondary=book_m2m_author,backref='books')
    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    def __repr__(self):
        return self.name

engine = create_engine("mysql+pymysql://root:123456@localhost/test",
                                    encoding='utf-8')
Base.metadata.create_all(engine)  # 创建表结构
MariaDB [test]> desc book_m2m_author;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| book_id   | int(11) | YES  | MUL | NULL    |       |
| author_id | int(11) | YES  | MUL | NULL    |       |
+-----------+---------+------+-----+---------+-------+
# 做了复合键了

 

# 添加数据
import orm_m2m
from sqlalchemy.orm import sessionmaker

Session_class = sessionmaker(bind=orm_m2m.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
session = Session_class()  # 生成session实例 #cursor
# 创建书
b1 = orm_m2m.Book(name="learn python with Alex",pub_date="2014-05-2")
b2= orm_m2m.Book(name="learn Zhangbility with Alex",pub_date="2015-05-2")
b3 = orm_m2m.Book(name="Learn hook up girls with Alex",pub_date="2016-05-2")
# 创建作者
a1 = orm_m2m.Author(name="Alex")
a2 = orm_m2m.Author(name="Jack")
a3 = orm_m2m.Author(name="Rain")
# 关联关系
b1.authors = [a1,a3]
b3.authors = [a1,a2,a3]

session.add_all([b1,b2,b3,a1,a2,a3])
session.commit()

 

# 重要是查询
author_obj = session.query(orm_m2m.Author).filter(orm_m2m.Author.name=="alex").first()
print(author_obj.books[0:])
book_obj = session.query(orm_m2m.Book).filter(orm_m2m.Book.id==2).first()
print(book_obj.authors)

 

多对多删除 
删除数据时不用管boo_m2m_authors , sqlalchemy会自动帮你把对应的数据删除

通过书删除作者

author_obj =s.query(Author).filter_by(name="Jack").first()
book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()
book_obj.authors.remove(author_obj) #从一本书里删除一个作者
s.commit()

 

直接删除作者 

删除作者时,会把这个作者跟所有书的关联关系数据也自动删除

author_obj =s.query(Author).filter_by(name="Alex").first()
# print(author_obj.name , author_obj.books)
s.delete(author_obj)
s.commit()

 

8、中文问题

先查看数据库的字符集

MariaDB [test]> show create database test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+

 

修改数据库的字符集(如果修改字符集,添加仍不显示中文,可能就需要创建是指定尝试一下了。)

MariaDB [test]> alter database test character set utf8;

 

创建数据库指定数据库的字符集

MariaDB [test]> create database mydb character set utf8;

 

sqlalchemy 连接指定

engine = create_engine("mysql+pymysql://root:123456@localhost/test?charset=utf8",)

 

转自:http://blog.csdn.net/fgf00/article/details/52949973

本文由云顶娱乐官方入口发布于云顶娱乐网址4118112,转载请注明出处:通过orm将编程语言的对象模型和数据库的关系模

关键词: