python: sqlhelper

import pymysql


def connect(*args, **kwargs):
    connection = pymysql.connect(*args, **kwargs)
    cur = connection.cursor()
    return SQLHelper(connection, cur)


class SQLHelper(object):
    delimiter = ","
    SQL_TABLE_NAME = []
    SQL_ROW_VALUE = []

    def __init__(self, connection, cur):
        self.connection = connection
        self.cur = cur

    def insert(self, table, **kwargs):
        print(kwargs)
        for key in kwargs:
            self.SQL_TABLE_NAME.append(key)
            self.SQL_ROW_VALUE.append("\'"+kwargs[key]+"\'")
        self.SQL_TABLE_NAME = self.delimiter.join(self.SQL_TABLE_NAME)
        self.SQL_ROW_VALUE = self.delimiter.join(self.SQL_ROW_VALUE)
        print(self.SQL_TABLE_NAME)
        sql = "INSERT INTO " + table + " ( " + self.SQL_TABLE_NAME + " ) VALUES ( " + self.SQL_ROW_VALUE + " );"
        print(sql)
        self.cur.execute(sql)

    def close(self):
        self.cur.close()
        self.connection.close()

SQL Server

"""
SQLServerDAL.py
读取SQL Server数据
date 2023-06-13
edit: Geovin Du,geovindu, 涂聚文
IDE: pycharm edit python 11
如何写一个SQLHelper

"""

import os
import sys
from pathlib import Path
import re
import pymssql  #sql server
import pymysql
import Insurance



class SQLClass(object):
    """
    Sql server
    考虑从配置文件读取数据库的连接

    """
    def __init__(self, strserver, struser, strpwd,strdatabase):
        """

        :param strserver:
        :param struser:
        :param strpwd:
        :param strdatabase:
        """
        self._strserver=strserver
        self._struser=struser
        self._strpwd=strpwd
        self._strdatabase=strdatabase


    def select(self):
        """
        查询所有记录
         
        """
        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor() 
        cursor.execute('select * from InsuranceMoney;') 
        row = cursor.fetchone() 
        while row: 
            print(str(row[0]) + " " + str(row[1]) + " " + str(row[2]))    
            row = cursor.fetchone() 
 
    def insert(self,iobject):
        """
        插入操作
        param:iobject 输入保险类
         
        """
        dubojd=Insurance.InsuranceMoney(iobject)
        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor() 
        cursor.execute("insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES ('{0}', {1}, {2})".format(dubojd.getInsuranceName, dubojd.getInsuranceCost,dubojd.getIMonth)) 
        row = cursor.fetchone() 
        while row: 
            print("Inserted InsuranceMoney ID : " +str(row[0]))
            row = cursor.fetchone() 
        conn.commit()
        conn.close()
     
    def insertStr(self,InsuranceName,InsuranceCost,IMonth):
        """
        插入操作
        param:InsuranceName
        param:InsuranceCost
        param:IMonth
 
        """
        conn = pymssql.connect(
            server=self._strserver,
            user=self._struser,
            password=self._strpwd,
            database=self._strdatabase
        )
        cursor = conn.cursor() 
        cursor.execute("insert into InsuranceMoney(InsuranceName,InsuranceCost,IMonth) OUTPUT INSERTED.ID VALUES('{0}',{1},{2})".format(InsuranceName, InsuranceCost,IMonth)) 
        row = cursor.fetchone() 
        while row: 
            print("Inserted InsuranceMoney ID : " +str(row[0]))
            row = cursor.fetchone() 
        conn.commit()
        conn.close()

MySQL

