hiven oracle,pymysql与cx_Oracle与pyhive

pymysql

无论是用pymysql连接MySQL，还是使用cx_Oracle连接Oracle也好，总体流程都是一样的：

1、获取connection

2、获取cursor

3、在cursor上执行相关的操作

在 pymysql 提供了一个很好的使用数据库的示例：

# https://pymysql.readthedocs.io/en/latest/user/examples.html

import pymysql.cursors

# Connect to the database

connection = pymysql.connect(host='localhost',

user='user',

password='passwd',

db='db',

charset='utf8mb4',

cursorclass=pymysql.cursors.DictCursor)

try:

with connection.cursor() as cursor:

# Create a new record

sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

# connection is not autocommit by default. So you must commit to save

# your changes.

connection.commit()

with connection.cursor() as cursor:

# Read a single record

sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"

cursor.execute(sql, ('webmaster@python.org',))

result = cursor.fetchone()

print(result)

finally:

connection.close()

默认情况下 cursor.fetchone() 返回的是一个元组，但是在 pymysql 中可以通过指定 cursorclass=pymysql.cursors.DictCursor 使之返回的是一个字典

cx_Oracle

cx_Oracle本身也自定义了一些类型：

cx_Oracle.jpg

cx_Oracle默认从 cursor 中 fetch 出来的是一个元组，按照SQL中的顺序组织，但是很多时候希望返回的是一个 dict ，然后 cursor.description 为我们提供了足够的信息。

# a sequence of 7-item sequences.

# (name, type, display_size, internal_size, precision, scale, null_ok)

# 每一个元组表示一个字段

# 返回所有的列名

columnNames = [d[0].lower() for d in cursor.description]

# 返回所有的列名与类型

columnNames = [(d[0].lower(), d[1]) for d in cursor.description]

# 将结果构造成 [{}, {}, {}] 的格式

datas = list()

for row in cursor.fetchall():

datas.append(dict(zip(columnNames, row)))

## 或者通过列表表达式

[dict(zip(columnNames, row)) for row in cursor.fetchall()]

# 完整代码

import cx_Oracle

connection = cx_Oracle.connect("****",

"*****",

"14.29.1.27:11526/orcl",

encoding = "GBK",

nencoding = "GBK")

cursor = connection.cursor()

cursor.execute('select * from iptv_app.test_add_pack_plan')

columnNames = [d[0].lower() for d in cursor.description]

datas = [dict(zip(columnNames, row)) for row in cursor.fetchall()]

pyhive

from pyhive import hive

conn = hive.Connection(host='192.168.1.105', port=10000, username='yepeng', database='default')

cursor = conn.cursor()

cursor.execute('select * from student')

for result in cursor.fetchall():

print( result)

当然通过Python去连接Hive也可以通过PySpark

from pyspark.sql import SparkSession

spark = SparkSession \

.builder \

.appName("Python Spark SQL basic example") \

.config("spark.some.config.option", "some-value") \

.getOrCreate()

spark.sql("show databases").show(10)