使用Python创建各式数据库连接

本文主要描述各种数据库产品的python连接方式。大部分连接以sqlalchemy为核心，调用不同的驱动，创建连接提供pandas或其他使用。
所以使用的大前提下要安装sqlalchemy以及对应sql产品的依赖包
pip install sqlalchemy

mysql

pip install mymysql

# mysql版获获取连接方式
# pip install mymysql
def getMysqlConnection(host, port, dbname, user, password):
    engine = create_engine('mysql+pymysql://'+user+':'+password+'@'+host+':'+str(port)+'/'+dbname, poolclass=NullPool)
    conn = engine.connect()
    return conn

oracle

pip install cx_oracle

# oracle的连接方式
# pip install cx_oracle
def getOracleConnection(host, port, dbname, user, password):
    engine = create_engine('oracle://'+user+':'+password+'@'+host+':'+port+'/'+dbname, echo=True)
    conn = engine.connect()
    return conn

postgresql

pip install psycopg2

# PG获取连接方式
# pip install psycopg2
def getPGConnection(host, port, dbname, user, password, schema=None):
    engine = create_engine('postgresql://'+user+':'+password+'@'+host+':'+str(port)+'/'+dbname, poolclass=NullPool)
    conn = engine.connect()
    if(schema != None):
        conn.execute('set search_path='+schema)
    return conn

国产达梦

pip install dmPython

# 达梦数据库的连接方式
# 达梦数据库默认根据用户名连数据库实例。所以这里无需dbname，但为了不影响python其他方法，默认dbname传空
# pip install dmPython
def getDMConnection(host, port, dbname, user, password):
    properties = { 'user' : user, 'password' : password, 'server' : host,
                   'port' : port, 'autoCommit' : True, }
    conn = dmPython.connect(**properties)
    return conn

整合

# 获取连接
def getConnection(host, port, dbname, user, password,type='mysql'):
    if type == 'mysql':
        return getMysqlConnection(host, port, dbname, user, password)
    elif type == 'oracle':
        return getOracleConnection(host, port, dbname, user, password)
    elif type == 'pg':
        return getPGConnection(host, port, dbname, user, password)
    elif type == 'dm':
        return getDMConnection(host, port, dbname, user, password)