dmSQLAlchemy 方言包

5.1 简介及安装

SQLAlchemy 是 python 下的开源软件，提供了 SQL 工具包及对象关系映射（ORM）工具，让应用程序开发人员使用上 SQL 的强大功能和灵活性。dmSQLAlchemy 方言包是 DM 提供用于 SQLAlchemy 连接 DM 数据库的方法。

1.SQLAlchemy 软件的安装。例如：SQLAlchemy-1.1.10.win-amd64-py2.7.exe。

2.dmSQLAlchemy 方言包的软件生成与安装。

dmSQLAlchemy 可以运行在任何安装了 Python 的平台上。生成工具 setup.py 位于 drivers\python\dmSQLAlchemy\dmSQLAlchemyx.x 目录中。

可以使用如下命令很方便地在 Windows 和 Linux 操作系统下编译并安装 dmSQLAlchemy：

//进入到setup.py所在的源码目录，执行以下命令：

python setup.py install

也可以先生成安装文件再进行安装，不同平台生成安装包的命令如下：

Windows：python setup.py bdist_wininst

Linux：python setup.py bdist_rpm   

生成之后的安装包（例如 dmSQLAlchemy-1.1.10.win-amd64.exe）位于 drivers\python\dmSQLAlchemy\dmSQLAlchemyx.x\dist 目录中。点击安装包安装即可。

DM 提供的 dmSQLAlchemy 与 SQLAlchemy 的版本对应关系如下表：

表5.1 dmSQLalchemy版本对应表

dmSQLAlchemy 版本	SQLAlchemy 版本
1.1.x	1.3.x
1.4.x	1.4.x
2.0.x	2.0.x

5.2 engine 的配置

create_engine()返回一个数据库引擎，下面是 DM 数据库的配置方法。

from sqlalchemy import create_engine

engine =
create_engine('dm://SYSDBA:Dmsys_123@localhost:5236/',connect_args={'local_code':1,'connection_timeout':15})

	或

engine =
create_engine('dm+dmPython://SYSDBA:Dmsys_123@localhost:5236/',connect_args={'local_code':1,'connection_timeout':15})

其中，connect_args 是字典选项，只要在 connect_args 中以字典对象的方式配置 dmPython.connect 支持的选项即可。可以包含多个字典对象，用逗号分隔。dmPython.connect 请参考 3.1.1.1 dmPython.connect。其他配置参考 SQLAlchemy 官网 http://docs.sqlalchemy.org 文档。

5.3 类型映射

SQLAlchemy 支持了数据库中使用的大部分类型，根据映射关系，dmSQLAlchemy 方言包支持达梦数据库的下列类型：

ARRAY，BFILE，BIGINT，BINARY，BIT，BLOB，CHAR，CHARACTER，CLOB，DATE，DATETIME，DEC，DECIMAL，DOUBLE，DOUBLE PRECISION，FLOAT，JSON，JSONB，INT，INTEGER，INTERVAL YEAR，INTERVAL MONTH，INTERVAL DAY，INTERVAL HOUR，INTERVAL MINUTE，INTERVAL SECOND，INTERVAL YEAR TO MONTH，INTERVAL DAY TO HOUR，INTERVAL DAY TO MINUTE，INTERVAL DAY TO SECOND，INTERVAL HOUR TO MINUTE，INTERVAL HOUR TO SECOND，INTERVAL MINUTE TO SECOND，IMAGE，ROWID，LONG，NCLOB，NUMBER，NUMERIC，NVARCHAR2，REAL，SMALLINT，TEXT，TIME，TIME WITH TIME ZONE，TIMESTAMP，TIMESTAMP WITH LOCAL TIME ZONE，TIMESTAMP WITH TIME ZONE，TINYINT，VARBINARY，VARCHAR，VARCHAR2。

其中由于 SQLAlchemy 类型支持问题，DECIMAL，DEC，NUMBER 与 NUMERIC 类型在 SQLAlchemy 中均被映射为 NUMBER 类型，时间间隔数据类型（INTERVAL YEAR 等）均被映射为 INTERVAL 类型，CHARACTER 类型被映射为 CHAR 类型，TINYINT 类型被映射为 SMALLINT 类型，CLOB 类型与 LONGVARCHAR 类型被映射为 TEXT 类型 TIME WITH TIME ZONE 类型被映射为 TIME 类型。

5.4 异步功能

5.4.1 简介

dmSQLAlchemy 在 2.0.7 版本支持了 SQLAlchemy 的异步功能，由于功能要求限制，当前仅支持在 SQLAlchemy 版本大于 2.0.22 的版本上使用异步功能，同时需要 python 中安装了 dmAsync 包。

5.4.2 使用

可以通过创建异步 connection 或者创建异步 session 两种方法来使用 dmSQLAlchemy。

