python3学习-连接mysql

mysql介绍

mysql是web世界中使用的最广泛的数据库产品之一，是为服务器端设计的数据库，能承受高并发访问。此外，MySQL内部有多种数据库引擎，最常用的引擎是支持数据库事务的InnoDB

配置文件

[mysql-database]
host=localhost
port=3306
user=root
password=root
database=stock
charset=utf-8

读取配置文件

import os
import configparser

# 当前文件路径
curpath = os.path.dirname(os.path.realpath(__file__))
# 配置文件的路径
cfgpath = os.path.join(curpath, "config.ini")
# mysql配置的section名
dbSection = 'mysql-database'

conf = configparser.ConfigParser()
# 读取配置文件
conf.read(cfgpath, encoding='utf-8')
# 获取host
host = conf.get(dbSection, 'host')
# 获取port
port = conf.get(dbSection, 'port')
# 获取user
user = conf.get(dbSection, 'user')
# 获取password
password = conf.get(dbSection, 'password')
# 获取database
database = conf.get(dbSection, 'database')

连接mysql

使用mysql官方的mysql.connector库进行连接，并封装为class,最终代码如下：

#!/usr/bin/env python3
import os
import configparser
import mysql.connector
from mysql.connector import errorcode

# 当前文件路径
curpath = os.path.dirname(os.path.realpath(__file__))
# 配置文件的路径
cfgpath = os.path.join(curpath, "config.ini")
# mysql配置的section名
dbSection = 'mysql-database'

conf = configparser.ConfigParser()
# 读取配置文件
conf.read(cfgpath, encoding='utf-8')
# 获取host
host = conf.get(dbSection, 'host')
# 获取port
port = conf.get(dbSection, 'port')
# 获取user
user = conf.get(dbSection, 'user')
# 获取password
password = conf.get(dbSection, 'password')
# 获取database
database = conf.get(dbSection, 'database')

class DB:
    def __init__(self):
        self.config = {
            'host': host,
            'port': port,
            'user': user,
            'passwd': password,
            'database': database
        }

    def open(self):
        try:
            self.cnn = mysql.connector.connect(**self.config)
            self.cursor = self.cnn.cursor()
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                print("Something is wrong with your user name or password")
            elif err.errno == errorcode.ER_BAD_DB_ERROR:
                print("Database does not exist")
            else:
                print(err)
                self.cnn.close()

    def execute(self, sql, params):
        try:
            print(type(params))
            self.cursor.execute(sql, params)
        except Exception as e:
            print(e)

    def query(self, sql):
        self.cnn.query(sql)

    def commit(self):
        self.cnn.commit()

    def close(self):
        self.cursor.close()
        self.cnn.close()