python连接并操作mysql

最新推荐文章于 2024-08-12 23:11:19 发布

youthlost

最新推荐文章于 2024-08-12 23:11:19 发布

阅读量168

点赞数 1

分类专栏： python项目开发 MYSQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_35778860/article/details/100218041

版权

python项目开发同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

MYSQL

4 篇文章 0 订阅

订阅专栏

1 数据库简单操作
连接数据库之前，先使用 MySQL 客户端 SQLyog 手动创建好数据库和表：

# 创建数据库 
CREATE DATABASE db_study;
USE db_study;# 加USE,都加分号即可
# 创建表
CREATE TABLE tb_student
(
    id INT NOT NULL AUTO_INCREMENT,
    `name` CHAR(20) NOT NULL,
    gender CHAR(1) DEFAULT NULL,
    age INT DEFAULT NULL,
    score INT DEFAULT NULL,
    PRIMARY KEY(id)
)DEFAULT CHARSET = utf8;

在这里插入图片描述

2 insert 操作

import pymysql
db = pymysql.connect(host='10.52.165.111', user='root',
                      passwd='123456', db='db_study',charset='utf8')
cursor  = db.cursor()
sql = "insert into tb_student(name,gender,age,score) values('mike','F',21,90 )"
sql = "insert into tb_student(name,gender,age,score) values('jane','M',24,95 )"
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

在这里插入图片描述
3 select 操作

游标 Cursor 对象的属性和方法	描述
fetchone()	它用来获取使用游标对象来查询表时，返回的结果集对象中的下一行数据
fetchall()	它用来获取使用游标对象来查询表时，返回的结果集对象中的所有行数据
rowcount	只读属性，返回 execute() 方法影响的行数

import pymysql
db = pymysql.connect(host='10.52.165.111', user='root',
                      passwd = '123456', db='db_study', charset='utf8')
cursor = db.cursor()
sql_select = 'SELECT * FROM tb_student'
print(sql_select)
try:
    cursor.execute(sql_select)
    res = cursor.fetchone()
    print('fetchone ===> id = %s,name = %s,gender = %s,age = %d,score = %d ' % (res[0],res[1],res[2],res[3],res[4]))
    print('rowcount = %d' % cursor.rowcount)
    result = cursor.fetchall()
    for row in result:
        id = row[0]
        name = row[1]
        gender = row[2]
        age = row[3]
        score = row[4]
        print('id = %s,name = %s,gender = %s,age = %d,score = %d' % (id, name, gender, age, score))
except:
    import traceback
    traceback.print_exc()
    print('execute error')
db.close()

SELECT * FROM tb_student
fetchone ===> id = 1,name = mike,gender = F,age = 21,score = 90 
rowcount = 2
id = 2,name = jane,gender = M,age = 24,score = 95

4 update 操作
将 age = 24 的记录的 score 增加 5 分

import pymysql
db = pymysql.connect(host='10.52.165.111', user='root',
                      passwd = '123456', db='db_study', charset='utf8')
cursor  = db.cursor()
sql_update = 'UPDATE tb_student SET score = score + 5 WHERE age = 24 '
print('sql_update = %s' % sql_update)
try:
    cursor.execute(sql_update)
    db.commit()
    print('update success')
except:
    db.rollback()
    print('update error')
db.close()

sql_update = UPDATE tb_student SET score = score + 5 WHERE age = 24 
update success

在这里插入图片描述
5 delete 操作
将 age < 24 的记录删除

import pymysql
db = pymysql.connect(host='10.52.165.111', user='root',
                      passwd = '123456', db='db_study', charset='utf8')
cursor = db.cursor()
# delete row
sql_delete = 'DELETE FROM tb_student WHERE age < 24'
print('sql_delete = %s' % sql_delete)
try:
    cursor.execute(sql_delete)
    db.commit()
    print('delete success')
except:
    db.rollback()
    print('delete error')
print('delete rowcount = %d' % cursor.rowcount)
# 查询所有行
sql_select = 'SELECT * FROM tb_student'
print('sql_select = %s' % sql_select)
try:
    cursor.execute(sql_select)
    print('select rowcount = %d' % cursor.rowcount)
    result = cursor.fetchall()
    for row in result:
        id = row[0]
        name = row[1]
        gender = row[2]
        age = row[3]
        score = row[4]
        print('id = %s,name = %s,gender = %s,age = %d,score = %d' % (id, name, gender, age, score))
except:
    import traceback
    traceback.print_exc()
    print('execute error')
db.close()

sql_delete = DELETE FROM tb_student WHERE age < 24
delete success
delete rowcount = 1
sql_select = SELECT * FROM tb_student
select rowcount = 1
id = 2,name = jane,gender = M,age = 24,score = 100

