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)
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号