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=utf8import osimport configparser as cparserimport 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=utf8from .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 testselect * 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