python: read mysql

sql:

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

　　

 输出：

(('8.0.32',),)
<class 'str'> 1
Printing laptop details
Printing laptop details
1 六福书目录 0
6 文学 1
7 科学 1
Printing laptop details
(1, '六福书目录', 0)
(6, '文学', 1)
(7, '科学', 1)