pymysql类库学习记录

举报
建帅小伙儿 发表于 2022/09/25 01:24:12 2022/09/25
【摘要】 一、代码如下 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

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。