Python访问数据库——包括HANA、ORACLE、MYSQL等

写在最前边的话，这篇文章非原创，是同事整理的连接包括hana之内的数据库，例子也是以HANA为例，做个记录。

一、Python访问数据库

Python 标准数据库接口为 Python DB-API，Python DB-API为开发人员提供了数据库应用编程接口。

Python 数据库接口支持非常多的数据库，不同的数据库你需要下载不同的DB API模块，例如你需要访问Oracle数据库和Mysql数据，你需要下载Oracle和MySQL数据库模块。

常用的数据库DB API模块

数据库类型	DB API 模块
MySQL	pymysql
Oracle	cx_Oracle
PostgreSQL	psycopg2
SQL server	pymssql
SAP HANA	hdbcli
。。。。

安装方式，以安装操作 MySQL 数据库的 pymysql 模块为例，在终端中执行：pip install pymysql

在使用 pip 安装第三方包时：我们会发现安装速度极慢且经常出现失败，原因是安装源为国外网站，访问不畅，我们可以将 pip 的软件安装源更改为国内镜像，以提升下载安装速度，操作过程详见：更改 pip 源为国内镜像

DB-API 是一个规范. 它定义了一系列必需的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口 。

Python的DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。

Python DB-API使用流程：

• 引入 API 模块。

• 获取与数据库的连接。

• 执行SQL语句和存储过程。

• 关闭数据库连接。

二、使用 SQLAlchemy

SQLAlchemy是一个python语言实现的的针对关系型数据库的orm库，可用于连接大多数常见的数据库，比如Postges、MySQL、SQLite、Oracle等，它可以将数据库对象映射成Python中的类，使得我们在Python代码中不再需要关注不同数据库本身的特性，而是像处理python对象的形式来与数据库完成交互。

SQLAlchemy提供了两种与数据库交互的方式：

1. SQL语句

2. ORM

关于 ORM 模式的使用介绍，有兴趣可参考此文档：Python库之SQLAlchemy - 知乎

这种方式适合如前端网页等开发项目，在此不做介绍，我们主要讲解SQL语句交互模式。

1.SQLAlchemy 连接数据库

为了后续大家使用方便，我这里直接把几种主流关系型数据库和我们最常用的HANA数据库的连接方式全部列举出来了，大家可以直接参考

注意：

• sqlalchemy 没有直接支持 hana，但有人造好了让它能支持的轮子： sqlalchemy-hana，所以在使用 sqlalchemy 操作hana时，除了需要安装 hana 的 DB API模块外，还需要安装 sqlalchemy-hana

• python操作Oracle数据库除了要安装对应的包外，还需要额外安装  Oracle Instant Client，具体的安装步骤见：cx_Oracle 8 Installation — cx_Oracle 8.3.0 documentation

import cx_Oracle  # 连接Oracle数据库专用
from sqlalchemy import create_engine


# 数据库连接配置
db_type ='saphana'

ip = ''
port = '30015'
user = ''
password = ''
db = 'db_name'
#Oracle数据库
tnsname = 'tns_name'

# 针对不同的数据库类型，构造不同的数据库连接配置字符串
# 实际使用中大家针对各自要连接的数据库类型选择即可。
if db_type == 'saphana':
    # 需要安装以包/模块
    # pip install sqlalchemy-hana
    # pip install hdbcli
    config_str = "hana://{}:{}@{}:{}".format(user, password, ip, port)
elif db_type == 'mysql':
    # pip install pymysql
    config_str = "mysql+pymysql://{}:{}@{}:{}/{}".format(user, password, ip, port, db)
elif db_type == 'sqlserver':
    # pip install pymssql
    config_str = 'mssql+pymssql://{}:{}@{}:{}/{}?charset=utf8'.format(user, password, ip, port, db)
elif db_type == 'oracle':
    # pip install cx_Oracle
    
    # 第一次执行访问Oracle数据库的程序时需添加如下一行代码，
    # 以指定Oracle Instant Client的安装位置
		cx_Oracle.init_oracle_client("D:/Program Files/oracle/instantclient_21_9")
    
    dsnStr = cx_Oracle.makedsn(ip, port, service_name=tnsname)
    config_str = "oracle://%s:%s@%s" % (user, password, dsnStr)
elif db_type == 'postgresql':
    # pip install psycopg2
    config_str = "postgresql+psycopg2://{}:{}@{}:{}/{}".format(
        user, password, ip, port, db)

# 创建数据库连接引擎
db_engine = create_engine(config_str, encoding='utf-8')

2.读数据库（查询）

# 执行SQL语句

# 查询
sql_str = " SELECT * FROM FINE_REPORT_INFO "
execute_result = db_engine.execute(sql_str)
data = execute_result.fetchall()
data

3.写数据库（插入、更新）

# 写入
sql_str2 = " INSERT INTO PYTHON_TRAIN (ID,NAME,SEX,PHONE) VALUES('111','张三','男',12345678910) "
db_engine.execute(sql_str2)

4.通用做法

最通用的方法是通过Engine.connect()方法获得连接资源，connection 是Connection类的一个实例，是DBAPI连接的一个代理对象。

# 创建数据库连接
conn = db_engine.connect()
sql_str = ''' SELECT * FROM FINE_REPORT_INFO '''
execute_result = conn.execute(sql_str)
data = execute_result.fetchall()
data
# 关闭数据库连接
conn.close()

5.使用事务

Connection对象提供begin()函数来开始一个事务（Transaction）对象，该对象通常用于try/except代码块中，以保证调用Transaction.rollback() 或 Transaction.commit()

conn = db_engine.connect()
# 使用事务，可以在出现异常时进行回滚。
tran = conn.begin()
try:
    sql_str3 = " INSERT INTO PYTHON_TRAIN (ID,NAME,SEX,PHONE) VALUES('111','张三','男',12345678910) "
    conn.execute(sql_str3)
    sql_str4 = " INSERT INTO PYTHON_TRAIN (ID,NAME,SEX,PHONE) VALUES('222','李四','男',1234567890101212) "
    conn.execute(sql_str4)
    tran.commit()
except Exception as e:  # 当try代码块中的代码执行出现任何报错，就会执行except中的代码。
    print(e)
    tran.rollback()
conn.close()