Python / MySql
10 分钟 读完 (大约 1466 个字) 0次访问

181118-Python之Mysql基本使用篇

文章目录
  1. I. 基本使用篇
    1. 1. 基本环境
    2. 2. db连接
    3. 3. 插入
    4. 4. 更新
    5. 5. 查询
    6. 6. 关闭连接
    7. 7. 使用测试
  2. II. 其他
    1. 1. 一灰灰Blog： https://liuyueyi.github.io/hexblog
    2. 2. 声明
    3. 3. 扫描关注

I. 基本使用篇

这里主要使用mysql的基本使用姿势，也就是最常见的增删改查，这里主要是使用pymysql这个包来操作

1. 基本环境

mysql环境

mysql环境搭建，基本配置如下

1
2
3
4
5
6
ip: 127.0.0.1
port: 3306
user: root
password: 
database: test
table: user

表结构如下

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL DEFAULT '' COMMENT '用户名',
  `pwd` varchar(26) NOT NULL DEFAULT '' COMMENT '密码',
  `isDeleted` tinyint(1) NOT NULL DEFAULT '0',
  `created` varchar(13) NOT NULL DEFAULT '0',
  `updated` varchar(13) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

python环境

Python3.7 + pymysql

2. db连接

不管什么语言，使用mysql的第一步都是建立连接，然后才能执行具体的sql语句，第一步的建连如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import pymysql


def register():
    '''
    数据库连接相关的初始化操作
    :return:
    '''
    # 打开数据库连接
    db = pymysql.connect(host='127.0.0.1', port=3306,
                         user='root', password='',
                         db='test', charset='utf8')

    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()

    # 使用 execute()  方法执行 SQL 查询
    cursor.execute("SELECT VERSION()")

    # 使用 fetchone() 方法获取单条数据.
    data = cursor.fetchone()

    print("Database version : %s " % data)
    return db, cursor

db, cursor = register()

建立连接主要就是pymysql.connect这一行的调用，成功之后会返回mysql的连接对象db;然后可以通过它获取后续进行sql语句执行的cursor对象；这两个对象后续都会使用

3. 插入

插入命令简单来说就是两步

  • cursor.execute(sql) : 执行sql语句
  • db.commit() : 提交sql执行的结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
def insertDb(cursor, db):
    '''
    测试向表中新插入一条数据的case
    :param cursor:
    :param db:
    :return:
    '''
    print("-------------- start to insert one record ----------------")
    # 插入一条数据
    cursor.execute('''
        insert into user(`name`, `pwd`, `isDeleted`, `created`, `updated`) values('test_{0}', '123456', 0, {0}, {0})
    '''.format(int(time.time())))
    # 提交修改，在插入、修改、删除时，需要执行下面这个命令，否则sql的执行结果不会提交到mysql（类似事物中的提交概念）
    db.commit()
    print("cursor.execute effect: ", cursor.rowcount)
    print("-------------- end to insert one record ----------------\n")

    # 查询db的使用方式
    print("-------------- start to query one record ----------------")
    cursor.execute("select * from user")
    # 表示取得结果集下一行
    data = cursor.fetchone()
    print("fetch result", data)
    print("-------------- end to query one record ----------------\n")

4. 更新

更新与删除其实和插入的步骤都一样，无非是sql语句不同罢了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
def updateDb(cursor, db):
    '''
    测试更新db的case
    :param cursor:
    :param db:
    :return:
    '''
    # 开始更新db
    print("-------------- start to update one record ----------------")
    cursor.execute('''
        update user set isDeleted=1 where id=1 limit 1
    ''')
    # 再未提交之前，查看结果
    cursor.execute('''
        select * from user where id=1
    ''')
    data = cursor.fetchone()
    print("query before update res: ", data)

    # 提交执行
    db.commit()

    # 查看提交之后的结果
    cursor.execute('''
        select * from user where id=1
    ''')
    data = cursor.fetchone()
    print("query after update res: ", data)
    print("-------------- end to update one record ----------------\n")

5. 查询

查询与前面最大的区别就是不在需要执行 db.commit() 这一行了，直接cursor.execute即可，why？因为mysql的MVVC快照读方式

对于查询，最主要的就是对返回结果的处理，有取一条，取多条，取所有的三种使用方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
def queryDb(cursor, db):
    '''
    查询db的使用case
    :param cursor:
    :param db:
    :return:
    '''
    print("-------------- start to query records ----------------")

    cursor.execute('''
        select * from user limit 10
    ''')
    # 取结果集合中的第一条数据
    record = cursor.fetchone()
    print("record1:", record)
    print("----split------")

    # 取结果集合中的两条，然后依次打印
    records = cursor.fetchmany(2)
    print("record2:", records)

    print("----split------")

    # 通过遍历方式，逐一获取所有的结果
    for record in cursor.fetchall():
        print("record:", record)

    print("-------------- end to query records ----------------\n")

