Python操作mysql数据库

操作步骤：

1. python读取MySQL的配置文件；
2. 根据读取到的配置文件连接MySQL；
3. Python连接MySQL后，进行操作，增删改查。

ConfigParser模块

ConfigParser是Python的自带模块，可以读取配置文件。ini是常见的配置文件格式。

配置文件的格式：

[sesion]

host=127.0.0.1
port=3306
user=root
password=123456
db_name=test

代码操作

project：

配置文件：

mysql_db.py

#config=utf8
import os
import configparser as cparser
import pymysql.cursors

#--------读取配置文件-----------
#获取当前脚本的路径
base_dir = os.path.dirname(os.path.dirname(__file__))
base_dir = str(base_dir)
base_dir = base_dir.replace('\\','/')
file_path = base_dir + '/db_conf.ini'
print(file_path)
#读取配置文件
cf = cparser.ConfigParser()
cf.read(file_path)
host = cf.get("mysql_conf",'host')
port = cf.get("mysql_conf",'port')
user = cf.get("mysql_conf",'user')
password = cf.get("mysql_conf",'password')
db_name = cf.get("mysql_conf",'db_name')
print(host,port,user,password,db_name)

#----------python操作MySQL----------
class DB():
    #__init__()方法连接mysql数据库
    def __init__(self):
        try:
            self.connection = pymysql.connect(host = host,
                                              user = user,
                                              password = password,
                                              db = db_name,
                                              charset = 'utf8mb4',
                                              cursorclass = pymysql.cursors.DictCursor
                                              )
        except pymysql.err.OperationalError as e:
            print(e)
            print("连接失败")
    #建立数据表
    def create_table(self):
        sql1 = "create table students(" \
              "id int not null," \
              "name varchar(20) not null," \
              "sex varchar(10) not null," \
              "tall float," \
              "address varchar(255)" \
              ");"
        sql2 = "alter table students add constraint id_pk primary key (id);"
        print(sql1)
        print(sql2)
        with self.connection.cursor() as cursor:
            cursor.execute(sql1)
            cursor.execute(sql2)
        self.connection.commit()
    #删除表
    def drop_table(self,table_name):
        sql3 = "drop table " + table_name + ";"
        print(sql3)
        with self.connection.cursor() as cursor:
            cursor.execute(sql3)
        self.connection.commit()
    #清空数据表
    def clear_table(self,table_name):
        sql4 = "truncate table"+" "+table_name + ";"
        print(sql4)
        with self.connection.cursor() as cursor:
            cursor.execute(sql4)
        self.connection.commit()
    #插入数据（插入的数据封装成字典）
    def insert(self,table_name,table_data):
        for i in table_data:
            #遍历字典获取每一个key值，再获取每个value，把values str()为字符串的形式，
            # 因为在sql语句中，值是字符串的形式的。
            table_data[i] = "'" + str(table_data[i]) + "'"
            #print(table_data[i])
        key = ",".join(table_data.keys())      #以逗号分割keys
        value = ",".join(table_data.values())  #以逗号分割values
        sql = "insert into" + " " + table_name + "(" +key + ")"+"values" + "(" + value + ")" + ";"
        print(sql)
        with self.connection.cursor() as cursor:
            cursor.execute(sql)
        self.connection.commit()
    #断开数据库连接
    def close(self):
        self.connection.close()
if __name__ == "__main__":
    db = DB()
    #db.create_table()
    db.clear_table(table_name="students")
    table_data = {"id":"1","name":"liyue","sex":"boy"}
    db.insert(table_name="students",table_data=table_data)
    #db.drop_table(table_name="students")

test_data.py

#coding=utf8
from .mysql_db import DB

