投稿
  1. 极客之音首页
  2. 后端笔记

python操作mysql数据库

飞熊 • • 后端笔记 • 阅读 119

得意时要看淡，失意时要看开。不论得意失意，切莫大意;不论成功失败，切莫止步。志得意满时，需要的是淡然，给自己留一条退路;失意落魄时，需要的是泰然，给自己觅一条出路python操作mysql数据库，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

Python DB-API操作数据库

Python 标准数据库接口为 Python DB-API，Python DB-API为开发人员提供了数据库应用编程接口。Python 数据库接口支持非常多的数据库：mysql，oracle。

不同的数据库你需要下载不同的DB API模块，例如你需要访问Oracle数据库和Mysql数据，你需要下载Oracle和MySQL数据库模块。

Python的DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。

在这里插入图片描述

使用python DB-API操作流程

在这里插入图片描述

可以看出接口操作数据额核心是创建connection对象。

import mysql.connector
conn = mysql.connector.connect(
	user='root', 
	password='password', 
	database='db' , 
	auth_plugin='mysql_native_password'
)

如上导入了mysql.connector第三方库，需要安装pip install mysql-connector和pip install mysql-connector-python --allow-external mysql-connector-python

另外MySQLdb库也可以实现同样的功能。pip install MySQLdb 是python2版本。

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )
# 其他都一样

这是四个最基础的参数，最后一个auth_plugin是为解决高版本mysql8.0更改加密密码的措施。

PyMySQL也是python3的DB-API之一，库名不同但操作方法都是一样的。

# 安装
pip3 install PyMySQL

import pymysql
 
# 打开数据库连接
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")
 
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
 
print ("Database version : %s " % data)
 
# 关闭数据库连接
db.close()

操作方法都是一样的。

connecttion对象

在这里插入图片描述
在这里插入图片描述

cursor对象

常用方法
在这里插入图片描述
在这里插入图片描述

close():关闭此游标对象
fetchone():得到结果集的下一行
fetchmany([size = cursor.arraysize]):得到结果集的下几行
fetchall():得到结果集中剩下的所有行
excute(sql[, args]):执行一个数据库查询或命令
excutemany(sql, args):执行多个数据库查询或命令

执行sql语句都是使用cursor对象。

常用属性
connection:创建此游标对象的数据库连接
arraysize:使用fetchmany()方法一次取出多少条记录，默认为1
lastrowid:返回最后一个数据的id

其他方法
__iter__()：创建一个可迭代对象（可选）
next()：获取结果集的下一行（如果支持迭代的话）
nextset()：移到下一个结果集（如果支持的话）
callproc(func[,args])：调用一个存储过程
setinputsizes(sizes)：设置输入最大值（必须有，但具体实现是可选的）
setoutputsizes(sizes[,col])：设置大列 fetch 的最大缓冲区大小

其他属性
description：返回游标活动状态（包含7个元素的元组）：（name, type_code, display_size, internal_size, precision, scale, null_ok）只有 name 和 type_cose 是必需的
rowcount：最近一次 execute() 创建或影响的行数
messages：游标执行后数据库返回的信息元组（可选）
rownumber：当前结果集中游标所在行的索引（起始行号为 0）

https://www.jb51.net/article/104820.htm
http://www.zzvips.com/article/105426.html

增删查改案例

查

import mysql.connector

conn = mysql.connector.connect(user='root', password='baby5429', db='db1', auth_plugin='mysql_native_password')
cursor = conn.cursor()


def method():
    cursor.execute('select * from user')
    values = cursor.fetchall()

    print(values)
    cursor.close()

    conn.close()


if __name__ == "__main__":
    method()

在这里插入图片描述
excute(sql[, args]):执行一个数据库查询或命令,excutemany(sql, args):执行多个数据库查询或命令fetchone()需要借助循环。fetchall()查询所有。

删

def method():
    cursor.execute('delete from user where id = 4')
    values = cursor.rowcount

    print(values)

    cursor.close()

    # 提交事物
    conn.commit()
    conn.close()

DML操作最重要的就是提交事物。rowcount参数返回影响行数。

# 编程式事物管理
def method():
    cursor.execute('delete from user where id = 4')
    values = cursor.rowcount

    print(values)

    cursor.close()
    try:
        num = 5 / 0
        conn.commit()
    except Exception:
        conn.rollback()
    finally:
        conn.close

增
动态参数的占位符是%s。而不是python的其他占位符。

def method():
    cursor.execute('insert into user(username,password) values(%s,%s)',("zhansan1","123456"))
    values = cursor.rowcount

    print(values)

    cursor.close()
    try:
        #num = 5 / 0
        conn.commit()
    except Exception:
        conn.rollback()
    finally:
        conn.close

改和增等DML语句都是只更改sql语句，然后提交事务即可，动态参数的占位符是%s

SQLAlchemy实现ORM映射

ORM是将数据库查询对象映射为对象的技术，SQLAlchemy是python实现该技术的个框架。

python的DDL操作查询的结构如下：

在这里插入图片描述
显然是python的列表，每个元素是一个元组（有序不重复）

将该据库表的一行记录与一个对象互相做自动转换：

自定义转换

# user对象
class User(object):
    def __init__(self, id, username, password):
        self.__id = id
        self.__username = username
        self.__password = password

    def getUserName(self):
        return self.__username

    def setUserName(self, username):
        self.__username = username
        return None

    def getPassword(self):
        return self.__password

    def serPassword(self, password):
        self.__password = password
        return None


