Python connects to DB2 database

DB2 is a relational database management system developed by IBM in the United States. Its main operating environments are UNIX (including IBM's own AIX), Linux, IBM i (formerly known as OS/400), z/OS, and Windows Server version. Today we will discuss how to use Python to connect to DB2 database

I encountered such a situation at work. The project needs to connect to IBM's relational database (DB2). There are relatively few libraries in this area, among whichibm_db is a relatively easy-to-use library. There are also tutorials on the Internet, but they seem to be inaccurate, not very detailed, and full of errors. I have no choice but to get it and analyze the source code myself, and finally get it.

Installation

Environmental requirements:

First is the database DB2, download the connection directly to Baidu, I downloaded these two File:

Just download the one pointed by the arrow. I haven’t tested it on Linux yet.

Database API (I have been looking for this thing for a long time, and finally found the right one) (Cannot find search: SQLAPI.zip)

Python2.7

VCForPython2.7

ibm_db (the main library, the ntx64_odbc_cli library will be downloaded during installation, and the IBM_DB_HOME variable will be detected during installation, so you need to install the database before installing ibm_db)

The above modules are in It can be found online, please download and install it yourself.

Building a database

After the database is installed, create a new instance. The default is DB2, and then create a new database. The MYTEST I created (in the operation database and link database Note the case), command line method:

Open the command line processor: (administrator identity)

Enter? Just press Enter and it will be displayed Command list, open the database manager:

Then just close it. It is more convenient to use db2 data studio to establish the database and create tables. Create a temporary in the root directory during installation directory, unzip the file, and then modify the properties of install.exe to be compatible with Windows 7 and open it with administrator rights. After installation, click on the left to create a new database.

Fill in the above method. The username and password should be the username and password set when installing the database.

After the instance is configured and tested successfully, you can create the database.

Just write down the database name and alias, leave out the rest because it is for testing, and wait for the formal environment to examine the configuration for performance optimization. Click Run to create. The process is a bit slow. I don’t know if it is due to the machine configuration. It took about ten minutes.

The process of creating a table will not be described in detail below. It is important to note that before creating a table, you must first create a schema and use a custom schema to create the table.

Connection

Connect the direct import library

Just import ibm_db_dbi.

import ibm_db_dbi

conn = ibm_db_dbi.connect(“PORT=50000;PROTOCOL=TCPIP;”, host=db[“host”], database=db[“database”], user=db[“user”],

password=db[“passwd”])

conn.set_autocommit(True)

cursor = conn.cursor()

Connect to the database and set up automatic submission

Query

sql = “select * from testable”

result = cursor.execute(sql)

Note that the above query method is wrong. The correct answer is as follows:

sql = “select * from MYSCHEMA.TESTTABLE” 


result = cursor.execute(sql) rows = cursor.fetchall()

The operation here is no different from MySQL

This place has been fooled for several hours , T_T

insert

sql = “insert into MYSCHEMA.TESTTABLE (“uuid”, “content”) values (‘%s', %s)” % (“1234567890”, “asdfghjkl”)

result = cursor.execute(sql)

update

sql = “update \”MYSCHEMA\”.\”TESTTABLE \” set \”content\” = ‘%s' where \”uuid\” = ‘%s'” % (

“aaa”, “1234567890”)

result = cursor.execute(sql)

If the operation is successful, the result is True. Pay attention to the quotation marks of each statement. The odd and even numbers must be in the above way.

The above is the entire content of using Python to connect to the DB2 database shared in this article. I hope it can be helpful to my friends.

For more articles related to Python connecting to DB2 database, please pay attention to the PHP Chinese website!