Python之MySQL数据库连接驱动pymysql的使用

  本文介绍python数据库MySQL连接驱动pymysql的简单使用

  1,安装pymysql模块

  未安装模块首先使用pip安装

pip3 install pymysql

  2,创建数据库连接

  使用模块pymysql创建数据库连接的语法为

  传递的参数为host主机,user用户名 password密码,db数据库名称 

conn = pymysql.connect(host='localhost',user='www-data',password='www-data',db='awesome')

  以键值对方法传递,也可以把这些键值对存储为一个dict使用以下方式传递

conn = pymysql.connect(**kw)

  3,创建数据库浮标

  使用以下方法创建浮标,使用数据库连接对象conn加方法cursor创建一个数据库连接浮标

cursor = conn.cursor(pymysql.cursors.DictCursor)

  4,执行sql语句

  有了数据库浮标cursor就可以执行sql语句了,执行语法如下

cursor.execute(sql,args)
sql # 需要执行的sql语句例如'select * from table_name'
args # 替换sql语句的格式化字符串,即sql语句可以使用%s代表一个字符串,然后在args中使用对应的变量或参数替换,args为一个list或元组,即是一个有序的序列需要和sql中的%s一一对应
# 例如sql='select * from table_name where id=%s'  args=['12345']
# 相当于使用args中的参数替换sql中的%s 
# select * from table_name where id='12345'

  5,实践执行sql语句

  实践执行sql语句前我们首先在本机创建一个数据库和对应的表用于测试

  数据库对应的主机,用户名,密码,库名,表名如下

host: localhost
user: www-data
password: www-data
db:awesome
table_name: users

  创建表名的sql语句如下,需要在数据库中创建好对应的表

