小工具      在线工具  汉语词典  css  js  c++  java

python操作mysql数据库

Python,python 额外说明

收录于:157天前

Python DB-API操作数据库

Python的标准数据库接口是Python DB-API,它为开发人员提供了数据库应用程序编程接口。 Python数据库接口支持多种数据库:mysql、oracle。

不同的数据库需要下载不同的DB API模块。例如,如果您需要访问Oracle数据库和MySQL数据,则需要下载Oracle和MySQL数据库模块。

Python 的 DB-API 实现了大多数数据库的接口。使用它连接各个数据库后,就可以对各个数据库进行同样的操作。

在这里插入图片描述

使用python DB-API操作流程

在这里插入图片描述

可见,接口操作数据的核心是创建连接对象。

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

如上导入了mysql.connector第三方库,需要安装pip install mysql-connectorpip 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语句。

共同属性
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表,已有就忽略。数据库表模型类通过```表名````和表关联起来是基本属性,Column表示数据表的列。

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

  • 创建数据库会话

Session 在 sqlalchemy 中用于在程序和数据库之间创建会话。所有对象的加载和保存都需要会话对象。

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有filterfilter_by两个过滤方法,上述例子也可写为:

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

filter_byfilter的区别:
在这里插入图片描述
在过滤属性时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绑定仍然要用类名.属性,相等用 == 不支持联合查询 '''

filterfilter_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

. . .

相关推荐

额外说明

PostgreSQL数据库计划任务扩展,计划任务(pg_cron)。什么是 pg_cron?

PostgreSQL不自带定时任务功能,但是可以安装第三方的扩展或者使用操作系统的cron,第三方扩展使用比较多的有pgAgent和pg_cron . . 本文来源: https://github.com/citusdata/pg_cron What i

额外说明

leetcode665(非递减序列:数组遍历)

给你一个长度为 n 的整数数组,请你判断在 最多 改变 1 个元素的情况下,该数组能否变成一个非递减数列。 我们是这样定义一个非递减数列的: 对于数组中所有的 i (0 <= i <= n-2),总满足 nums[i] <= nums[i + 1]。 示

额外说明

MySQL约束和查询

约束和查询 1. 约束 1.1 约束类型 1.2 常用的约束 2. 查询 2.1 聚合查询 2.1.1 聚合函数 2.1.2 GROUP BY 2.1.3 HAVING 2.2 联合查询 2.2.1 内连接 2.2.2 外连接 2.3 合并查询 1. 约

额外说明

【100个 Unity踩坑小知识点】| Unity调用API ,动态获取Android权限,附带所有Android权限表格

Unity 小科普 老规矩,先介绍一下 Unity 的科普小知识: Unity是 实时3D互动内容创作和运营平台 。 包括游戏开发、美术、建筑、汽车设计、影视在内的所有创作者,借助 Unity 将创意变成现实。 Unity 平台提供一整套完善的软件解决方

额外说明

统计判别之模式分类(二)

注意:贝叶斯分类规则是基于统计概念的,如果只有少数模式样本,一般较难获得最优的结果 正态分布模式的贝叶斯分类器       当已知或者有理由设想类概率密度函数P(x|ωi)是多变量的正态分布时,上一节介绍的贝叶斯分类器可以导出一些简单的判别函数。 由于正

额外说明

CentOS Linux 设置汉语拼音输入法的方法有哪些?7种适用于CentOS的汉语拼音输入法

CentOS Linux 设置汉语拼音输入法的方法有哪些?7种适用于CentOS的汉语拼音输入法 文章目录 CentOS Linux 设置汉语拼音输入法的方法有哪些?7种适用于CentOS的汉语拼音输入法 1. Intelligent Pinyin 输入

额外说明

一、docker的介绍

一、虚拟化和容器 虚拟化介绍 操作系统层虚拟化是指通过划分一个宿主操作系统的特定部分,产生一个个隔离的操作执行环境。操作系统层的虚拟化是操作系统内核直接提供的虚拟化,虚拟出的操作系统之间共享底层宿主操作系统内核和底层的硬件资源。操作系统虚拟化的关键点在于

额外说明

wordpress简捷按钮_如何在WordPress中添加Skype共享按钮

wordpress 简单按钮 您知道 Skype 有一个共享按钮吗?我们也没有这样做,直到一位读者向我们索要有关如何在 WordPress 中添加 Skype 共享按钮的教程。 Skype 是世界上最受欢迎的通讯应用程序之一。在本文中,我们将向您展示如何

额外说明

Java多线程:单例模式

-栏目内容:Java 子夜的星的主页 -座右铭:前面的路并不远,一直走下去 目录 1.饿人模式2.惰性模式(单线程)3.惰性模式(多线程) 单例模式是最常见的设计模式之一。单例模式保证程序中某个类只有一个实例,而不需要创建多个实例。单例模式的具体实现方式

ads via 小工具