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）

1. window版本的dm8，安装一下（可能官网没有了，就联系公司的商务咨询一下，看能不能拿到）
2. docker镜像

更改源码

我的需求一共是三个，识别BIGINT,VARCHAR,TINYINT这三个

所以需要调整的文件我列到下面，自行对比差异

sqlalchemy/sqlalchemy_dm/base.py（主要是get_columns需要更改）

 @reflection.cachedef 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_synonymsdblink"""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'] = schematext += " 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'):  # 添加 VARCHARcoltype = self.ischema_names.get(coltype)(length)elif coltype == 'BIGINT':  # 添加 BIGINTcoltype = sqltypes.BIGINTelif '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.NULLTYPEcdict = {'name': orig_colname,'type': coltype,'nullable': nullable,'default': default,'autoincrement': 'auto',}if orig_colname.lower() == orig_colname:cdict['quote'] = Truecolumns.append(cdict)return columns

sqlalchemy/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.10ls /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 -ycp /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.10python3 setup.py bdist_rpmpython3 setup.py installapt-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.sorpm -ivh dmPython-2.4.8-8.1-py310-1.x86_64.rpm  --nodepscd /usr/local/lib/python3.10/site-packages/dmPythonRpm
rpm -ivh dmPython-2.4.8-8.1-py310-1.x86_64.rpmapt-get autoremove --purge dmpythonalien 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-packagescp 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