问题描述
有没有办法将 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.
希望这一切都有帮助.