pymysql类库学习记录

一、代码如下

代码语言：javascript复制

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

二.案例代码

代码语言：javascript复制

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

三、案例2

代码语言：javascript复制

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

pymysql

0 人点赞