python-连接数据库的公用方法

时间：2024-03-31 19:01:25

1、创建数据库连接方法：以mysql为例，连接成功之后输出特定表结构

# ==============================================================
# -*- coding: utf-8 -*-
# ==============================================================
import json
import pymysql

def conn_mysql(con):
    db = pymysql.connect(con[0], con[1], con[2], con[3])
    cursor = db.cursor()
    sql_dict = input("请输入表名，按回车结束：\n")
    table_sql = "SELECT TABLE_NAME FROM `INFORMATION_SCHEMA`.`TABLES` WHERE table_schema = '"+con[3]+"' and table_name = '"+sql_dict+"'"
    cursor.execute(table_sql)
    table_data = cursor.fetchall()
    table_len = len(table_data)
    if table_len == 0:
        print("输入的表名在指定数据库中不存在,请重新输入表！")
        print("======即将重新开始=======")
        from db_conn.maker import Start
        Start()
        # return
    else:
        SQL = "DESC "+sql_dict
        cursor.execute(SQL)
        data = cursor.fetchall()
        d = []
        for i in data:
            b = {}
            b['Field'] = i[0]
            b['Type'] = i[1]
            b['Null'] = i[2]
            b['Key'] = i[3]
            b['Default'] = i[4]
            d.append(b)
            jsonStr = json.dumps(d)
        print("表结构：%s" % jsonStr)
        exit()
def conn_sqlserver():
    pass
def conn_oracle():
    pass
def conn_postgresql():
    pass

2、创建数据库类：处理连接信息

# ==============================================================
# -*- coding: utf-8 -*-
# ==============================================================
from db_conn.fun_conn import conn_mysql

class DbConn(object):
    def __init__(self,ConnInfo):
        self.dbType = ConnInfo[0]
        self.dbHost = ConnInfo[1]
        self.user = ConnInfo[2]
        self.password = ConnInfo[3]
        self.dbName = ConnInfo[4]
    def connDb(self):
        if self.dbType == 'mysql':
            print("要连接的数据类型为：%s" % self.dbType)
            print("用户名：%s"% self.user)
            print("密码：%s"%self.password)
            print("连接数据库为：%s"% self.dbName)
            con = [self.dbHost,self.user,self.password,self.dbName]
            conn_mysql(con)
        elif self.dbType == 'sqlserver':
            print("sqlserver")
        elif self.dbType == 'oracle':
            print("oracle")
        elif self.dbType == 'pg':
            print("pgSql")
        else:
            print("不存在的数据库类型！")
            from db_conn.maker import Start
            Start()

3、连接：（1）实例化类；（2）调用类中的连接信息处理方法，根据连接类型调用不同数据库连接方法；

# ==============================================================
# -*- coding: utf-8 -*-
# ==============================================================
from db_conn.class_conn import DbConn

# ConnInfo = ['mysql','localhost','root','Aa111111','db_jxc']
# mysql = DbConn(ConnInfo)
# mysql.connDb()

def Start():
    # list = []
    # dbtypt = input("数据库类型：\n")
    # list.append(dbtypt)
    # dbaddress= input("服务地址：\n")
    # list.append(dbaddress)
    # dbuser = input("用户名：\n")
    # list.append(dbuser)
    # dbpwd = input("密码：\n")
    # list.append(dbpwd)
    # dbname = input("数据库名：\n")
    # list.append(dbname)
    # ConnInfo = list
    ConnInfo = ['mysql', 'localhost', 'root', 'Aa111111', 'db_jxc']
    mysql = DbConn(ConnInfo)
    mysql.connDb()

Start()

4、执行效果：

python-连接数据库的公用方法

相关文章