Python3 使用pymysql库操作mysql

柔光的暖阳◎ 2022-05-11 10:48 301阅读 0赞

1.先安装好mysql及pymysql库。

pip install pymysql

2.连接mysql.

In [1]: import pymysql
    
    In [2]: db = pymysql.connect(host = 'localhost',user = 'root',password = '123456',
       ...: port = 3306)
    
    In [3]: cursor = db.cursor()  #设置游标
    
    In [4]: cursor.execute('SELECT VERSION()')    #SQL语句，查询版本信息
    Out[4]: 1
    
    In [5]: cursor.fetchone()
    Out[5]: ('5.7.23',)
    
    In [6]: db.close()   #关闭连接

3.创建新的SQL库

In [7]: sql = "CREATE DATABASE movies DEFAULT CHARACTER SET utf8"  #新建一个movies数据库
    
    In [8]: cursor.execute(sql)
    Out[8]: 1

打开mysql的可视化工具可以看到，数据库已经创建成功。

![70][]

4.连接到movies数据库

In [10]: db = pymysql.connect(host = 'localhost',user = 'root',password = '12345
        ...: 6',port = 3306,db = "movies")

5.新建一张表

In [11]: sql = 'CREATE TABLE IF NOT EXISTS movies(movie VARCHAR(255) NOT NULL,a
        ...: ctor VARCHAR(255) NOT NULL,score VARCHAR(255) NOT NULL,realse VARCHAR(
        ...: 255) NOT NULL,PRIMARY KEY(movie))'
    
    In [12]: cursor.execute(sql)
    Out[12]: 0

![70 1][]

可以看到，表已经创建成功了。

6.插入数据

In [13]: movie = "倩女幽魂"
    
    In [14]: actor = "张国荣,王祖贤,午马"
    
    In [15]: score = "8.8"
    
    In [16]: realse = "2011-04-30"
    
    In [17]: sql = "INSERT INTO movies(movie,actor,score,realse) values(%s,%s,%s,%s
        ...: )"
    
    In [18]: try:
        ...:     cursor.execute(sql,(movie,actor,score,realse))
        ...:     db.commit()
        ...: except:
        ...:     db.rollback()
        ...:

打开mysql可视化工具，可以看到，已经插入成功。

![70 2][]

7.查询数据。假定该表中有如下数据：

![70 3][]

In [19]: sql = "SELECT * FROM movies"
    
    In [19]: try:
        ...:     cursor.execute(sql)
        ...:     data = cursor.fetchone()
        ...:     while data:
        ...:         print (data)
        ...:         data = cursor.fetchone()
        ...: except:
        ...:     print ("Error!")
        ...:
    ('倩女幽魂', '张国荣,王祖贤,午马', '8.8', '2011-04-30')
    ('加勒比海盗', '约翰尼·德普,凯拉·奈特莉,奥兰多·布鲁姆', '8.9', '2003-11-21')
    ('唐伯虎点秋香', '周星驰,巩俐,郑佩佩', '9.2', '1993-07-01(中国香港)')

8.更新数据

In [20]: sql = 'UPDATE movies SET score = %s WHERE movie = %s'
    
    In [21]: try:
        ...:     cursor.execute(sql,("9.9","倩女幽魂"))  #更改倩女幽魂的评分为9.9分
        ...:     db.commit()   #该语句一定要写，否则更新无效
        ...: except:
        ...:     db.rollback()
        ...:
    
    In [22]: sql = "SELECT * FROM movies WHERE movie = '倩女幽魂'"
    
    In [23]: cursor.execute(sql)
    Out[23]: 1
    
    In [24]: cursor.fetchone()
    Out[24]: ('倩女幽魂', '张国荣,王祖贤,午马', '9.9', '2011-04-30')     #评分以改为9.9分

9.删除数据

In [25]: sql = "DELETE FROM movies WHERE movie = '倩女幽魂'"  #删除倩女幽魂这条数据
    
    In [26]: try:
        ...:     cursor.execute(sql)
        ...:     db.commit()
        ...: except:
        ...:     db.rollback()
        ...:
    
    In [27]: sql = "SELECT * FROM movies"
    
    In [28]: cursor.execute(sql)
    Out[28]: 2
    
    In [29]: cursor.fetchall()  #结果显示倩女幽魂的数据以删除
    Out[29]:
    (('加勒比海盗', '约翰尼·德普,凯拉·奈特莉,奥兰多·布鲁姆', '8.9', '2003-11-21')
    ,
     ('唐伯虎点秋香', '周星驰,巩俐,郑佩佩', '9.2', '1993-07-01(中国香港)'))    
    
    In [30]:

10.删除所有数据而不删除表

In [30]: sql = "TRUNCATE TABLE movies"
    
    In [31]: cursor.execute(sql)
    Out[31]: 0
    
    In [32]: sql = "SELECT * FROM movies"
    
    In [33]: cursor.execute(sql)
    Out[33]: 0
    
    In [34]: cursor.fetchone()   #无一条数据，说明已全部删除
    
    In [35]:

欢迎关注本人微信公众号,会分享更多的干货:

![20200607142700196.png][]

[70]: /images/20220505/46c8dd30f2dd4e8584a97cbb686770b2.png
[70 1]: /images/20220505/edafe04002da4b79b304e6eb22f25b03.png
[70 2]: /images/20220505/9dc075f48d9a42a5b954a7301501695f.png
[70 3]: /images/20220505/07fc59d12719417283a5a998e9229c75.png
[20200607142700196.png]: /images/20220505/6b7d491dec6b4e03bb2f00827a2f7988.png