使用sqlalchemy-gbasedbt连接GBase 8s数据库
测试环境:
- 操作系统:CentOS 7.9 64-bit
数据库版本:GBase8sV8.8_AEE_3.0.0_1,对应的CSDK版本为3.0.0_1
1,确认安装python3
确认已经安装python3和python3-devel
[root@localhost test]# python3 -V Python 3.6.8
如果没有安装,建议使用yum install python3来安装。
升级pip的版本
[root@localhost test]# python3 -m pip install --upgrade --force-reinstall pip
WARNING: Running pip install with root privileges is generally not a good idea. Try `__main__.py install --user` instead.
Collecting pip
Downloading https://files.pythonhosted.org/packages/a4/6d/6463d49a933f547439d6b5b98b46af8742cc03ae83543e4d7688c2420f8b/pip-21.3.1-py3-none-any.whl (1.7MB)
100% |████████████████████████████████| 1.7MB 235kB/s
Installing collected packages: pip
Successfully installed pip-21.3.1
[root@localhost test]# pip3 list
WARNING: pip is being invoked by an old script wrapper. This will fail in a future version of pip.
Please see https://github.com/pypa/pip/issues/5599 for advice on fixing the underlying issue.
To avoid this problem you can invoke Python with '-m pip' instead of running pip directly.
Package Version
---------- -------
pip 21.3.1
setuptools 39.2.0
2,安装GBase 8s数据库连接工具(CSDK)
可以直接下载免安装版本的CSDK驱动:
链接:https://pan.baidu.com/s/1s9EW3VoRznlj6uDHubIEtg?pwd=ejfb
提取码:ejfb
解压到指定目录/opt下,生成/opt/gbase8s-odbc-driver目录
[root@localhost test]# ll
总用量 35188
-rw-r--r--. 1 root root 36029237 3月 11 20:52 GBase8s_3.0.0_1-Linux64-ODBC-Driver.tar.gz
[root@localhost test]# tar -zxf GBase8s_3.0.0_1-Linux64-ODBC-Driver.tar.gz -C /opt/
[root@localhost test]# cd /opt/
[root@localhost opt]# ll
总用量 4
drwxr-xr-x. 20 gbasedbt gbasedbt 4096 3月 10 15:14 gbase
drwxrwxr-x. 9 1001 1003 88 12月 13 2020 gbase8s-odbc-driver
drwxr-xr-x. 2 root root 6 10月 31 2018 rh
创建必须的环境变量,并使环境生效
export GBASEDBTDIR=/opt/gbase8s-odbc-driver
export CSDK_HOME=/opt/gbase8s-odbc-driver
export PATH=$GBASEDBTDIR/bin:$PATH
export LD_LIBRARY_PATH=$GBASEDBTDIR/lib:$GBASEDBTDIR/lib/cli:$GBASEDBTDIR/lib/esql:$LD_LIBRARY_PATH
unset ODBCINI
ODBCINI环境变量需要去除。
创建sqlhosts配置文件
[root@localhost test]# vi /opt/gbase8s-odbc-driver/etc/sqlhosts
[root@localhost test]# more /opt/gbase8s-odbc-driver/etc/sqlhosts
gbase01 onsoctcp a02.gbasedbt.com 9088
3,安装sqlalchemy-gbasedbt
3.1, 在线安装sqlalchemy-gbasedbt
确认python3、python3-devel和gcc均已经安装,CSDK也已经安装以及环境变量已经配置的情况下,可直连网络的情况下,可使用pip3 install sqlalchemy-gbasedbt直接安装
[root@localhost test]# pip3 install sqlalchemy-gbasedbt
Collecting sqlalchemy-gbasedbt
Using cached sqlalchemy_gbasedbt-0.2.4-py3-none-any.whl (10 kB)
Collecting DbtPy
Using cached DbtPy-3.0.5.4.tar.gz (162 kB)
Preparing metadata (setup.py) ... done
Requirement already satisfied: sqlalchemy in /usr/local/lib64/python3.6/site-packages (from sqlalchemy-gbasedbt) (1.4.46)
Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib64/python3.6/site-packages (from sqlalchemy->sqlalchemy-gbasedbt) (2.0.2)
Requirement already satisfied: importlib-metadata in /usr/local/lib/python3.6/site-packages (from sqlalchemy->sqlalchemy-gbasedbt) (4.8.3)
Requirement already satisfied: typing-extensions>=3.6.4 in /usr/local/lib/python3.6/site-packages (from importlib-metadata->sqlalchemy->sqlalchemy-gbasedbt) (4.1.1)
Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.6/site-packages (from importlib-metadata->sqlalchemy->sqlalchemy-gbasedbt) (3.6.0)
Using legacy 'setup.py install' for DbtPy, since package 'wheel' is not installed.
Installing collected packages: DbtPy, sqlalchemy-gbasedbt
Running setup.py install for DbtPy ... done
Successfully installed DbtPy-3.0.5.4 sqlalchemy-gbasedbt-0.2.4
将同时安装依赖包:sqlalchemy、greenlet、importlib-metadata、typing-extensions、zipp和DbtPy,安装后的pip3列表如下:
[root@localhost test]# pip3 list
Package Version
------------------- -------
DbtPy 3.0.5.4
greenlet 2.0.2
importlib-metadata 4.8.3
pip 21.3.1
setuptools 39.2.0
SQLAlchemy 1.4.46
sqlalchemy-gbasedbt 0.2.4
typing_extensions 4.1.1
zipp 3.6.0
4,编写测试Demo,执行测试
测试demo文件
#!/usr/bin/env python3
# Filename: testSqlalchemy_gbasedbt
from sqlalchemy import MetaData, Table, Column, String, create_engine
from sqlalchemy.dialects import registry
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
registry.register("gbasedbt", "sqlalchemy_gbasedbt.dbtdb", "GBasedbtDialect")
# 创建对象的基类:
Base = declarative_base()
# 定义User对象:
class User(Base):
# 表的名字:
__tablename__ = 'user'
# 表的结构:
id = Column(String(20), primary_key=True)
name = Column(String(20))
# 初始化数据库连接:
# ConStr = 'gbasedbt://<username>:<password>@<host name>:<port number>/<databasename>;SERVER=<server name>'
ConStr = 'gbasedbt://gbasedbt:GBase123@a02.gbasedbt.com:9088/testdb;SERVER=gbase01;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;DELIMIDENT=y'
engine = create_engine(ConStr)
# 创建对象
Base.metadata.create_all(engine)
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
# 创建session对象:
session = DBSession()
# 创建新User对象:
new_user = User(id='2', name='测试用户')
# 添加到session:
session.add(new_user)
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.close()
# 创建Session:
session = DBSession()
# 创建Query查询,filter是where条件,最后调用one()返回唯一行,如果调用all()则返回所有行:
user = session.query(User).filter(User.id=='2').one()
# 打印类型和对象的name属性:
print('type:', type(user))
print('name:', user.name)
# 关闭Session:
session.close()
测试结果:
[root@localhost test]# ./testSqlalchemy_gbasedbt.py
type: <class '__main__.User'>
name: 测试用户
- 上一篇: GBase 8s数据库配置使用IPv6
- 下一篇: GBase 8s内置dbms_lob函数说明
from sqlalchemy import create_engine
from contextlib import contextmanager
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.dialects import registry
from config.finalparam import FinalParamfrom utils.Loging import LOGGINGfrom utils.common import get_yaml
registry.register("gbasedbt", "sqlalchemy_gbasedbt.dbtdb", "GBasedbtDialect")
# DATABASE_CONFIG_YML = "../config/gw_database_config.yaml"
DATABASE_CONFIG_YML = "../config/database_config.yaml"
DATABASE_CONFIG_YML = FinalParam.DATABASE_CONFIG_YML
cfg = get_yaml(DATABASE_CONFIG_YML)
# print(type(cfg))
host = cfg['host']
port = cfg['port']
user = cfg['user']
password = cfg['password']
database = cfg['database']
Base = declarative_base()
engine = create_engine(
"gbasedbt://%s:%s@%s:%s/%s" % (user, password, host, port, database)+";SERVER=gbase01;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;DELIMIDENT=y",
# encoding='utf-8',
# echo=True, # SQL 语句及其参数到控制台
pool_pre_ping=True, # 检测断开连接
pool_recycle=1800, # 每30分钟重新创建连接,避免超时
pool_size=3, # 连接池中最多可以保持的连接数
max_overflow=4, # 允许在需要时,连接池外再创建最多 4 个额外的连接,超出连接池大小的部分会在使用后关闭
# pool_recycle=7200, # 表示连接在 7200 秒(2 小时)后会被回收,避免因长时间连接而导致的问题(例如,MySQL 的“互动超时”)
# pool_timeout=30 # 连接池的超时时间,表示如果在 30 秒内没有获取到可用的连接,将会抛出异常
)Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
报错信息
File "/work/audioTrace_screenCorrect0605-kylin/dao/screen_correct_mapper.py", line 2, in
from dao.databaseConnector import Connector
File "/work/audioTrace_screenCorrect0605-kylin/dao/databaseConnector.py", line 23, in
engine = create_engine(
^^^^^^^^^^^^^^
File "", line 2, in create_engine
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned
return fn(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 518, in create_engine
entrypoint = u._get_entrypoint()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/url.py", line 662, in _get_entrypoint
cls = registry.load(name)
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 330, in load
return self.impls[name]()
^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 349, in load
mod = compat.import_(modulepath)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy_gbasedbt/dbtdb.py", line 7, in
import DbtPyDbi as Database
File "/usr/local/lib/python3.12/site-packages/DbtPyDbi.py", line 44, in
import DbtPy
ImportError: /usr/local/lib/python3.12/site-packages/DbtPy.cpython-312-x86_64-linux-gnu.so: undefined symbol: PyUnicode_FromUnicode
如果你的数据库是363,可以使用gbase8s-sqlalchemy(依赖gbase8sdb,sqlalchemy~=2.0.0).
ConStr = 'gbasedbt://gbasedbt:123456@localhost:9088/testdb;SERVER=gbase;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;'报错信息
lib/python3.10/site-packages/DbtPyDbi.py", line 498, in connect
conn = DbtPy.connect(ConStr, '', '')
Exception: [GBasedbt][GBasedbt ODBC Driver]Invalid connection string attribute. SQLCODE=-11005