6. 关闭连接

1
2
3
4
5
6
7
8
def close(db):
    '''
    关闭数据库连接
    :param db:
    :return:
    '''
    # 关闭数据库连接
    db.close()

7. 使用测试

测试方式

1
2
3
4
5
6
db, cursor = register()
for i in range(0, 11):
    insertDb(cursor, db)
updateDb(cursor, db)
queryDb(cursor, db)
close(db)

输出结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
Database version : 5.7.22 
-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect:  1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to update one record ----------------
query before update res:  None
query after update res:  None
-------------- end to update one record ----------------

-------------- start to query records ----------------
record1: (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
----split------
record2: ((13, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296'), (14, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296'))
----split------
record: (15, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (16, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (17, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (18, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (19, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (20, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (21, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
-------------- end to query records ----------------

II. 其他

1. 一灰灰Blog： https://liuyueyi.github.io/hexblog

一灰灰的个人博客，记录所有学习和工作中的博文，欢迎大家前去逛逛

2. 声明

尽信书则不如，已上内容，纯属一家之言，因个人能力有限，难免有疏漏和错误之处，如发现bug或者有更好的建议，欢迎批评指正，不吝感激

3. 扫描关注

一灰灰blog

QrCode

知识星球

goals

# Mysql, Python, 教程

喜欢这篇文章？打赏一下作者吧

181119-Redis性能监控之Redis-Stat
181117-Python异步之asyncio

评论

关注我
Google Play    Android

链接

最新文章

标签云

AES Android Arthas AutoCloseable BeanUtil BloomFilter BufferedImage BugFix Bugfix CURL Chrome DNS Docker Dubbo ElasticSearch Email FastJson Git GitHub Grafana Groovy Gson Guava HashMap IDEA IO ImageMagic InfluxDB InputStream Iterator JDK JNDI JVM Jackson Java JavaAgent JavaScript JavaWeb Jquery Json LRU Linux List MD5 Map Maven Mongo MongoDB MongoDb MySql Mybatis Mysql Nginx Node OGNL Prometheus ProtoStuff Python QlExpress QuickAlarm QuickMedia RMI RabbitMQ ReactJS Redis SSL Shell Socket Solr Spring SpringBoot Vue WebSocket WebView Win10 Yaml ZooKeeper css curl dns ffmpeg git gitalk google grep hexo hostname jdk logger markdown named ncat python ssh ssl time tmux zsh 专业词汇 乱码 二维码 代理 内购 分库分表 反射 吐槽 子系统 工具 并发 序列化 手记 技术方案 指南 教程 方案设计 时区 时间窗口 爬虫 编程技巧 证书 问题记录 随笔

标签

AES 1
Android 4
Arthas 1
AutoCloseable 1
BeanUtil 2
BloomFilter 1
BufferedImage 2
BugFix 2
Bugfix 2
CURL 2
Chrome 2
DNS 1
Docker 5
Dubbo 1
ElasticSearch 7
Email 1
FastJson 1
Git 3
GitHub 1
Grafana 3
Groovy 1
Gson 5
Guava 5
HashMap 1
IDEA 4
IO 2
ImageMagic 2
InfluxDB 17
InputStream 1
Iterator 1
JDK 36
JNDI 3
JVM 3
Jackson 1
Java 75
JavaAgent 2
JavaScript 2
JavaWeb 2
Jquery 1
Json 1
LRU 1
Linux 11
List 1
MD5 1
Map 1
Maven 4
Mongo 2
MongoDB 4
MongoDb 10
MySql 3
Mybatis 2
Mysql 21
Nginx 7
Node 1
OGNL 4
Prometheus 1
ProtoStuff 2
Python 142
QlExpress 4
QuickAlarm 1
QuickMedia 3
RMI 3
RabbitMQ 9
ReactJS 1
Redis 9
SSL 1
Shell 21
Socket 2
Solr 1
Spring 26
SpringBoot 2
Vue 1
WebSocket 1
WebView 2
Win10 2
Yaml 1
ZooKeeper 1
css 3
curl 1
dns 1
ffmpeg 1
git 2
gitalk 1
google 1
grep 1
hexo 1
hostname 1
jdk 1
logger 1
markdown 1
named 1
ncat 1
python 2
ssh 1
ssl 1
time 1
tmux 2
zsh 1
专业词汇 1
乱码 1
二维码 3
代理 1
内购 1
分库分表 1
反射 3
吐槽 1
子系统 1
工具 5
并发 3
序列化 1
手记 6
技术方案 22
指南 6
教程 21
方案设计 1
时区 1
时间窗口 3
爬虫 5
编程技巧 15
证书 1
问题记录 1
随笔 1

分类

归档

分类

归档

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×