如何将 Python 连接到 Db2

2022-01-14 00:00:00 python db2

问题描述

有没有办法将 Python 连接到 Db2?

Is there a way to connect Python to Db2?

解决方案

文档很难找到，一旦找到，就非常糟糕.以下是我在过去 3 小时内发现的内容.

The documentation is difficult to find, and once you find it, it's pretty abysmal. Here's what I've found over the past 3 hours.

需要使用pip安装ibm_db，如下:

pip install ibm_db

您需要创建一个连接对象.文档在这里.

You'll want to create a connection object. The documentation is here.

这是我写的:

from ibm_db import connect
# Careful with the punctuation here - we have 3 arguments.
# The first is a big string with semicolons in it.
# (Strings separated by only whitespace, newlines included,
#  are automatically joined together, in case you didn't know.)
# The last two are emptry strings.
connection = connect('DATABASE=<database name>;'
                     'HOSTNAME=<database ip>;'  # 127.0.0.1 or localhost works if it's local
                     'PORT=<database port>;'
                     'PROTOCOL=TCPIP;'
                     'UID=<database username>;'
                     'PWD=<username password>;', '', '')

接下来，您应该知道 ibm_db 的命令实际上永远不会给您结果.相反，您需要重复调​​用命令上的 fetch 方法之一来获取结果.我写了这个辅助函数来处理这个问题.

Next you should know that commands to ibm_db never actually give you results. Instead, you need to call one of the fetch methods on the command, repeatedly, to get the results. I wrote this helper function to deal with that.

def results(command):
    from ibm_db import fetch_assoc

    ret = []
    result = fetch_assoc(command)
    while result:
        # This builds a list in memory. Theoretically, if there's a lot of rows,
        # we could run out of memory. In practice, I've never had that happen.
        # If it's ever a problem, you could use
        #     yield result
        # Then this function would become a generator. You lose the ability to access
        # results by index or slice them or whatever, but you retain
        # the ability to iterate on them.
        ret.append(result)
        result = fetch_assoc(command)
    return ret  # Ditch this line if you choose to use a generator.

现在定义了该辅助函数，您可以轻松地执行以下操作，例如获取数据库中所有表的信息:

Now with that helper function defined, you can easily do something like get the information on all the tables in your database with the following:

from ibm_db import tables

t = results(tables(connection))

如果您想查看给定表格中的所有内容，您现在可以执行以下操作:

If you'd like to see everything in a given table, you could do something like this now:

from ibm_db import exec_immediate

sql = 'LIST * FROM ' + t[170]['TABLE_NAME']  # Using our list of tables t from before...
rows = results(exec_immediate(connection, sql))

现在 rows 包含数据库中第 170 个表中的行 list，其中每一行都包含列名的 dict:价值.

And now rows contains a list of rows from the 170th table in your database, where every row contains a dict of column name: value.

希望这一切都有帮助.

相关文章