python_SQLALCHEM

最新推荐文章于 2024-05-10 18:02:59 发布

xyang_1128

最新推荐文章于 2024-05-10 18:02:59 发布

阅读量314

点赞数

分类专栏：数据库的学习 python 文章标签： mysql python sqlalchemy

数据库的学习同时被 2 个专栏收录

17 篇文章 1 订阅

订阅专栏

python

15 篇文章 0 订阅

订阅专栏

ORM介绍

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

orm的优点：

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

缺点:

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

2. sqlalchemy安装

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

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

sqlalchemy基本使用

sql创建table

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

使用sqlalchemy创建

_data_ = '2019/5/27 19:51'
_author = 'XiaoYang'

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:1039191520@localhost/book",
                       encoding='utf-8', echo=True)
# 生成基类
Base = declarative_base()

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

Base.metadata.create_all(engine)

Lazy Connecting
The Engine, when first returned by create_engine(), has not actually tried to connect to the database yet; that happens only the first time it is asked to perform a task against the database.　　

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

_data_ = '2019/5/27 19:51'
_author = 'XiaoYang'
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创建一条数据试试

_data_ = '2019/5/27 20:25'
_author = 'XiaoYang'


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

engine = create_engine("mysql+pymysql://root:1039191520@localhost/book",
                       encoding='utf-8', 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))


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

# session 会话
session = Session_class()


user = User(name='xiaoyang',password='123456')
print(user.name,user.id)  #此时还没创建对象呢，不信你打印一下id发现还是None
session.add(user)
print(user.name,user.id) #此时也依然还没创建


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

查询数据

my_user = session.query(User).filter_by(name="xiaoyang").first()

print(my_user)

修改

# modify
my_user = session.query(User).filter_by(name="xiaoyang").first()


if not  my_user is None:
    print("数据不为空")
    my_user.password = "456789"

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

回滚

# rollback
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() ) #再查就发现刚才添加的数据没有了。

多条件查询

my_user = session.query(User).filter(User.name == "xiaoyang").filter(User.password == "456789").all()
print(my_user)

统计和分组

统计

result = session.query(User).filter(User.name.like("%yang%")).count()
print(result)

分组

from sqlalchemy import func
result = session.query(func.count(User.name),User.name).group_by(User.name).all()

print(result)

输出结果如下

[(1, 'jiangshanqin'), (1, 'xiaoyang')]

相当于原生sql为

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

外键关联

我们创建一个addresses表，跟user表关联

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

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

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

The relationship.back_populates parameter is a newer version of a very common SQLAlchemy feature calledrelationship.backref. The relationship.backref parameter hasn’t gone anywhere and will always remain available! The relationship.back_populates is the same thing, except a little more verbose and easier to manipulate. For an overview of the entire topic, see the section Linking Relationships with Backref.　　

表结构关系如下

表创建好后，插入几条数据后,我们可以这样反查试试


user = session.query(User).filter(User.id==1).first()

for obj in user.addresses:
    print("--------------------",obj.email_address)



address = session.query(Address).filter(Address.id==1).first()



print(address.user.name)

创建关联对象

#  insert   foreign obj

user = session.query(User).filter(User.id==1).first()

user.addresses = [Address(email_address="r1@126.com"), #添加关联对象
                 Address(email_address="r2@126.com")]


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

多外键关联

One of the most common situations to deal with is when there are more than one foreign key path between two tables.

Consider a Customer class that contains two foreign keys to an Address class:

下表中，Customer表有2个字段都关联了Address表

_data_ = '2019/5/28 9:58'
_author = 'XiaoYang'

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

Base = declarative_base()

engine = create_engine("mysql+pymysql://root:1039191520@localhost/book",
                       encoding='utf-8', echo=True)


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))


Base.metadata.create_all(engine)

创建表结构是没有问题的，但你Address表中插入数据时会报下面的错

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join

condition between parent/child tables on relationship

Customer.billing_address - there are multiple foreign key

paths linking the tables. Specify the 'foreign_keys' argument,

providing a list of those columns which should be

counted as containing a foreign key reference to the parent table.

原因是sqlachemy无法通过Address 查询出 customer

解决办法如下

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)
 
    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])

这样sqlachemy就能分清哪个外键是对应哪个字段了

多对多关系

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

一本书可以有好几个作者一起出版
一个作者可以写好几本书

平常的一对多关系是处理不了的,所以我们需要搞出一张中间表，就可以了,把多对多拆分成俩个一对多

样就相当于通过book_m2m_author表完成了book表和author表之前的多对多关联

_data_ = '2019/5/28 10:38'
_author = 'XiaoYang'

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()

engine = create_engine("mysql+pymysql://root:1039191520@localhost/book",
                       encoding='utf-8', echo=True)



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)
    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)

接下来创建几本书和作者

_data_ = '2019/5/28 10:38'
_author = 'XiaoYang'

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()

engine = create_engine("mysql+pymysql://root:1039191520@localhost/book",
                       encoding='utf-8', echo=True)



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)
    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_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

# session 会话
session = Session_class()


b1 = Book(name="跟Alex学Python")
b2 = Book(name="跟Alex学把妹")
b3 = Book(name="跟Alex学装逼")
b4 = Book(name="跟Alex学开车")

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

b1.authors = [a1,a3]
b2.authors = [a1,a2,a3]


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

session.close()

查看数据表结构

查看数据

mysql> select * from books;
+----+------------------+----------+
| id | name             | pub_date |
+----+------------------+----------+
|  1 | 跟Alex学Python   | NULL     |
|  2 | 跟Alex学把妹     | NULL     |
|  3 | 跟Alex学装逼     | NULL     |
|  4 | 跟Alex学开车     | NULL     |
+----+------------------+----------+
4 rows in set (0.00 sec)
 
mysql> select * from authors;
+----+------+
| id | name |
+----+------+
| 10 | Alex |
| 11 | Jack |
| 12 | Rain |
+----+------+
3 rows in set (0.00 sec)
 
mysql> select * from book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       2 |        10 |
|       2 |        11 |
|       2 |        12 |
|       1 |        10 |
|       1 |        11 |
+---------+-----------+

多对多删除

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

通过书删除作者



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

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

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

直接删除作者

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

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

处理中文

sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式

eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)

xyang_1128

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
python_SQLALCHEM

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

扫一扫