python: read mysql

geovindu

已于 2023-06-15 22:53:23 修改

阅读量57

点赞数

分类专栏： Python 文章标签： sql 数据库 python

于 2023-06-15 22:37:17 首次发布

本文链接：https://blog.csdn.net/geovindu/article/details/131237083

版权

Python 专栏收录该内容

32 篇文章 0 订阅

订阅专栏

create database geovindu;
 
use geovindu;
 
drop table BookKindList;
#书目录
create table BookKindList
(
    BookKindID INT NOT NULL AUTO_INCREMENT, #自动增加
    BookKindName nvarchar(500) not null,
    BookKindParent int null,
   PRIMARY KEY(BookKindID)  #主键
);
 
insert into BookKindList(BookKindName,BookKindParent) values("六福书目录",0);
 
 
#删除
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`DeleteBookKind` $$
CREATE PROCEDURE `geovindu`.`DeleteBookKind` (IN param1 INT)
BEGIN
         Delete From bookkindlist WHERE BookKindID = param1;
END $$
DELIMITER ;
  
delete from bookkindlist WHERE BookKindID =10;
  
  
SELECT * FROM bookkindlist;
     
 execute DeleteBookKind(10)；
   
   
   
#查询所有
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindListAll` $$
CREATE PROCEDURE `geovindu`.`proc_Select_BookKindListAll` ()
BEGIN
    SELECT * FROM bookkindlist;
END $$
DELIMITER ;
   
 DROP PROCEDURE proc_Select_BookKindListAll;
   
   
   
select * from  `geovindu`.`bookkindlist`;
SELECT * FROM bookkindlist;
   
#统计
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`BookKindCount` $$
CREATE PROCEDURE `geovindu`.`BookKindCount` (OUT param1ID INT)
BEGIN
        select COUNT(*) into param1ID  From bookkindlist;
END $$
DELIMITER ;
   
#更新
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Update_BookKindList` $$
CREATE PROCEDURE `geovindu`.`proc_Update_BookKindList` (IN param1ID Int,IN param1Name NVarChar(1000),IN param1Parent Int)
BEGIN
IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then #如果存在相同的记录，不更新名称
UPDATE BookKindList
    SET
        BookKindName=param1Name ,
        BookKindParent=param1Parent
    where
        BookKindID=param1ID;
ELSE
    UPDATE BookKindList
    SET BookKindParent=param1Parent
    where
        BookKindID=param1ID;
