使用Python DB-API连接InterSystems IRIS

文章

Jingwei Wang · 八月 30, 2022 阅读大约需 3 分钟

InterSystems IRIS 允许从任何符合DB-API的Python应用程序对InterSystems IRIS 进行快速、无缝地访问。Python DB-API驱动是对PEP 249 v2.0（Python数据库API规范 v2.0）的完整兼容。

步骤

前提是要有一个Python的开发环境。本示例使用vs code 如下所示创建一个dbapi.py文件。 dbapi.py :

# Embedded Python examples from summer 2022
import iris as dbapi

mytable = "mypydbapi.test_things"
conn = dbapi.connect(hostname='localhost', port=1972, namespace='IRISAPP', username='superuser', password='iris')

# Create table
cursor = conn.cursor()
try:
 cursor.execute(f"CREATE TABLE {mytable} (myvarchar VARCHAR(255), myint INTEGER, myfloat FLOAT)")
except Exception as inst:
 pass
cursor.close()
conn.commit()

# Create some data to fill in
chunks = []
paramSequence = []
for row in range(10):
 paramSequence.append(["This is a non-selective string every row is the same data", row%10, row * 4.57292])
 if (row>0 and ((row % 10) == 0)):
   chunks.append(paramSequence)
   paramSequence = []
chunks.append(paramSequence)

query = f"INSERT INTO {mytable} (myvarchar, myint, myfloat) VALUES (?, ?, ?)"

for chunk in chunks:
 cursor = conn.cursor()
 cursor.executemany(query, chunk)
 cursor.close()
 conn.commit()
# conn.close()

sql = f"select * from {mytable}"
rowsRead = 0
cursor = conn.cursor()
cursor.arraysize = 20

cursor.execute(sql)
rc = cursor.rowcount
rows = cursor.fetchall()
for row in rows:
 print(row)
rowsRead += len(rows)

cursor.close()
conn.close()

安装 DB-API驱动，点击此链接下载DB-API驱动

pip install intersystems_irispython-version-py3-none-any.whl

配置Connection String - 按照Intersystems IRIS的服务器，在dbapi.py文件中配置
- hostname
- port
- namespace
- username
- password

在InterSystems IRIS管理门户中创建IRISAPP命名空间。

在VS code中运行dbapi.py文件，运行结果如下，说明数据成功导入。

['This is a non-selective string every row is the same data', 0, 0.0]
['This is a non-selective string every row is the same data', 1, 4.57292]
['This is a non-selective string every row is the same data', 2, 9.14584]
['This is a non-selective string every row is the same data', 3, 13.71876]
['This is a non-selective string every row is the same data', 4, 18.29168]
['This is a non-selective string every row is the same data', 5, 22.8646]
['This is a non-selective string every row is the same data', 6, 27.43752]
['This is a non-selective string every row is the same data', 7, 32.01044]
['This is a non-selective string every row is the same data', 8, 36.58336]
['This is a non-selective string every row is the same data', 9, 41.156279999999995]

在IRISAPP命名空间下，查看InterSystems IRIS 数据库，可以看到数据，说明数据导入成功InterSystems IRIS。
```
SELECT myvarchar, myint, myfloat FROM mypydbapi.test_things
```