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()

#连接数据库
mydb=mysql.connector.connect(
    host=host,
    port=port,
    user=user,
    passwd=password
)
print(mydb)
#mysql语句执行
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE runoob_db")

mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE runoob_db")

mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
  print(x)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")

mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
  print(x)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")

mycursor = mydb.cursor()
 
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = ("RUNOOB", "https://www.runoob.com")
mycursor.execute(sql, val)
 
mydb.commit()    # 数据表内容有更新，必须使用到该语句
 
print(mycursor.rowcount, "记录插入成功。")

mycursor = mydb.cursor()
 
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = [
  ('Google', 'https://www.google.com'),
  ('Github', 'https://www.github.com'),
  ('Taobao', 'https://www.taobao.com'),
  ('stackoverflow', 'https://www.stackoverflow.com/')
]
 
mycursor.executemany(sql, val)
 
mydb.commit()    # 数据表内容有更新，必须使用到该语句
 
print(mycursor.rowcount, "记录插入成功。")

#如果我们想在数据记录插入后，获取该记录的 ID ，可以使用以下代码：
print("1 条记录已插入, ID:", mycursor.lastrowid)

mycursor.execute("SELECT * FROM sites")
 
myresult = mycursor.fetchall()     # fetchall() 获取所有记录
 
for x in myresult:
  print(x)
#查询指定字段数据
mycursor.execute("SELECT name, url FROM sites")
 
myresult = mycursor.fetchall()
 
for x in myresult:
  print(x)
#只读取一条
mycursor.execute("SELECT * FROM sites")
 
myresult = mycursor.fetchone()
 
print(myresult)
#限制查询量limit
mycursor.execute("SELECT * FROM sites LIMIT 3")

mycursor = mydb.cursor()
 
sql = "SELECT * FROM sites WHERE name ='RUNOOB'"
 
mycursor.execute(sql)
 
myresult = mycursor.fetchall()
 
for x in myresult:
  print(x)
#也可使用通配符%
sql = "SELECT * FROM sites WHERE url LIKE '%oo%'"

#查询结果排序可以使用 ORDER BY 语句，默认的排序方式为升序，关键字为 ASC，如果要设置降序排序，可以设置关键字 DESC。
mycursor = mydb.cursor()
 
sql = "SELECT * FROM sites ORDER BY name"
 
mycursor.execute(sql)
 
myresult = mycursor.fetchall()
 
for x in myresult:
  print(x)
#默认升序，降序如下
sql = "SELECT * FROM sites ORDER BY name DESC"

mycursor = mydb.cursor()
 
sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
 
mycursor.execute(sql)
 
mydb.commit()
 
print(mycursor.rowcount, " 条记录删除")

mycursor = mydb.cursor()
 
sql = "UPDATE sites SET name = 'ZH' WHERE name = 'Zhihu'"
 
mycursor.execute(sql)
 
mydb.commit()
 
print(mycursor.rowcount, " 条记录被修改")
执行代码，输出结果为：
#防止注入
sql = "UPDATE sites SET name = %s WHERE name = %s"
val = ("Zhihu", "ZH")

mycursor = mydb.cursor()
 
sql = "DROP TABLE IF EXISTS sites"  # 删除数据表 sites
 
mycursor.execute(sql)