1.创建数据库连接
conn = pymysql.connect(
  user='array',
  password='admin',
  host='192.168.120.216',
  prot=3306,
  charset='uft8',
  database='uag'
)
返回一个数据库连接对象
2.创建游标
cursor = conn.cursor(cursor=None)
返回一个游标对象
3.创建sql语句
sql = "select * from uag;"
4.执行sql语句,并返回受影响行数
effect_row = cursor.excute(sql)
5.提交数据，否则无法保存修改的数据
conn.commit()
6.关闭游标
cursor.close()
7.断开连接
conn.close()

# coding=utf-8
"""
    作者：gaojs
    功能：
    新增功能：
    日期：2022/4/17 13:59
"""
import pymysql
class Database:
    def __init__(self):
        # 连接数据库
        self.connect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='admin',
            db='gaojs_test',
            charset='UTF8'
        )
        # 获取游标
        self.cursor = self.connect.cursor()
        self.cursor.execute("SELECT VERSION()")
        # 使用 fetchone() 方法获取单条数据.
        data = self.cursor.fetchone()
        print("Database version : %s " % data)
    def create_table(self):
        """
        创建表
        :return:
        """
        # 切换数据库
        self.cursor.execute("use gaojs_test;")
        # 创建表语句
        self.cursor.execute("DROP TABLE IF EXISTS tb_gjs")
        self.cursor.execute("DROP TABLE IF EXISTS gaojs")
        # 使用预处理语句创建表
        sql = "CREATE TABLE tb_gjs( number char(10), name varchar(50), age int, gender varchar(10) , salary char(20));"
        sql1 = "CREATE TABLE gaojs(sid char(15), yuwen char(10), shuxue varchar(50), english int, physics varchar(10) , history char(20), computer char(20));"
        self.cursor.execute(sql)
        self.cursor.execute(sql1)
        self.cursor.execute("desc tb_gjs;")
        self.connect.commit()
    def insert_sql(self):
        """
        update插入数据: UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
        :return:
        """
        # self.cursor.execute("update tb_gjs set phone='13666666666' where name='qiubing';")
        # self.cursor.execute("update tb_gjs set phone='13688888888' where name='gaojianshuai';")
        self.cursor.execute("INSERT INTO tb_gjs (number, name, age, gender, salary) VALUE ('001', 'gaojianshuai', 30, 'man', '16000')")
        self.cursor.execute("INSERT INTO tb_gjs (number, name, age, gender, salary) VALUE ('002', 'chensishi', 27, 'woman', '18000')")
        self.cursor.execute("INSERT INTO tb_gjs (number, name, age, gender, salary) VALUE ('003', 'arraynetworks', 30, 'man', '19000')")
        self.connect.commit()
    def update(self):
        """
        更新数据
        :return:
        """
        self.cursor.execute("update tb_gjs set phone='13666666666' where name='qiubing';")
        self.connect.commit()
    def disconnect_db(self):
        """
        断开数据库
        :return:
        """
        self.connect.commit()
        self.connect.close()
    def alter_table(self):
        """
        新增加列
        :return:
        """
        self.cursor.execute("alter table tb_gjs add column company varchar(20) after salary;")
        self.cursor.execute("""insert into tb_gjs (number, name, age, gender, salary, company) values ('006', 'wanghuan', 22, 'nv', '35000', 'Camlot');""")
        self.connect.commit()
    def delete_procedure(self):
        """
        存储过程
        :return:
        """
        self.cursor.execute("""
            CREATE PROCEDURE delete_age(IN p_age INTEGER)
            BEGIN
            DELETE FROM tb_gjs
            WHERE age = p_age;
            END$$
        """)
        self.cursor.execute("call delete_age(22);")
test = Database()
test.create_table()
test.insert_sql()
test.alter_table()
test.delete_procedure()

import pymysql
from pymysql_test import Database
def get_conn():
    # 连接数据库
    connect = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='admin',
        db='gaojs_test',
        charset='UTF8'
    )
    return connect
    # # 获取游标
    # cursor = connect.cursor()
    # cursor.execute("SELECT VERSION()")
    # # 使用 fetchone() 方法获取单条数据.
    # data = cursor.fetchone()
    # print("Database version : %s " % data)
def load_file_to_mysql():
    conn = get_conn()
    try:
        with open('student.txt', encoding='utf-8') as f:
            for line in f:
                line = line.strip()
                sid, chinese, math, english, phisics, history, computer = line.split("\t")
                sql = f"insert into gaojs (sid, chinese, math, english, phisics, history, computer) values ({sid}, {chinese}, {math}, {english}, {phisics}, {history}, {computer})"
                cursor = conn.cursor()
                cursor.execute(sql)
            cursor.commit()
    finally:
        if conn is not None:
            conn.close()
if __name__ == '__main__':
    load_file_to_mysql()