CREATE TABLE `users` (
  `id` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `passwd` varchar(50) NOT NULL,
  `admin` tinyint(1) NOT NULL,
  `name` varchar(50) NOT NULL,
  `image` varchar(500) NOT NULL,
  `created_at` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email` (`email`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

  创建好的表对应的结构如下

mysql> desc users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | varchar(50)  | NO   | PRI | NULL    |       |
| email      | varchar(50)  | NO   | UNI | NULL    |       |
| passwd     | varchar(50)  | NO   |     | NULL    |       |
| admin      | tinyint(1)   | NO   |     | NULL    |       |
| name       | varchar(50)  | NO   |     | NULL    |       |
| image      | varchar(500) | NO   |     | NULL    |       |
| created_at | double       | NO   | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
7 rows in set (2.68 sec)

  ①执行insert操作

  往数据库插入一条数据

import time
# insert start
sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
args = ['test@qq.com','password',1,'test','about:blank',time.time(),'111111']
# 使用replace 把'?'替换成'%s'
cursor.execute(sql.replace('?','%s'),args)
conn.commit()
# insert end

  插入以下代码

  use_pymysql.py

import time
# insert start
sql = 'insert into `users` (`email`, `passwd`, `admin`, `name`, `image`, `created_at`, `id`) values (?, ?, ?, ?, ?, ?, ?)'
args = ['test@qq.com','password',1,'test','about:blank',time.time(),'111111']
# 使用replace 把'?'替换成'%s'
cursor.execute(sql.replace('?','%s'),args)
conn.commit()
# insert end

  注意:执行修改操作需要使用conn.commit()提交,否则修改不生效

  在MySQL数据库查询可以看到插入的数据

mysql> select * from users;
+--------+-------------+----------+-------+------+-------------+------------------+
| id     | email       | passwd   | admin | name | image       | created_at       |
+--------+-------------+----------+-------+------+-------------+------------------+
| 111111 | test@qq.com | password |     1 | test | about:blank | 1637723096.69565 |
+--------+-------------+----------+-------+------+-------------+------------------+
1 row in set (0.00 sec)

 

  注意:执行操作也是有返回的,返回结果是本次操作影响的数据库条数,如果把返回结果打印,本次输出为1

 

rs = cursor.execute(sql.replace('?','%s'),args)
conn.commit()
print(rs)

  ②执行update操作

  修改数据我们根据条件id修改刚刚插入的数据

  执行修改前需要把刚刚执行的插入语句注释,否则因为建值重复会报错

# update start
sql = 'update `users` set `email`=?, `passwd`=?, `admin`=?, `name`=?, `image`=?, `created_at`=? where `id`=?'
args = ['test2@qq.com','password',1,'test2','about:blank',time.time(),'111111'] 
print(cursor.execute(sql.replace('?','%s'),args))
conn.commit()
# update end

  执行以后把email和name修改了

 

 

   ③执行delete操作

  执行删除操作把刚刚插入的数据删除

# delete start
sql = 'delete from `users` where `id`=?'
args = ['111111'] 
print(cursor.execute(sql.replace('?','%s'),args))
conn.commit()
# delete end

  同样根据关键字id指定的值删除了这条数据

  ④执行select操作

  在执行select操作前我们保证数据库里面至少有一条数据

cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = 'select * from users'
args = []
# 执行查询返回结果数量
# 执行查询
rs=cursor.execute(sql,args)
# 获取查询结果
# 获取查询的第一条结果,返回一个dict,dict元素是查询对应的键值对
# 如果查询结果有多条则执行一次,游标移动到下一条数据,在执行一次又返回一条数据
print(cursor.fetchone())

  同样执行select操作返回结果也是本次操作影响了几行数据,即根据条件查询到几条数据,本次为1条

  要想获得结果使用游标的fetchone(),fetchall(),fetehmant(num)来获取,他们的区别在于

# 获取查询的第一条结果,返回一个dict,dict元素是查询对应的键值对
# 如果查询结果有多条则执行一次,游标移动到下一条数据,再执行一次又返回一条数据
print(cursor.fetchone())
# 获取查询的所有结果,返回一个list,list元素是dict,dict元素是查询对应的键值对
print(cursor.fetchall())
# 获取查询的前几条结果,返回一个list,list元素是dict,dict元素是查询对应的键值对
print(cursor.fetchmany(1))

  fetchone()获取的是dict

  fetchall()和fecthmany(num)获取的是list,list的元素是是dict

  他们对应的结果如下

print(cursor.fetchone())
# {'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}

print(cursor.fetchall())
# [{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}]

print(cursor.fetchmany(1))
# [{'id': '111111', 'email': 'test@qq.com', 'passwd': 'password', 'admin': 1, 'name': 'test', 'image': 'about:blank', 'created_at': 1637723578.5734}]

  注意:cursor的一个浮标,当获取数据以后浮标会移动,假如查询的数据只有一条已经使用cursor.fetchone()方法取出这一条数据,浮标已经移动到没有数据,再使用fetchone(),fetchall() ,fetchmany(num)去获取则会返回None或者是空列表[]

  浮标处没有数据了再使用fetchone()返回None,使用fetchall(),fetchmany(num)返回[]

  ⑥写成函数使用

  为了方便调用可以把执行sql语句写成函数来使用,分两个函数,一个函数执行select把结果返回,一个函数执行insert,update,delete把影响的行数返回

def select(sql,args,size=None):
    log(sql,args)
    cursor =  conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(sql.replace('?','%s'),args or ())
    if size:
        rs = cursor.fetchmany(size)
    else:
        rs = cursor.fetchall()
    cursor.close
    logging.info('rows returned: %s' % len(rs))
    return rs

def execute(sql,args):
    
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        cursor.execute(sql.replace('?','%s'),args)
        # rowcount方法把影响函数返回
        rs = cursor.rowcount
        cursor.close()
        conn.commit()
    except:
        raise
    return rs

  其中select函数加一个默认参数size即返回查询的数据条数,需要传递一个整数返回这个整数的个数的条数,如果不传递则返回所有查询结果,结果是一个list,这个list的元素是查询结果键值对组成的dict

  执行修改操作的函数把影响的条数返回,如果返回为0则代表没有影响数据库,代表修改失败,修改操作不要忘记conn.commit()提交修改结果,否则修改不生效

posted @ 2021-11-24 11:45  minseo  阅读(174)  评论(0编辑  收藏  举报