import mysql.connector
from mysqlTest.User import User
conn = mysql.connector.connect(user='root', password='baby5429', db='db1', auth_plugin='mysql_native_password')
cursor = conn.cursor()


def method():
    cursor.execute('select * from user')
    values = cursor.fetchall()

    print(values)
    cursor.close()
    try:
        # 存储结构
        users = []

        # 定义orm模型
        for i in values:
            user = User(i[0],i[1],[2])
            users.append(user)
    except Exception:
        conn.rollback()
    finally:
        conn.close

    print(users)


if __name__ == "__main__":
    method()

在这里插入图片描述

使用orm框架转换

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

# 创建对象的基类:
Base = declarative_base()

# 初始化数据库连接:

engine = create_engine("mysql+mysqlconnector://root:baby5429@localhost:3306/db1")

Base.metadata.create_all(engine)


# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    username = Column(String(20))
    password = Column(String(11))

    def __init__(self,id, username, password):
        self.id = id
        self.username = username
        self.password = password


# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)


session = DBSession()

if __name__ == "__main__":

    user1 = User("6","zhansan3","123456")
    session.add(user1)
    session.commit()

概念和数据类型
在这里插入图片描述
在这里插入图片描述

使用步骤

  • 安装
    pip install sqlalchemy该框架是配合之前的两个框架一起使用的。

  • 注册数据库驱动

from sqlalchemy import create_engine
 
engine = create_engine("mysql://user:password@hostname/dbname?charset=uft8")

create_engine()用来初始化数据库连接。SQLAlchemy用一个字符串表示连接信息：
'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'

数据库类型为mysql，数据库驱动为mysql-connecter。
还有额外的配置参数：

engine = create_engine("mysql://user:password@hostname/dbname?charset=uft8",
            echo=True,
            pool_size=8,
            pool_recycle=60*30
            )

echo: 当设置为True时会将orm语句转化为sql语句打印，一般debug的时候可用
pool_size: 连接池的大小，默认为5个，设置为0时表示连接无限制
pool_recycle: 设置时间以限制数据库多久没连接自动断开

  • 创建数据库映射类
from sqlalchemy import Column, String
from sqlalchemy.ext.declarative import declarative_base
# 构造基类
Base = declarative_base()

# 定义User对象
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    username = Column(String(20),unique=False)
    password = Column(String(11))

    def __init__(self,username, password):
        self.username = username
        self.password = password

base.metadata.create_all(engine)

declarative_base()是sqlalchemy内部封装的一个方法，通过其构造一个基类，这个基类和它的子类，可以将Python类和数据库表关联映射起来。base.metadata.create_all(engine)是创建表，在engine定义的数据中创建User表，已有就忽略。数据库表模型类通过“`tablename““和表关联起来是基本属性，Column表示数据表的列。

如果数据库没有表结构通过base.metadata.create_all(engine)可创建表。执行这段代码，就会发现在db中创建了users表。

  • 创建数据库会话

sqlalchemy中使用session用于创建程序和数据库之间的会话，所有对象的载入和保存都需要通过session对象 。

DBSession = sessionmaker(bind=engine)
session = DBSession()

session的常见操作方法包括：

flush：预提交，提交到数据库文件，还未写入数据库文件中
commit：提交了一个事务
rollback：回滚
close：关闭

增
add()方法添加，参数是类的实例对象

user1 = User("zhansan3","123456")
session.add(user1)
session.commit()

add_all() 方法批量添加

查

user = session.query(User).filter_by(id=1).all()
    for i in user:
        print(i)
        print(i.username)
        print(i.password)

在这里插入图片描述
query()方法查询，参数是基类的子类对象即映射的表对象。filter_by()方法是筛选，相当于where。此时还没有去具体的数据库中查询，只有当执行具体的.all()，.first()等函数时才会真的去操作数据库。前者返回所有数据，后者返回第一条数据。

user = session.query(User) 
    for i in user:
        print(i)
        print(i.username)
        print(i.password)

在这里插入图片描述

其中，query有filter和filter_by两个过滤方法，上述例子也可写为：

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

filter_by和filter的区别：
在这里插入图片描述
在过滤属性时filter必须用类名.属性，且不支持联合查询。filter_by可以直接用属性，支持联合查询。

# filter_by
users = session.query(Users).filter_by(id=1).all()
'''
query绑定对象后filter_by直接过滤属性即可，直接用=，!=，>，<。
'''


# filter
user = session.query(User).filter(User.id == 1).all()
'''
即使通过query绑定仍然要用类名.属性，相等用 == 不支持联合查询
'''

有filter和filter_by的语句在.all()或.first()后才会去操作数据库

改
更新数据有两种方法，一种是使用query中的update方法：

session.query(User).filter_by(id=8).update({'password': "qwerty"})
session.commit()

# update参数是一个字典类型

update的参数是一个字典类型。

另一种是操作对应的表模型，覆盖原有数据：

user = session.query(User).filter_by(id=8).first()
user.username = "lihua"
session.add(user)
session.commit()

add()方法会覆盖原有数据也达到修改目的。

删
在mysql中删除数据一般通过主键的字段将一整行删除delete from table where id =?，在SQLAlchemy中则是根据信息查出某一行，让后删掉：

user = session.query(User).filter(User.id == 8).first()
session.delete(user)
session.commit()

另外一种则是借助查询函数，将查询的结果直接删除：

session.query(User).filter(User.id == 7).delete()
session.commit()

使用这种方法不能加.first或.all，否则变成了返回结果。使用.delete()直接实现对数据库的操作。

https://www.jb51.net/article/173950.htm

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/156282.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！