END IF;
END $$
DELIMITER ;
   
   
#查询一条
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Select_BookKindList` $$
CREATE PROCEDURE `geovindu`.`proc_Select_BookKindList` (IN param1 INT)
BEGIN
        SELECT * FROM BookKindList WHERE BookKindID = param1;
END $$
DELIMITER ;
   
#插入一条
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindList` $$
CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindList` (IN param1Name NVarChar(1000),IN param1Parent Int)
BEGIN
        insert into BookKindList(BookKindName,BookKindParent) values(param1Name,param1Parent);
END $$
DELIMITER ;
   
#插入一条返回值
DELIMITER $$
DROP PROCEDURE IF EXISTS `geovindu`.`proc_Insert_BookKindOut` $$
CREATE PROCEDURE `geovindu`.`proc_Insert_BookKindOut` (IN param1Name NVarChar(1000),IN param1Parent Int,OUT ID INT)
BEGIN
     IF NOT EXISTS (SELECT * FROM BookKindList WHERE BookKindName=param1Name) then   #如果存在相同的记录，不添加
        INSERT INTO BookKindList (BookKindName,BookKindParent)VALUES(param1Name ,param1Parent);
        #set ID=Last_insert_id()
        SELECT LAST_INSERT_ID() into ID;
      end if;
END $$
DELIMITER ;

"""
bookkind.py  书目录类
读取excel文件数据
date 2023-06-15
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
 
"""
 
import sys
import os
 
class BookKindList(object):
 
    def __init__(self, BookKindID, BookKindName, BookKindParent):
        """
        保险类  构造函数
        :param  BookKindID:  自增ID
        :param  BookKindName:  书目录名
        :param  BookKindParent:  父ID
        """
        self.__BookKindID = BookKindID
        self.__BookKindName = BookKindName
        self.__BookKindParent = BookKindParent
 
 
    def getBookKindID(self):
        """
        得到自增ID
        :return:  返回自增ID
        """
        return self.__BookKindID
 
 
    def setBookKindID(self, BookKindID):
        """
        自增ID
        :param  BookKindID:  ID
        :return:  none
        """
        self.__BookKindID = BookKindID
 
 
    def getBookKindName(self):
        """
        获取书目录名
        :return:  返回书类目名
        """
        return self.__BookKindName
 
 
    def setBookKindName(self, BookKindName):
        """
        设置书目录名
        :param  BookKindName:  书类名
        :return:  none
        """
        self.__BookKindName = BookKindName
 
 
    def getBookKindParent(self):
        """
        获取父ID
        :return:  返回月份
        """
        return self.__BookKindParent
 
 
    def setBookKindParent(self, BookKindParent):
        """
        设置父ID
        :param  BookKindParent:  输入父ID
        :return:  none
        """
        self.__BookKindParent = BookKindParent
 
 
    def __str__(self):
        return f"BookKindID:  {self.__BookKindID},  BookKindName:  {self.__BookKindName},  BookKindParent:  {self.__BookKindParent}"
 
 
 
 
"""
MySQLDAL.py
读取MySQL数据
date 2023-06-15
edit: Geovin Du,geovindu, 涂聚文
ide: PyCharm 2023.1 python 11
参考：
https://www.mssqltips.com/sqlservertip/6694/crud-operations-in-sql-server-using-python/
https://learn.microsoft.com/zh-cn/sql/connect/python/pymssql/step-3-proof-of-concept-connecting-to-sql-using-pymssql?view=sql-server-ver16
https://docs.sqlalchemy.org/en/20/tutorial/data_update.html
https://datatofish.com/update-records-sql-server/
https://www.dev2qa.com/how-to-use-python-to-insert-delete-update-query-data-in-sqlite-db-table/
https://kontext.tech/article/893/call-sql-server-procedure-in-python
https://learn.microsoft.com/en-us/sql/connect/python/pymssql/python-sql-driver-pymssql?view=sql-server-ver16
https://pythontic.com/database/mysql/stored_procedure
https://github.com/pymssql/pymssql/blob/master/tests/test_connections.py
https://pynative.com/python-mysql-execute-stored-procedure/
 
"""
 
import sys
import os
import pymssql
import pymysql
import pyodbc
import BookKind
 
class sqlDAL(object):
 
    def mysqlconnect():
        """
        连接MySQL 检测其版本
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password="geovindu",
            db='geovindu',
        )
 
        cur = conn.cursor()
        cur.execute("select @@version")
        output = cur.fetchall()
        print(output)
 
        # To close the connection
        cur.close()
        conn.close()
 
    def connectDB(self, host, user, psw, db_name, charset='utf8'):
        self.db = pymysql.connect(host=host, user=user, password=psw, db=db_name, charset=charset)
 
 
    def execSql(self, sql):
        """
        执行SQL语句
        :param sql: SQL 语句
        :return:
        """
        # sql is insert, delete or update statement
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)
            # commit sql to mysql
            self.db.commit()
            cursor.close()
            return True
        except:
            self.db.rollback()
        return False
 
 
    def select():
        """
        查询
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password="geovindu",
            db='geovindu',
        )
 
        cursor = conn.cursor()
        cursor.callproc('proc_Select_BookKindListAll')
        # print results
        print("Printing laptop details")
        #for result in cursor.stored_results():
            #print(result.fetchall())
        rows = cursor.fetchall()
        for row in rows:
            print(f'{row[0]} {row[1]} {row[2]}')
        # To close the connection
        cursor.close()
        conn.close()
 
 
    def selectdu():
        """
        查询
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password="geovindu",
            db='geovindu',
        )
 
        cursor = conn.cursor()
        cursor.callproc('proc_Select_BookKindListAll')
        # print results
        print("Printing laptop details")
        #for result in cursor.stored_results():
        details =cursor.fetchall()#cursor.stored_results()#result.fetchall()
        for det in details:
            print(det)
        # To close the connection
        cursor.close()
        conn.close()
 
 
    def Add(objdu):
        """
        添加
        :param objdu: 书目录类
        :return:
        """
        # To connect MySQL database
        conn = pymysql.connect(
             host='localhost',
             user='root',
             password="geovindu",
             db='geovindu',
        )
        #print(type(objdu.getBookKindName()),objdu.getBookKindParent())
        cursor = conn.cursor() #prepared=True
        args=(objdu.getBookKindName(), objdu.getBookKindParent())
        cursor.callproc('proc_Insert_BookKindList', args)
        conn.commit()
        # print results
        print("Printing laptop details")
        #for result in cursor.stored_results():
            #print(result.fetchall())
            # To close the connection
        cursor.close()
        conn.close()
 
    def Addstr(BookKindID, BookKindName, BookKindParent):
        """
        添加
        :param BookKindName:
        :param BookKindParent:
        :return:
        """
        # To connect MySQL database
        conn = pymysql.connect(
             host='localhost',
             user='root',
             password="geovindu",
             db='geovindu',
        )
 
        cursor = conn.cursor()#prepared=True
        args = (BookKindName, BookKindParent)
        cursor.callproc('proc_Insert_BookKindList', args)
        conn.commit()
        # print results
        print("Printing laptop details")
        #for result in cursor.stored_results():
            #print(result.fetchall())
            # To close the connection
        cursor.close()
        conn.close()

调用：

import MySQLDAL
import BookKind
 
  MySQLDAL.sqlDAL.mysqlconnect()
    #MySQLDAL.sqlDAL.Addstr(0,"文学",1)
    tu.append(BookKind.BookKindList(0,"科学",1))
 
    MySQLDAL.sqlDAL.Add(BookKind.BookKindList(0,"科学",1))
    MySQLDAL.sqlDAL.select()
    MySQLDAL.sqlDAL.selectdu()