在这里插入图片描述
附作者相关研究：
Python:
Python实现线性回归、逻辑回归、KNN、SVM、朴素贝叶斯、决策树、K-Means7种机器学习算法的经典案例——亲测可用，链接
https://download.csdn.net/download/qq_35778860/20715889
Python实现飞机大战的完整代码——亲测可用，链接
https://download.csdn.net/download/qq_35778860/57165535
Python爬虫获取豆瓣网评分Top200的电影——亲测可用，链接
https://download.csdn.net/download/qq_35778860/56379309
Python根据下拉选项绘制雷达图和柱形图（异常预控平台）——亲测可用，链接
https://download.csdn.net/download/qq_35778860/20715210
Python根据下拉选项绘制雷达图和柱形图（异常预控平台）——亲测可用，链接
https://download.csdn.net/download/qq_35778860/20715210
python实现雷达图——亲测可用，链接
https://download.csdn.net/download/qq_35778860/20695215
基于python的北京房屋出租数据可视化分析与3D展示——亲测可用，链接
https://download.csdn.net/download/qq_35778860/20675051
基于Python设计的web接口聊天机器人
https://download.csdn.net/download/qq_35778860/66662593
用Python进行自然语言处理（中文）.rar
https://download.csdn.net/download/qq_35778860/63652414
《机器学习实战》全书python代码——很全很完整
https://download.csdn.net/download/qq_35778860/62203534
Python3实现十大排序算法
https://download.csdn.net/download/qq_35778860/61630851
C#
成绩管理系统(C/S结构的应用系统，含access数据库)——C#实现
https://download.csdn.net/download/qq_35778860/66967387
C#实现学生信息管理系统（包括增删改查功能）——亲测可用
https://download.csdn.net/download/qq_35778860/61620149
Matlab
极限学习机ELM+OSELM+KELM+半监督SSELM+USELM的matlab程序(附完整代码)
https://download.csdn.net/download/qq_35778860/56378533
P+近邻KNN+LS最小二乘算法的matlab代码——亲测可用（含实例）
https://download.csdn.net/download/qq_35778860/56378309
基于matlab的卷积神经网络实现手写数字识别
https://download.csdn.net/download/qq_35778860/66667747
基于MATLAB的人工蜂群算法
https://download.csdn.net/download/qq_35778860/66665944
利用MATLABsilulink搭建有源电力滤波器仿真.zip
https://download.csdn.net/download/qq_35778860/63659150
动态规划MATLAB程序.zip
https://download.csdn.net/download/qq_35778860/63658838
基于matlab的模式识别基础实例源代码.zip
https://download.csdn.net/download/qq_35778860/63658486
Matlab_动态窗口法实现机器人在障碍环境下的模拟避碰仿真.zip
https://download.csdn.net/download/qq_35778860/63658232
bp神经网络整定pid参数matlab应用程序.rar
https://download.csdn.net/download/qq_35778860/63656174
利用深度学习的matlab程序编码仿真实现图像分割.zip
https://download.csdn.net/download/qq_35778860/63655267
Java
基于java的信息管理系统
https://download.csdn.net/download/qq_35778860/66658535
Java swing学生成绩系统（源码+数据库脚本）.rar
https://download.csdn.net/download/qq_35778860/63653951
Jsp
jsp学生信息管理系统（源码+数据库脚本）.zip
https://download.csdn.net/download/qq_35778860/63653655
SCN
随机配置网络SCN实现的matlab代码——亲测可用
https://download.csdn.net/download/qq_35778860/61636684
BLS
宽度学习BLS的matlab代码+Mnist数据集
https://download.csdn.net/download/qq_35778860/61635045
Mysql
Mysql增删改查代码操作，很全很完整——亲测可用
https://download.csdn.net/download/qq_35778860/61626574
C
基于单片机的交通灯控制系统设计（含代码，原理图）——C语言实现
https://download.csdn.net/download/qq_35778860/60383113
基于单片机的温度控制系统设计代码（含代码，原理图）——C语言实现
https://download.csdn.net/download/qq_35778860/60379923
郭天祥51单片机书中400例程——亲测可用
https://download.csdn.net/download/qq_35778860/58014293
其他
微信转盘抽奖小程序源码.rar
https://download.csdn.net/download/qq_35778860/63652890

youthlost

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
python连接并操作mysql

1 数据库简单操作连接数据库之前，先使用 MySQL 客户端 SQLyog 手动创建好数据库和表：# 创建数据库 CREATE DATABASE db_study;USE db_study;# 加USE,都加分号即可# 创建表CREATE TABLE tb_student( id INT NOT NULL AUTO_INCREMENT, `name` CHAR(20)...
复制链接

扫一扫