python thrift 同时处理n个连接_python 连接常见数据库获取表名以及字段名

因为我是需要统一调用的，所以后面改成了函数写的。

Python连接hive

TTransportException(type=1, message="Could not start SASL: b'Error in sasl_client_start (-4) SASL(-4): no mechanism available: Unable to find a callback: 2'")

经过各种折腾，现在总算是解决了。这里把解决的方法共享一下： 原因是由于SASL 与PURE-SASL包有冲突 。需要检查一下是否有SASL包，如果有的话，卸载掉就可以了。

TypeError: can’t concat str to bytes

定位到错误的最后一条，在init.py第94行

... header = struct.pack(">BI", status, len(body)) self._trans.write(header + body) ...

修改为

... header = struct.pack(">BI", status, len(body)) if(type(body) is str): body = body.encode()

最终代码：

import pprint
from impala.dbapi import connect

conn = connect(host='192.168.44.95', port=10000, auth_mechanism='PLAIN', database='civil_db')
cursor = conn.cursor()

cursor.execute('show tables')
all_data = cursor.fetchall()
tabList = []
for data in all_data:
    tabList.append(data[0])
print('tabList(%d):n%sn' % (len(tabList), pprint.pformat(tabList, indent=4)))


for tabName in tabList:
    print('table %s ...' % tabName)
    sql = 'desc %s'
    sql = sql % (tabName)
    cursor.execute(sql)
    rowList = cursor.fetchall()
    title = [i[0] for i in cursor.description]
    print(title)
    fieldList = list()
    for row in rowList:
        fieldList.append(row[0])
    print('fieldList(%d):n%sn' % (len(fieldList), pprint.pformat(fieldList, indent=4)))

cursor.close()
conn.close()

# cursor.execute('SHOW Tables')
# print(cursor.fetchall())

python连接hbase

错误 ：thrift.transport.TTransport.TTransportException: Could not connect to any of [('192.168.44.94', 9090)]

应该是需要在装有hbase的那台服务器上安装thrift之后，python才能连接到hbase吧。

python连接mysql

import pymysql
import pprint
#
# HOST = '127.0.0.1'
# PORT = 3306
# USER = 'root'
# PASSWD = 'root'
# dbName = 'mysql'


def conn_mysql(host,port,user,passwd,dbname):
    conn = pymysql.connect(host=host,
                           port=port,
                           user=user,
                           passwd=passwd,
                           db=dbname,
                           charset='utf8')
    cursor = conn.cursor()

    # def show_tables():
    sql = 'show tables;'
    cursor.execute(sql)
    rowList = cursor.fetchall()
    tableList = list()
    for row in rowList:
        tableList.append(row[0])
    print('tableList(%d):n%sn' % (len(tableList), pprint.pformat(tableList, indent=4)))
    # return tableList


    # 处理每个表
    # def show_colunms(tableList):
    for tabName in tableList:
        print('table %s ...' % tabName)
        sql = "select column_name from information_schema.columns where table_schema='%s' and table_name='%s';"
        sql = sql % (dbname, tabName)
        cursor.execute(sql)
        rowList = cursor.fetchall()
        fieldList = list()
        for row in rowList:
            fieldList.append(row[0])
        print('fieldList(%d):n%sn' % (len(fieldList), pprint.pformat(fieldList, indent=4)))

    cursor.close()
    conn.close()


# conn_mysql(HOST,PORT,USER,PASSWD,dbName)

python 连接sqlserver

import pymssql
import pprint

# host = "localhost"
# port = '1433'
# user = "sa"
# password = "root"
# database = "python_test"
# charset = "utf8"


def conn_sqlserver(host,port,user,password,database):
    port = str(port)
    host = host+':'+port
    connect = pymssql.connect(host=host, user=user, password=password,
                              database=database, charset='utf8')
    if connect:
        print("连接成功!")
    cursor = connect.cursor()  # 创建一个游标对象,python里的sql语句都要通过cursor来执行
    cursor.execute("select name from sysobjects where xtype='U'")  # 执行sql语句,获取数据库中的表名
    rowList = cursor.fetchall()
    tableList = []
    for row in rowList:
        tableList.append(row[0])
    print('tableList(%d):n%sn' % (len(tableList), pprint.pformat(tableList, indent=4)))

    # 处理每个表
    for tabName in tableList:
        print('table %s ...' % tabName)
        sql = "select name from syscolumns where id = object_id('%s')"
        sql = sql % (tabName)
        cursor.execute(sql)
        rowList = cursor.fetchall()
        fieldList = list()
        for row in rowList:
            fieldList.append(row[0])
        print('fieldList(%d):n%sn' % (len(fieldList), pprint.pformat(fieldList, indent=4)))
    cursor.close()  # 关闭游标
    connect.close()  # 关闭连接


# conn_sqlserver(host,password,port,user,database)

python 连接oracle

import cx_Oracle
import pprint

# user = 'scott'
# passwd = 'tiger'
# host = '127.0.0.1'
# port = '1521'
# dbname = 'orcl'


def conn_oracle(host,port,user,passwd,dbname):
    port = str(port)
    host_port = user+'/'+passwd+'@'+host+':'+port+'/'+dbname
    # conn = cx_Oracle.connect("user/tiger@127.0.0.1:1521/orcl")
    conn = cx_Oracle.connect("%s" %host_port)
    cursor = conn.cursor()

    # 获取当前用户下的所有表的信息
    # def conn_oracle():
    results = cursor.execute('select table_name from user_tables')
    # 获取所有数据
    all_data = cursor.fetchall()
    tabList = []
    for data in all_data:
        tabList.append(data[0])
    print('tabList(%d):n%sn' % (len(tabList), pprint.pformat(tabList, indent=4)))

    # 处理每个表
    for tabName in tabList:
        print('table %s ...' % tabName)
        sql = 'select * from "%s" '
        sql = sql % (tabName)
        cursor.execute(sql)
        rowList = cursor.fetchall()
        title = [i[0] for i in cursor.description]
        print(title)
        # fieldList = list()
        # for row in rowList:
        #     fieldList.append(row[0])
        # print('fieldList(%d):n%sn' % (len(fieldList), pprint.pformat(fieldList, indent=4)))
    cursor.close()
    conn.close()
# conn_oracle(user,passwd,host,port,dbname)