pymysql类库学习记录
【摘要】
一、代码如下
1.创建数据库连接conn = pymysql.connect( user='array', password='admin', host='192.168.120.216', prot=3306, charset='uft8', database='uag') 返回一个数据库连接对象 2.创建游标 cursor...
一、代码如下
-
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()
三、案例2
-
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()
文章来源: blog.csdn.net，作者：懿曲折扇情，版权归原作者所有，如需转载，请联系作者。
原文链接：blog.csdn.net/qq_41332844/article/details/126837322
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）