Python 操作 MySQL

前期已学习MySQL的命令操作，现需使用Python对数据库进行操作，具体操作流程分为以下5步：

创建数据库连接
创建游标
数据库操作
关闭游标
关闭连接

　　首先，需要对游标有个大体概念；游标是数据库中用来存储数据处理位置的容器，游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。 先有数据基础：游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。 类似于指针：游标类似于指向数据结构堆栈中的指针，用来pop出所指向的数据，并且只能每次取一个。

　　1、创建数据库连接

Python创建数据库连接，需使用mysql包中的connect模块，首先需导入模块：

from mysql import connect

创建数据库连接：

# 创建数据库连接需包含host、port、database、user、password、charset这6个参数，排序无要求
conn = connect(host='localhost', port=3306, database='数据库名', user='数据库账户名', password='密码', charset='utf8')

　　2、创建游标

cur = conn.cursor()

　　3、数据库操作

# 数据库操作命令，建议使用三引号，以防命令中存在引号的情况。
sql_str = '''select * from 数据表名称'''

# 游标记录执行次数
row_count = cur.execute(sql_str)
print(row_count)

# fetch命令用来取出命令执行结果
row_one = cur.fetchone()
# fetchone()为仅取出1条记录
print(row_one)

# fetchmany(n)括号内填入执行数量
row_many = cur.fetchmany(4)
# 以上结果为元祖形式，循环遍历并打印
for t in row_many:
    print(t)

# fetchall 执行取出所有剩下结果
row_all = cur.fetchall()
for t in row_many:
    print(t)

　　4、关闭游标和连接，注意先后顺序

cur.close()
conn.close()

参数化查询时需注意防止SQL注入问题

# 拼接字段作为sql查询语句
sql_str = ''' select * from 表名 where 字段名=%s''' % 搜索条件
cur.execute(sql_str)
fetch_all = cur.fetchall()
for i in fetch_all:
    print(i)

'''
当搜索条件为'任意字符 or 1'时，sql执行前面任意字符后，继续执行or后面的1，sql中1为True，系统会自动输出所有该表格数据。
'''

# 参数化查询，防止注入问题
sql_str = ''' select * from 表名 where 字段名=%s''' 
cur.execute(sql_str, 搜索条件) # 搜索条件可为列表、元祖、字典等格式数据
fetch_all = cur.fetchall()
for i in fetch_all:
    print(i)

'''
当搜索条件为'任意字符 or 1'时，sql执行前面任意字符后，继续执行or后面的1，系统自动判断为异常，并抛出
Warning: (1292, "Truncated incorrect DOUBLE value: '1 or 1'")
  result = self._query(query)
的异常提醒。
'''

以上为查询数据操作，下面为连续代码实现数据库数据修改操作

from mysql import connect


conn = connect(host='localhost', port=3306, database='数据库名', user='用户名', password='密码', charset='utf8')
cur = conn.cursor()

sql_str = ''' insert into 表名(字段1, 字段2......) value(修改参数1, 修改参数2......) '''
row_count = cur.execute(sql_str)

# 修改操作需先提交
conn.commit()

cur.close()
conn.close()

 下面为完成某货物清单数据库操作案例：

首先需创建数据库

create database JDDB charset utf8;

按如下要求创建数据表

# 创建goods表
| goods | CREATE TABLE `goods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `cate_id` int(10) unsigned NOT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `price` decimal(10,3) NOT NULL DEFAULT '0.000',
  `is_show` bit(1) NOT NULL DEFAULT b'1',
  `is_saleoff` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`),
  KEY `FK_cate_id` (`cate_id`),
  CONSTRAINT `FK_cate_id` FOREIGN KEY (`cate_id`) REFERENCES `goods_cates` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 |

# 创建goods_cates表
| goods_cates | CREATE TABLE `goods_cates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 |

# 创建goods_brands表
| goods_brands | CREATE TABLE `goods_brands` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 |

按以上规则创建完成后，可按自身要求插入数据

from pymysql import connect


class JD(object):
    def __init__(self):
        self.__conn = connect(host='localhost', port=3306, database='JDDB', user='root', password='mysql',
                              charset='utf8')
        self.__cur = self.__conn.cursor()

    def run(self):
        while True:
            print("1查询所有商品信息")
            print("2查询所有商品在种类信息")
            print("3查询所有商品在品牌信息")
            print("4添加商品种类")
            print("5根据id查询商品信息")
            print("6根据id查询商品信息安全方式")
            select_id = input('请输入要进行的操作选项：')

            if select_id == '1':
                self.__fetch_good()

            elif select_id == '2':
                self.__fetch_cate()

            elif select_id == '3':
                self.__fetch_brand()

            elif select_id == '4':
                cate_name = input('请输入需添加的商品种类：')
                self.__add_cate(cate_name)

            elif select_id == '5':
                good_id = input('请输入需查找的商品id：')
                self.__find_info(good_id)

            elif select_id == '6':
                good_id = input('请输入需查找的商品id：')
                self.__find_info_safe(good_id)

            else:
                print('请输入合法操作！')
                print('-'*50)
                print('')

    def __show_info(self, fetch_all):
        for i in fetch_all:
            print(i)
        print('-'*50)
        print('')

    # 查询所有商品
    def __fetch_good(self):
        sql_str = '''select * from goods'''
        self.__cur.execute(sql_str)
        fetch_all = self.__cur.fetchall()
        self.__show_info(fetch_all)

    # 查询所有商品类目
    def __fetch_cate(self):
        sql_str = ''' select * from goods_cates'''
        self.__cur.execute(sql_str)
        fetch_all = self.__cur.fetchall()
        self.__show_info(fetch_all)

    # 查询所有商品品牌
    def __fetch_brand(self):
        sql_str = ''' select * from goods_brands'''
        self.__cur.execute(sql_str)
        fetch_all = self.__cur.fetchall()
        self.__show_info(fetch_all)

    # 添加商品类目
    def __add_cate(self, type):
        sql_str = '''insert into goods_cates(name) value(%s)'''
        self.__cur.execute(sql_str, (type,))
        self.__conn.commit()
    
    def __find_info(self, good_id):
        '''
        该方法中查询语句存在缺陷，可能出现SQL注入溢出 
        '''
        sql_str = ''' select goods.name,goods.price,gb.name,gc.name from goods inner join goods_brands gb inner join goods_cates gc on goods.brand_id=gb.id and goods.cate_id=gc.id where goods.id= %s''' % good_id
        self.__cur.execute(sql_str)
        fetch_all = self.__cur.fetchall()
        self.__show_info(fetch_all)

    # 按编号查询商品
    def __find_info_safe(self, good_id):
        sql_str = ''' select goods.name,goods.price,gb.name,gc.name from goods inner join goods_brands gb inner join goods_cates gc on goods.brand_id=gb.id and goods.cate_id=gc.id where goods.id= %s'''
        self.__cur.execute(sql_str, (good_id,))
        fetch_all = self.__cur.fetchall()
        self.__show_info(fetch_all)

    def __del__(self):
        '''
        程序执行结束后自动关闭游标及连接
        '''    
        self.__cur.close()
        self.__conn.close()


def main():
    jd = JD()
    jd.run()


if __name__ == '__main__':
    main()       

该案例为展示Python实现SQL操作的简单项目，主要目的为简单展示实现方式及过程。