sqlalchemy连接dm8 get_columns BIGINT VARCHAR字段不显示
问题
标题即为问题，
问题出现原因
sqlalchemy对应的sqlalchemy_dm源码需要调整
版本说明
python 3.10
dmPython 2.5.5(2.4.8也可以)
sqlalchemy1.4.52
sqlalchemy_dm1.4.39
环境说明
部署环境 ubuntu20
开发环境window11 wsl2 ubuntu20
可能会出现的报错
AttributeError: module 'sqlalchemy.engine.result'has no attribute 'FullyBufferedResultProxy
找不到dmPython 模块
AttributeError: type object 'DMDialect_dmPython' has no attribute 'dbapi'
（按照我的经验，这些报错都只是dmPython dpi 和sqlalchemy 的兼容问题，尽量使用网络下载的方式安装dmPython）
离线安装方式请划到最后查阅
解决步骤
获取文件（sqlalchemy）
- window版本的dm8，安装一下（可能官网没有了，就联系公司的商务咨询一下，看能不能拿到）
- docker镜像
更改源码
我的需求一共是三个，识别BIGINT,VARCHAR,TINYINT这三个
所以需要调整的文件我列到下面，自行对比差异
sqlalchemy/sqlalchemy_dm/base.py（主要是get_columns需要更改）
@reflection.cache
def get_columns(self, connection, table_name, schema=None, **kw):
self.trace_process('DMDialect', 'get_columns', connection, table_name, schema, **kw)
"""
kw arguments can be:
dm_resolve_synonyms
dblink
"""
resolve_synonyms = kw.get('dm_resolve_synonyms', False)
dblink = kw.get('dblink', '')
info_cache = kw.get('info_cache')
(table_name, schema, dblink, synonym) = \
self._prepare_reflection_args(connection, table_name, schema,
resolve_synonyms, dblink,
info_cache=info_cache)
columns = []
if self._supports_char_length:
char_length_col = 'char_length'
else:
char_length_col = 'data_length'
params = {"table_name": table_name}
text = "SELECT column_name, data_type, %(char_length_col)s, "\
"data_precision, data_scale, "\
"nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s "\
"WHERE table_name = :table_name"
if schema is not None:
params['owner'] = schema
text += " AND owner = :owner "
text += " ORDER BY column_id"
text = text % {'dblink': dblink, 'char_length_col': char_length_col}
c = connection.execute(sql.text(text), **params)
for row in c:
(colname, orig_colname, coltype, length, precision, scale, nullable, default) = \
(self.normalize_name(row[0]), row[0], row[1], row[
2], row[3], row[4], row[5] == 'Y', row[6])
# 添加对 BIGINT 和 VARCHAR 类型的支持
if coltype == 'NUMBER':
coltype = _DMNumeric(precision, scale)
elif coltype in ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'VARCHAR'): # 添加 VARCHAR
coltype = self.ischema_names.get(coltype)(length)
elif coltype == 'BIGINT': # 添加 BIGINT
coltype = sqltypes.BIGINT
elif 'WITH TIME ZONE' in coltype:
coltype = TIMESTAMP(timezone=True)
else:
coltype = re.sub(r'\(\d+\)', '', coltype)
try:
coltype = self.ischema_names[coltype]
except KeyError:
util.warn("Did not recognize type '%s' of column '%s'" %
(coltype, colname))
coltype = sqltypes.NULLTYPE
cdict = {
'name': orig_colname,
'type': coltype,
'nullable': nullable,
'default': default,
'autoincrement': 'auto',
}
if orig_colname.lower() == orig_colname:
cdict['quote'] = True
columns.append(cdict)
return columnssqlalchemy/sqlalchemy_dm/types.py(部分需要修改)
ischema_names = {
'TINYINT': TINYINT,
'BIGINT': BIGINT,
'VARCHAR': VARCHAR,
'VARCHAR2': VARCHAR,
'NVARCHAR2': NVARCHAR,
'CHAR': CHAR,
'DATE': DATE,
'DATETIME': DATETIME,
'NUMBER': NUMBER,
'BLOB': _DMBLOB,
#'BLOB': _DMBinary,
'BFILE': BFILE,
'CLOB': CLOB,
'NCLOB': NCLOB,
'TIME WITH TIME ZONE':TIME,
'TIMESTAMP': TIMESTAMP,
'TIMESTAMP WITH TIME ZONE': TIMESTAMP,
'INTERVAL DAY TO SECOND': INTERVAL,
'FLOAT': FLOAT,
'DOUBLE PRECISION': DOUBLE_PRECISION,
'LONG': LONGVARCHAR,
'BIT': BIT,
'TEXT': _DMText,
#'TEXT': VARCHAR,
'INTEGER': _DMInteger,
'INT': _DMInteger,
'BINARY':DMBINARY
}安装驱动到python环境
获取到window的文件后，将dm8/drivers/python/sqlalchemy放到python环境中
随便放一个地址
然后执行以下命令
python setup.py install
记住执行前，记得把环境原有的sqlalchemy和sqlalchemy_dm删除
如果还有其他问题，见招拆招即可
dmPython离线安装
这一步骤，仅适用于，dmPython只能离线安装的伙伴
氛围两个步骤
制作dmPython rpm包
需要吧window中 drivers/python/dmPython放到docker中任意位置
因为离线方式暂时不是迫切的（我已经实现了）
而且部署环境也可以访问互联网，故先留下制作镜像和docker服务安装dmPython的随笔 如下
理解如下的命令
有个比较重要的事情，dpi的路径，是dm8 docker服务中的bin目录（整个copy过去就行）
# dm8 docker服务安装python3.10
apt-get install software-properties-common -y
add-apt-repository ppa:deadsnakes/ppa
apt-get install python3.10
ls /usr/bin/python3*
update-alternatives --install /usr/bin/python3 python3 /usr/bin/python3.8 1
update-alternatives --install /usr/bin/python3 python3 /usr/bin/python3.10 2
update-alternatives --config python3
python3 --version
#dm8 制作dmPython rpm包
cd /opt/dmdbms
ls
cd /opt/dmdbms/drivers/python/dmPython
apt-get update
apt-get install python3 -y
apt-get install python3.10-distutils -y
export DM_HOME=/opt/dmdbms
apt-get install build-essential -y
apt-get install python3.10-dev -y
apt-get install rpm -y
cp /opt/dmdbms/drivers/python/dpi/include/DPI.h /usr/include/python3.10
cp /opt/dmdbms/drivers/python/dpi/include/DPIext.h /usr/include/python3.10
cp /opt/dmdbms/drivers/python/dpi/include/DPItypes.h /usr/include/python3.10
python3 setup.py bdist_rpm
python3 setup.py install
apt-get install rpm -y
#开发服务安装dmPython
cd /usr/local/lib/python3.10/site-packages/dmPythonRpm
apt-get install alien -y
alien dmPython-2.4.8-8.1-py310-1.x86_64.rpm
dpkg -i dmpython_2.4.8-2_amd64.deb
pip uninstall dmPython -y
pip show dmPython
export PYTHONPATH=$PYTHONPATH:/usr/local/lib/python3.10/dist-packages
export LD_LIBRARY_PATH=/opt/dpi:$LD_LIBRARY_PATH
python3 -c "import dmPython; print(dmPython.__file__)"
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/dpi/
echo $LD_LIBRARY_PATH
find / -name libdmdpi.so
rpm -ivh dmPython-2.4.8-8.1-py310-1.x86_64.rpm --nodeps
cd /usr/local/lib/python3.10/site-packages/dmPythonRpm
rpm -ivh dmPython-2.4.8-8.1-py310-1.x86_64.rpm
apt-get autoremove --purge dmpython
alien dmPython-2.4.8-8.1-py310-1.x86_64.rpm
pip show dmPython
dpkg -i dmpython_2.4.8-2_amd64.deb
pip show dmPython
export PYTHONPATH=$PYTHONPATH:/usr/local/lib/python3.10/dist-packages
cp dmPython-2.4.8.egg-info /usr/local/lib/python3.10/site-packages/dmPython-2.4.8.egg-info
cp dmPython.cpython-310-x86_64-linux-gnu.so /usr/local/lib/python3.10/site-packages/dmPython.cpython-310-x86_64-linux-gnu.so
#参考资料
https://blog.csdn.net/qq_45458674/article/details/134399152