5.4.2.1 通过创建异步 connection 使用 dmSQLAlchemy

与正常使用 SQLAlchemy 的异步连接数据库方法相同，可以通过在创建 engine 时使用 create_async_engine 方法创建异步 engine，通过异步 engine 的 begin 接口创建连接，通过连接执行语句：

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import declarative_base, sessionmaker
from sqlalchemy import Column, Integer, MetaData, text
import asyncio

engine = create_async_engine("dm+dmAsync://SYSDBA:sysDBA*00@localhost:5236/", echo=True)
AsyncSessionLocal = sessionmaker(autoflush=False, bind=engine, class_=AsyncSession)

async def async_func():
async with AsyncSessionLocal() as session:
  query = text('SELECT * FROM DUAL;')
  result = await session.execute(query)
  rows = result.fetchall()
  for row in rows:
  print(row)

   await session.commit()
   await engine.dispose()

asyncio.run(async_func())

5.4.2.2 通过创建异步 session 使用 dmSQLAlchemy

除了通过 connection 去执行语句，更常用的方法是通过创建异步 session 去使用 SQLAlclehmy。

在创建 engine 时使用 create_async_engine 方法可以创建异步 engine，根据异步 engine，使用 async_sessionmaker 方法创建 session，从而进行对数据库的异步操作

import asyncio
import datetime
from typing import List
from sqlalchemy import ForeignKey
from sqlalchemy import func
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncAttrs
from sqlalchemy.ext.asyncio import async_sessionmaker
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy.orm import selectinload
 
class Base(AsyncAttrs, DeclarativeBase):
  pass
class B(Base):
 __tablename__ = "b"
   id: Mapped[int] = mapped_column(primary_key=True)
   a_id: Mapped[int] = mapped_column(ForeignKey("a.id"))
   data: Mapped[str]
   
class A(Base):
  __tablename__ = "a"
  id: Mapped[int] = mapped_column(primary_key=True)
  data: Mapped[str]
  create_date: Mapped[datetime.datetime] = mapped_column(server_default=func.now())
  bs: Mapped[List[B]] = relationship()

async def insert_objects(async_session: async_sessionmaker[AsyncSession]) -> None:
async with async_session() as session:
 async with session.begin():
 session.add_all(
 [  A(bs=[B(data="b1", id=1), B(data="b2", id=2)], data="a1",id=1),
    A(bs=[], data="a2",id=2),
    A(bs=[B(data="b3", id=3), B(data="b4", id=4)], data="a3",id=3),
     ]
  )
 async def select_and_update_objects(
 async_session: async_sessionmaker[AsyncSession],
) -> None:
 async with async_session() as session:
 stmt = select(A).order_by(A.id).options(selectinload(A.bs))
 result = await session.execute(stmt)
 for a in result.scalars():
 print(a, a.data)
 print(f"created at: {a.create_date}")
 for b in a.bs:
 print(b, b.data)
 result = await session.execute(select(A).order_by(A.id).limit(1))
 a1 = result.scalars().one()
 a1.data = "new data"
 await session.commit()
 print(a1.data)
 for b1 in await a1.awaitable_attrs.bs:
 print(b1, b1.data)
 
async def async_main() -> None:
  engine = create_async_engine("dm+dmAsync://SYSDBA:sysDBA*00@localhost:5236", echo=True)
  async_session = async_sessionmaker(engine, expire_on_commit=False)
  async with engine.begin() as conn:
  await conn.run_sync(Base.metadata.create_all)
  await insert_objects(async_session)
  await select_and_update_objects(async_session)
  await engine.dispose()
  
asyncio.run(async_main()

5.5 特殊说明

1、dmSQLalchemy 当前版本，主键 integer primary_key 不会再自动形成自增列了；

2、只有当 dmPython 版本大于 2.5.9 时才可以正常使用 executemany 返回多行数据集；

3、由于表名标准化问题，克隆或迁移表时，全大写表名将无法识别，请避免使用此类表名；

4、由于当 python 中的 True 或 False 作为 JSON 类型的值时，选取时使用的 json_value 函数在匹配时如果期望匹配 True 或 False，需要使用字符串’true’或’false’匹配。

5、由于 SQLAlchemy 对于类型返回值的检测问题，导致 JSON 类型无法返回为正常的 dict，因此暂时将从数据库中查询到的 JSON 或者 JSONB 类型列的值映射为字符串类型返回。

5.6 暂不支持功能

1、由于数据库语法暂不支持 LATERAL 功能，因此在 dmSQLAlchemy 中当前暂不支持使用 SQLAlchemy 提供的 lateral 方法构建子查询语句。

2、由于语法限制暂不支持使用 bulk_insert_mappings，supports_default_values，supports_empty_insert 等空值插入方法。