记录几种常见的python连接数据库的代码,方便用的时候直接取。
1.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%s\n' % (len(tableList), pprint.pformat(tableList, indent=4)))
# return tableList
2.python连接hive
from pyhive import hive
import pandas as pd
ip=''
conn = hive.connect(ip)
def runhql(sql):
cursor = conn.cursor()
cursor.execute(sql)
logs = cursor.fetch_logs()
datas = cursor.fetchall()
print(len(datas))
return datas
3.python连接德鲁伊库
from pydruid.db import connect
def find_druid(ip,port,sql):
conn = connect(host=ip, port=port)
curs = conn.cursor()
curs.execute(sql)
df = pd.DataFrame(curs.fetchall())
return df
4.python连接sqlsever
import pymssql
import pprint
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%s\n' % (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%s\n' % (len(fieldList), pprint.pformat(fieldList, indent=4)))
cursor.close() # 关闭游标
connect.close() # 关闭连接
5.python连接oracle
import cx_Oracle
import pprint
def conn_oracle(host,port,user,passwd,dbname,sql):
port = str(port)
host_port = user+'/'+passwd+'@'+host+':'+port+'/'+dbname
conn = cx_Oracle.connect("%s" %host_port)
cursor = conn.cursor()
# 获取当前用户下的所有表的信息
# def conn_oracle():
results = cursor.execute(sql)
# 获取所有数据
all_data = cursor.fetchall()
tabList = []
for data in all_data:
tabList.append(data[0])
print('tabList(%d):\n%s\n' % (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%s\n' % (len(fieldList), pprint.pformat(fieldList, indent=4)))
cursor.close()
conn.close()
6.python 连接impala查询:文章来源:https://www.toymoban.com/news/detail-528123.html
文章来源地址https://www.toymoban.com/news/detail-528123.html
from impala.dbapi import connect
ip=''
port=''
sql=''
conn = connect(host=ip, port=port)
cur = conn.cursor()
cur.execute(sql)
data_list=cur.fetchall()
for data in data_list:
print data
到了这里,关于python连接数据库的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!