#创建测试数据(插入一张数据表)，多张表需要另外封装字典。
datas = [
        {"id":"1","name":"liyu","sex":"boy","tall":"190","address":"china"},
        {"id":"2","name":"liyue","sex":"boy","tall":"189","address":"china"},
        {"id":"3","name":"liudong","sex":"girl","tall":"180","address":"china"},
        {"id":"4","name":"liu","sex":"boy","tall":"170","address":"china"},
        {"id":"5","name":"liyue","sex":"boy","tall":"160","address":"china"},
        {"id":"6","name":"liyue","sex":"boy","tall":"170","address":"china"},
        {"id":"7","name":"liyue","sex":"boy","tall":"150","address":"china"},
        {"id":"8","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"9","name":"liyue","sex":"boy","tall":"190","address":"china"},
        {"id":"10","name":"liyue","sex":"boy","tall":"160","address":"china"},
        {"id":"11","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"12","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"13","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"14","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"15","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"16","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"17","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"18","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"19","name":"liyue","sex":"boy","tall":"180","address":"china"},
        {"id":"20","name":"liyue","sex":"boy","tall":"180","address":"china"},
    {"id": "21", "name": "liyu", "sex": "boy", "tall": "190", "address": "china"},
    {"id": "22", "name": "liyue", "sex": "boy", "tall": "189", "address": "china"},
    {"id": "23", "name": "liudong", "sex": "girl", "tall": "180", "address": "china"},
    {"id": "24", "name": "liu", "sex": "boy", "tall": "170", "address": "china"},
    {"id": "25", "name": "liyue", "sex": "boy", "tall": "160", "address": "china"},
    {"id": "26", "name": "liyue", "sex": "boy", "tall": "170", "address": "china"},
    {"id": "27", "name": "liyue", "sex": "boy", "tall": "150", "address": "china"},
    {"id": "28", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "29", "name": "liyue", "sex": "boy", "tall": "190", "address": "china"},
    {"id": "30", "name": "liyue", "sex": "boy", "tall": "160", "address": "china"},
    {"id": "31", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "32", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "33", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "34", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "35", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "36", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "37", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "38", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "39", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"},
    {"id": "40", "name": "liyue", "sex": "boy", "tall": "180", "address": "china"}
    ]
#出入测试数据
def test_data():
    db = DB()
    db.clear_table(table_name="students")
    for i in datas:
        db.insert(table_name="students",table_data=i)
    db.close()
if __name__ == "__main__":
    test_data()

数据库查询操作：

在上面的类中增加查询的方法：

fetchone()方法获取一个结果集，返回是衣蛾对象，字典形式；

#查询获取数据
    def select1(self,what,table_name,column_name,value):
        #select * from table;
        sql = "select "+what+" from "+table_name + " where " + column_name +" =" +"'"+value+"'" + ";"
        print(sql)
        with self.connection.cursor() as cursor:
            cursor.execute(sql)
            result = cursor.fetchone() #fetchone()方法获取一个查询结果集，返回的是一个对象，字典形式，可以通过字典的方法获取key和value
            print(result['pic_id'])
            self.connection.commit()

返回：

127.0.0.1 3306 root 123456 test
select * from test1 where id ='1 ';
12345   #获取的pic_id对应的值

fetchall()方法返回数据表中所有的行数据，返回的是一个列表，列表的元素是一个个字典，一个字典就是行数据；

    #查询获取数据
    def select1(self,what,table_name,column_name,value):
        #select * from table;
        sql = "select "+what+" from "+table_name + " where " + column_name +" =" +"'"+value+"'" + ";"
        print(sql)
        with self.connection.cursor() as cursor:
            cursor.execute(sql)
            result = cursor.fetchall()#返回一个列表，列表的元素是一个个字典，每一个字典就是一行数据；
            print(result)
            pic_id = result[0]['pic_id'] #可以通过字典的形式获取每一个字典的key和value值；
            print(pic_id)
            self.connection.commit()

返回：

select * from test1 where id ='1 ';
[{'id': 1, 'type': 'image', 'pic_id': 12345}]
12345