Python(29):Python3.7操作DB2数据库

宁宁可可

已于 2024-01-08 15:37:23 修改

阅读量1k

点赞数 7

分类专栏： python相关文章标签：数据库 python

于 2023-12-13 14:39:12 首次发布

本文链接：https://blog.csdn.net/fen_fen/article/details/134971449

版权

python相关专栏收录该内容

44 篇文章 0 订阅

订阅专栏

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="+"xxx"+";", "","", 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)

示例三：

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

import os


class DB2utils(object):
    os.environ['NLS_LANG'] = 'zh_CN.UTF8'

    def __init__(self, DATABASE, HOSTNAME, PORT,USER_NAME,PWD):
        self.DATABASE = DATABASE
        self.HOSTNAME = HOSTNAME
        self.PORT = PORT
        self.USER_NAME = USER_NAME
        self.PWD = PWD
        self.conn_str=f"DATABASE={self.DATABASE};HOSTNAME={self.HOSTNAME};PORT={self.PORT};PROTOCOL=TCPIP;UID={self.USER_NAME};PWD={self.PWD};"
        print(self.conn_str)

    def db2_insert(self,insert_sql):
        try:
            ibm_db_conn = ibm_db.connect(self.conn_str, "", "", charset='utf8', encoding='utf8')
            if ibm_db_conn:
                # 插入
                stmt = ibm_db.exec_immediate(ibm_db_conn, insert_sql)
                ibm_db.close(ibm_db_conn)
        except Exception as ex:
            print(str(ex))

    def db2_select(self,select_sql):
        print(select_sql)
        try:
            ibm_db_conn = ibm_db.connect(self.conn_str, "", "", charset='utf8', encoding='utf8')
            if ibm_db_conn:
                conn = ibm_db_dbi.Connection(ibm_db_conn)
                # 查询
                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)
        except Exception as ex:
            print(str(ex))




DATABASE = "sample"
HOSTNAME = "10.1.1.38"
PORT = "50000"
USER_NAME = "appuser"
PWD = "xxx"
insert_sql = "INSERT INTO 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 ('HMF', '北京xxx技术有限公司', '北京市', 'cpq@yahoo.com', '15652996964'," \
             " '210302199608124861', '武水电字第3632734号', 'BWP018930705', '210302199608124861', 'BWP018930705', '武水电字第3632734号', 'H21157232', " \
             "'9839487602', '117', '6227612145830440', '软件测试工程师','北京xxx技术有限公司','产品研发部')"
select_sql ="select * from DB2_VARCHAR"

db2_test = DB2utils(DATABASE, HOSTNAME, PORT, USER_NAME, PWD)

db2_test.db2_insert(insert_sql)
db2_test.db2_select(select_sql)