Python ibm_db

(windows不建议用)

原文来源在 Python 中使用 ibm_db 进行应用程序开发

通过调用 ibm_db.fetch_assoc 函数从结果集访存行

import ibm_db

conn = ibm_db.connect("database","username","password")
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_assoc(stmt)
while dictionary != False:
    print "The ID is : ", dictionary["EMPNO"]
    print "The name is : ", dictionary["FIRSTNME"]
    dictionary = ibm_db.fetch_assoc(stmt)

---

2. 原文来源

https://pypi.org/project/ibm-db/

Note: For windows after installing ibm_db, recieves the below error when we try to import ibm_db :

Python 3.11.4 (tags/v3.11.4:d2340ef, Jun  7 2023, 05:45:37) [MSC v.1934 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import ibm_db
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: DLL load failed while importing ibm_db: The specified module could not be found.
>>>

We need to make sure to set dll path of dependent library of clidriver before importing the module as:

import os

os.add_dll_directory('C:\\Program Files\\IBM\\CLIDRIVER\\bin')

import ibm_db

Refer https://bugs.python.org/issue36085 for more details.

$ python

Python 3.6.5 (default, May 10 2018, 00:54:55)

[GCC 4.3.4 [gcc-4_3-branch revision 152973]] on linux

Type "help", "copyright", "credits" or "license" for more information.

>>> import ibm_db

>>> #For connecting to local database named pydev for user db2inst1 and password secret, use below example

>>> #ibm_db_conn = ibm_db.connect('pydev', 'db2inst1', 'secret')

>>> #For connecting to remote database named pydev for uid db2inst and pwd secret on host host.test.com, use below example

>>> # Connect using ibm_db

>>> conn_str='database=pydev;hostname=host.test.com;port=portno;protocol=tcpip;uid=db2inst1;pwd=secret'

>>> ibm_db_conn = ibm_db.connect(conn_str,'','')

>>>

>>> # Connect using ibm_db_dbi

>>> import ibm_db_dbi

>>> conn = ibm_db_dbi.Connection(ibm_db_conn)

>>> # create table using ibm_db

>>> create="create table mytable(id int, name varchar(50))"

>>> ibm_db.exec_immediate(ibm_db_conn, create)

<ibm_db.IBM_DBStatement object at 0x7fcc5f44f650>

>>>

>>> # Execute tables API

>>> conn.tables('DB2INST1', '%')

[{'TABLE_CAT': None, 'TABLE_SCHEM': 'DB2INST1', 'TABLE_NAME': 'MYTABLE', 'TABLE_TYPE': 'TABLE', 'REMARKS': None}]

>>>

>>> # Insert 3 rows into the table

>>> insert = "insert into mytable values(?,?)"

>>> params=((1,'Sanders'),(2,'Pernal'),(3,'OBrien'))

>>> stmt_insert = ibm_db.prepare(ibm_db_conn, insert)

>>> ibm_db.execute_many(stmt_insert,params)

3

>>> # Fetch data using ibm_db_dbi

>>> select="select id, name from mytable"

>>> cur = conn.cursor()

>>> cur.execute(select)

True

>>> row=cur.fetchall()

>>> print("{} \t {} \t {}".format(row[0],row[1],row[2]),end="\n")

(1, 'Sanders') (2, 'Pernal') (3, 'OBrien')

>>> row=cur.fetchall()

>>> print(row)

[]

>>>

>>> # Fetch data using ibm_db

>>> stmt_select = ibm_db.exec_immediate(ibm_db_conn, select)

>>> cols = ibm_db.fetch_tuple( stmt_select )

>>> print("%s, %s" % (cols[0], cols[1]))

1, Sanders

>>> cols = ibm_db.fetch_tuple( stmt_select )

>>> print("%s, %s" % (cols[0], cols[1]))

2, Pernal

>>> cols = ibm_db.fetch_tuple( stmt_select )

>>> print("%s, %s" % (cols[0], cols[1]))

3, OBrien

>>> cols = ibm_db.fetch_tuple( stmt_select )

>>> print(cols)

False

>>>

>>> # Close connections

>>> cur.close()

True

>>> # Dropping the table created

>>> drop = "drop table mytable"

>>> stmt_delete = ibm_db.exec_immediate(ibm_db_conn,drop)

>>> conn1.tables('DB2INST1','MY%')

[]

>>>

>>> ibm_db.close(ibm_db_conn)

True

 

---

Python DB2 基础知识