python基于jar包驱动操作mysql数据库

import jpype.imports
from jpype import JInt, JShort
from jpype import dbapi2
import time
print(jpype.getDefaultJVMPath())


jpype.startJVM(classpath=['mysql-connector-java-5.1.49.jar']) 
# python 语法导入java 类
from com.mysql.jdbc import *


class DB():
    def __init__(self, conn_url = "jdbc:mysql://1.1.1.1:3306/smmb?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false"
                 , driver_args={"user":"root","password":"root123"}
                 , driver='com.mysql.jdbc.Driver'):
        # 建立连接 
        self.conn = dbapi2.connect(conn_url,driver=driver,driver_args=driver_args)
        # 创建游标，操作设置为字典类型        
        self.cur = self.conn.cursor()
    

    def __enter__(self):
        # 返回游标        
        return self.cur

    #def __exit__(self, exc_type, exc_val, exc_tb):
    def exit(self):
        # 提交数据库并执行        
        self.conn.commit()
        # 关闭游标        
        self.cur.close()
        # 关闭数据库连接        
        self.conn.close()
    #创建表
    def create(self,table_name,create_sql):
        # 使用 execute() 方法执行 SQL，如果表存在则删除
        drop_sql = 'DROP TABLE IF EXISTS {}'.format(table_name)
        self.cur.execute(drop_sql)
        # 使用预处理语句创建
        self.cur.execute(create_sql)
        self.conn.commit()
        print(table_name,'create table successful ')
    #插入表
    def insert(self,sql):

        try:
   # 执行sql语句
            self.cur.execute(sql)
           # 提交到数据库执行
            self.conn.commit()
            print(sql,'insert successful ')
        except Exception as e:
           # 如果发生错误则回滚
            self.conn.rollback()
            print (sql,"Error: insert failed",e)
        
    #批量插入表
    def inserts(self,sql,val):
        try:
           # 执行sql语句
            self.cur.executemany(sql,val)
           # 提交到数据库执行
            self.conn.commit()
            print(sql,'inserts successful ')
        except Exception as e:
           # 如果发生错误则回滚
            self.conn.rollback()
            print (sql,"Error: inserts failed",e)
    #删除表
    def delete(self,sql):
        try:
   # 执行sql语句
            self.cur.execute(sql)
           # 提交到数据库执行
            self.conn.commit()
            print(sql,'delete successful ')
        except Exception as e:
           # 如果发生错误则回滚
            self.conn.rollback()
            print (sql,"Error: delete failed",e)
    #更新表
    def update(self,sql):
        try:
   # 执行sql语句
            self.cur.execute(sql)
           # 提交到数据库执行
            self.conn.commit()
            print(sql,'update successful ')
        except Exception as e:
           # 如果发生错误则回滚
            self.conn.rollback()
            print (sql,"Error: update failed",e)
    #查询表
    def select(self,sql):
        try:
           # 执行SQL语句
            self.cur.execute(sql)
            col = self.cur.description
           # 获取所有记录列表
            results = self.cur.fetchall()
            print(sql,'select successful ')
            return results,col
        except Exception as e:
            print (sql,"Error: select failed",e)
    
    #查询表结构
    def show(self,table_name):
        show_sql = "show create table  {}".format(table_name)
        try:
           # 执行SQL语句
            self.cur.execute(show_sql)
            #col = self.cur.description
           # 获取所有记录列表
            results = self.cur.fetchall()
            print(table_name,'show successful ')
            return results
        except Exception as e:
            print (table_name,"Error: show failed",e)

        

# if __name__ == '__main__':
#     db = DB()
#     sql = 'select * from EMPLOYEE'
#     result = db.select(sql)
#     db.exit()