Python3.7操作DB2数据库

Python3.7操作DB2数据库

要在Python中操作DB2数据库，你可以使用IBM提供的 ibm_db 或 ibm_db_sa 包。以下是基本的步骤：

1、安装 ibm_db 包

pip install ibm_db

2、连接到DB2数据库

import ibm_db

conn_str = "DATABASE=<your_database_name>;HOSTNAME=<your_host>;PORT=<your_port>;PROTOCOL=TCPIP;UID=<your_username>;PWD=<your_password>;" conn = ibm_db.connect(conn_str, "", "")

请替换 <your_database_name>, <your_host>, <your_port>, <your_username>, <your_password> 分别为你的数据库名称、主机名、端口号、用户名和密码。

3、执行SQL语句

stmt = ibm_db.exec_immediate(conn, "SELECT * FROM your_table")

result = ibm_db.fetch_assoc(stmt)

while result:

print(result)

result = ibm_db.fetch_assoc(stmt)

这里的 your_table 是你想要查询的表名。

4、插入数据

insert_query = "INSERT INTO your_table (column1, column2) VALUES (?, ?)"

stmt = ibm_db.prepare(conn, insert_query)

data_to_insert = ('value1', 'value2')

ibm_db.execute(stmt, data_to_insert)

这里的 your_table 是你表名。

这里的 column1, column2 是你表中的列名。

5、关闭连接

ibm_db.close(conn)

请确保在使用密码等敏感信息时采取适当的安全措施，例如使用配置文件或环境变量。另外，建议使用上下文管理器或 try/finally 块来确保在发生错误时正确关闭数据库连接。

安装ibm_db和pandas请参考：Linux Centos7安装python3.7和安装pykmip-CSDN博客

6、示例

示例一

#_*_coding:utf-8_*_
import pandas as pd
import ibm_db
import ibm_db_dbi

import os

# import sys
# import importlib
# importlib.reload(sys)
# sys.setdefaultencoding("utf-8")

os.environ['NLS_LANG'] = 'zh_CN.UTF8'

ibm_db_conn = ibm_db.connect("DATABASE="+"sample"+";HOSTNAME="+"10.1.1.38"+";PORT="+"50000"+";PROTOCOL=TCPIP;UID="+"db2inst1"+";PWD="+"xxxxxx"+";", "","", charset='utf8', encoding='utf8')
conn = ibm_db_dbi.Connection(ibm_db_conn)

insert_sql = "INSERT INTO db2inst1.DB2_VARCHAR (aes, sm4, sm4_a, email, phone, ssn, military, passport, intelssn, intelpassport, intelmilitary, intelganghui," \
             " inteltaitonei, credit_card_short, credit_card_long, job,sm4_cbc,sm4_a_cbc) VALUES ('xxxx', '北京xxxx有限公司', '北京市', 'cpq@yahoo.com', '15652996964'," \
             " '210302199608124861', '武水电字第3632734号', 'BWP018930705', '210302199608124861', 'BWP018930705', '武水电字第3632734号', 'H21157232', " \
             "'9839487602', '117', '6227612145830440', '测试开发工程师','北京xxxxx有限公司','产品研发部')"
print(insert_sql)
stmt = ibm_db.exec_immediate(ibm_db_conn,insert_sql)

#select_sql = "SELECT id, aes, sm4, sm4_a, email, phone, ssn, military, passport, intelssn, intelpassport, intelmilitary, intelganghui, " \
              #              "inteltaitonei, credit_card_short, credit_card_long, job FROM DB2_VARCHAR"
#select_sql ="select * from DB2_VARCHAR where sm4_a like '北%'"
# where sm4_a like '北%'
select_sql ="select * from db2inst1.DB2_VARCHAR"


print(select_sql)
pd.set_option('display.width', 1000, 'display.max_rows', 1000000,'display.max_columns', None, 'display.encoding', 'utf-8')
select_result = pd.read_sql(select_sql, conn)
print(select_result)

ibm_db.close(ibm_db_conn)

示例二，预编译插入

#_*_coding:utf-8_*_
import pandas as pd
import ibm_db
import ibm_db_dbi

import os

# import sys
# import importlib
# importlib.reload(sys)
# sys.setdefaultencoding("utf-8")

os.environ['NLS_LANG'] = 'zh_CN.UTF8'

ibm_db_conn = ibm_db.connect("DATABASE="+"sample"+";HOSTNAME="+"10.1.1.38"+";PORT="+"50000"+";PROTOCOL=TCPIP;UID="+"db2inst1"+";PWD="+"lianshi"+";", "","", charset='utf8', encoding='utf8')
conn = ibm_db_dbi.Connection(ibm_db_conn)

insert_sql = "INSERT INTO db2inst1.DB2_VARCHAR (aes, sm4, sm4_a, email, phone, ssn, military, passport, intelssn, intelpassport, intelmilitary, intelganghui, inteltaitonei, credit_card_short, credit_card_long, job,sm4_cbc,sm4_a_cbc) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
stmt = ibm_db.prepare(ibm_db_conn, insert_sql)
value=('HMF', '北京xxx有限公司', '北京市', 'cpq@yahoo.com', '15652996964', '210302199608124861', '武水电字第3632734号', 'BWP018930705', '210302199608124861', 'BWP018930705', '武水电字第3632734号', 'H21157232', '9839487602', '117', '6227612145830440', '测试开发工程师','北京XXX有限公司','产品研发部')
ibm_db.execute(stmt, value)

#select_sql = "SELECT id, aes, sm4, sm4_a, email, phone, ssn, military, passport, intelssn, intelpassport, intelmilitary, intelganghui, " \
              #              "inteltaitonei, credit_card_short, credit_card_long, job FROM DB2_VARCHAR"
#select_sql ="select * from DB2_VARCHAR where sm4_a like '北%'"
# where sm4_a like '北%'
select_sql ="select * from db2inst1.DB2_VARCHAR"


print(select_sql)
pd.set_option('display.width', 1000, 'display.max_rows', 1000000,'display.max_columns', None, 'display.encoding', 'utf-8')
select_result = pd.read_sql(select_sql, conn)
print(select_result)

ibm_db.close(ibm_db_conn)