"""
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 __init__(self, strserver, struser, strpwd,strdatabase):
        """

        :param strserver:
        :param struser:
        :param strpwd:
        :param strdatabase:
        """
        self._strserver=strserver
        self._struser=struser
        self._strpwd=strpwd
        self._strdatabase=strdatabase
    def mysqlconnect(self):
        """
        连接MySQL 检测其版本
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host=self._strserver, #'localhost',
            user=self._struser,#'root',
            password=self._strpwd,#"geovindu",
            db=self._strdatabase #'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(self):
        """
        查询
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host=self._strserver, #'localhost',
            user=self._struser,#'root',
            password=self._strpwd,#"geovindu",
            db=self._strdatabase #'geovindu',
        )

        cursor = conn.cursor()
        cursor.callproc('proc_Select_BookKindListAll')
        # print results
        print("Printing BookKind 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(self):
        """
        查询
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host=self._strserver, #'localhost',
            user=self._struser,#'root',
            password=self._strpwd,#"geovindu",
            db=self._strdatabase #'geovindu',
        )

        cursor = conn.cursor()
        cursor.callproc('proc_Select_BookKindListAll')
        # print results
        print("Printing BookKind 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(self,objdu):
        """
        添加
        :param objdu: 书目录类
        :return:
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host=self._strserver, #'localhost',
            user=self._struser,#'root',
            password=self._strpwd,#"geovindu",
            db=self._strdatabase #'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 BookKind details")
        # for result in cursor.stored_results():
        # print(result.fetchall())
        # To close the connection
        cursor.close()
        conn.close()



    def Addstr(self,BookKindID, BookKindName, BookKindParent):
        """
        添加
        :param BookKindName:
        :param BookKindParent:
        :return:
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host=self._strserver, #'localhost',
            user=self._struser,#'root',
            password=self._strpwd,#"geovindu",
            db=self._strdatabase #'geovindu',
        )

        cursor = conn.cursor()  # prepared=True
        args = (BookKindName, BookKindParent)
        cursor.callproc('proc_Insert_BookKindList', args)
        conn.commit()
        # print results
        print("Printing BookKind details")
        # for result in cursor.stored_results():
        # print(result.fetchall())
        # To close the connection
        cursor.close()
        conn.close()

    def edit(self,objdu):
        """
        修改
        :param objdu: 书目录类
        :return:
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host=self._strserver, #'localhost',
            user=self._struser,#'root',
            password=self._strpwd,#"geovindu",
            db=self._strdatabase #'geovindu',
        )
        # print(type(objdu.getBookKindName()),objdu.getBookKindParent())
        cursor = conn.cursor()  # prepared=True
        args = (objdu.getBookKindID(), objdu.getBookKindName(), objdu.getBookKindParent())
        cursor.callproc('proc_Update_BookKindList', args)
        conn.commit()
        # print results
        print("Printing BookKind details")
        # for result in cursor.stored_results():
        # print(result.fetchall())
        # To close the connection
        cursor.close()
        conn.close()

    def delid(self,kindID):
        """
        修改
        :param kindID: 书目录类
        :return:
        """
        # To connect MySQL database
        conn = pymysql.connect(
            host=self._strserver, #'localhost',
            user=self._struser,#'root',
            password=self._strpwd,#"geovindu",
            db=self._strdatabase #'geovindu',
        )
        # print(type(objdu.getBookKindName()),objdu.getBookKindParent())
        cursor = conn.cursor()  # prepared=True
        args = (kindID)
        cursor.callproc('proc_Update_BookKindList', args)
        conn.commit()
        # print results
        print("Printing BookKind details")
        # for result in cursor.stored_results():
        # print(result.fetchall())
        # To close the connection
        cursor.close()
        conn.close()

调用：

    my=MySQLDAL.sqlDAL('localhost','root','geovindu','geovindu')
    print(type(my))
    my.mysqlconnect();
    my.edit(bookkind.BookKindList(2, "文学", 1))
    my.Add(bookkind.BookKindList(0, "社会科学", 3))
    my.select()
    my.selectdu()

https://github.com/aws-samples/aws-serverless-app-with-aurora-and-python-sql-alchemy-example
https://tortoise.github.io/
http://sqlobject.org/SQLObject.html
https://opensource.com/article/17/11/django-orm
https://www.libhunt.com/l/python/topic/orm Top 23 Python ORM Projects
https://www.monocubed.com/blog/top-python-frameworks/
https://github.com/encode/orm
https://github.com/nebula-contrib/nebula-carina
https://github.com/vinta/awesome-python
https://geekflare.com/best-python-frameworks/
Django
Peewee
InfluxDB
SQLAlchemy
MongoEngine
tortoise-orm
PonyORM
GINO
Sonar
PynamoDB https://www.libhunt.com/r/PynamoDB
sandman2 https://www.libhunt.com/r/sandman2 https://github.com/jeffknupp/sandman2
django-cacheops https://www.libhunt.com/r/django-cacheops
orm https://www.libhunt.com/r/encode/orm
Orator
ormar
beanie
prisma-client-py
walrus
piccolo
django-mongodb-engine
odmantic
aerich
sqlalchemy-mixins
pyDAL
μMongo
SaaSHub

https://github.com/kenjyco/sql-helper
https://github.com/VinceBarry/PyMySQLHelper/blob/master/SQLHelper.py
https://github.com/campbsb/PySqlHelper/blob/master/sql_helper_mysql.py
https://programs.team/sqlhelper-python-implementation.html

pip install pymssql
pip install pysqlite3
pip install pymysql
pip install pyodbc

import pymysql
import threading
from DBUtils.PooledDB import PooledDB
import greenlet


POOL = PooledDB(
            creator=pymysql,  # Modules that use linked databases
            maxconnections=6,  # The maximum number of connections allowed by the connection pool, 0 and None means unlimited connections
            mincached=2,  # When initialized, at least the link created in the link pool, 0 means not created
            blocking=True,  # Whether to block and wait if there is no available connection in the connection pool.  True, wait; False, do not wait and report an error
            ping=0,
            # ping MySQL On the server side, check if the service is available.# For example: 0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host='127.0.0.1',
            port=3306,
            user='root',
            password='000000',
            database='flask_day1',
            charset='utf8'
        )



class SqlHelper(object):
    def __init__(self):
        self.conn = None
        self.cursor = None


    def open(self):
        conn = POOL.connection()
        cursor = conn.cursor()

        return conn,cursor

    def close(self,cursor,conn):
        cursor.close()
        conn.close()

    def __enter__(self):

        self.conn,self.cursor = self.open()
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):

        self.close(self.cursor,self.conn)

posted @ 2023-06-16 08:05 ®Geovin Du Dream Park™ 阅读(85) 评论(0) 编辑收藏举报

®Geovin Du Dream Park™

why we only heard about haves and have-nots, but we did'nt heard about doers and doer-nots. 人生是一种心境,生活是一种艺术,成功是一种心态,幸福是一种感觉,竞争是一种建构,情感是一种容合.学习是一种成长.

python: sqlhelper

公告