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)