使用SQLAlchemy创建表时遇TypeError：函数最多接受3个参数（给定4个）

我正在开发一个从Instagram到Telegram的机器人，该机器人会监控指定Instagram用户账号，当有新帖子发布时，我会在Telegram收到通知。目前我正在使用SQLAlchemy为项目创建数据库表，但执行建表操作时遇到了错误，错误信息为：TypeError: function takes at most 3 arguments (4 given)。

环境信息 #

• SQLite3 版本：3.37.2
• Python 版本：3.11.1

表定义代码 #

from sqlalchemy import Column, Integer, DateTime, Text, Boolean
from config import Base, engine
import uuid


class InstagramImageNoRss(Base):
    __tablename__ = 'instagram_image_norss'
    __table_args__ = {'sqlite_autoincrement': True}
    id = Column(Text, primary_key=True, default=lambda: uuid.uuid4().hex)
    local_path = Column(Text)
    local_path_txt = Column(Text)
    local_path_json = Column(Text)
    comments_path = Column(Text)
    geolocation_path = Column(Text)
    text_data = Column(Text)
    json_data = Column(Text)
    comments_data = Column(Text)
    geolocation_data = Column(Text)
    username = Column(Text)
    sended = Column(Boolean)
    published = Column(DateTime)
    publication_index = Column(Integer)

    def __str__(self):
        import json
        return json.dumps(self.__dict__, default=str)


class InstgaramImageRss(Base):
    __tablename__ = 'instagram_image_rss'
    __table_args__ = {'sqlite_autoincrement': True}
    id = Column(Text, primary_key=True, default=lambda: uuid.uuid4().hex)
    published = Column(DateTime)
    local_name = Column(Text)
    local_path = Column(Text)
    rss_webstagram_id = Column(Text)
    summary = Column(Text)
    media_url = Column(Text)
    image_hash = Column(Text)
    creation_time = Column(DateTime)
    link = Column(Text)
    sended = Column(Boolean)
    username = Column(Text)

    def __str__(self):
        import json
        return json.dumps(self.__dict__, default=str)


class InstagramSubscription(Base):
    __tablename__ = 'instagram_subscription'
    __table_args__ = {'sqlite_autoincrement': True}
    id = Column(Text, primary_key=True, default=lambda: uuid.uuid4().hex)
    username = Column(Text, primary_key=True, unique=True)
    last_check_datetime = Column(DateTime)
    subscribed = Column(Boolean)

    def __str__(self):
        import json
        return json.dumps(self.__dict__, default=str, indent=4)


class Chat(Base):
    __tablename__ = 'chats'
    __table_args__ = {'sqlite_autoincrement': True}
    id = Column(Text, primary_key=True, default=lambda: uuid.uuid4().hex)
    chat_id = Column(Integer)
    admin = Column(Boolean)
    tg_ans = Column(Text)

    def __str__(self):
        import json
        return json.dumps(self.__dict__, default=str, indent=4)

# Error happens here
Base.metadata.create_all(engine)

完整错误栈 #

File "/workspace/InstagramTelegramBot/InstagramBotDAO.py", line 76, in <module>
    Base.metadata.create_all(engine)
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 5828, in create_all
    bind._run_ddl_visitor(
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3242, in _run_ddl_visitor
    with self.begin() as conn:
  File "/home/ribeiroo/.pyenv/versions/3.11.1/lib/python3.11/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3232, in begin
    with self.connect() as conn:
         ^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3268, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3292, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 421, in connect
    return _ConnectionFairy._checkout(self, self._fairy)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1269, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 403, in _do_get
    c = self._create_connection()
        ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 916, in __connect
    )._exec_w_sync_on_first_run(self.dbapi_connection, self)
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/event/attr.py", line 473, in _exec_w_sync_on_first_run
    self(*args, **kw)
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
    fn(*args, **kw)
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 704, in on_connect
    do_on_connect(dbapi_connection)
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/sqlite/pysqlcipher.py", line 145, in on_connect
    super_on_connect(conn)
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/sqlite/pysqlite.py", line 574, in connect
    fn(conn)
  File "/workspace/InstagramTelegramBot/.venv/lib/python3.11/site-packages/sqlalchemy/dialects/sqlite/pysqlite.py", line 557, in set_regexp
    dbapi_connection.create_function(
TypeError: function takes at most 3 arguments (4 given)

这个错误是SQLAlchemy版本与SQLite版本不兼容导致的：SQLAlchemy在set_regexp方法中调用create_function时传入了4个参数，但你的SQLite 3.37.2版本的create_function仅支持最多3个参数（函数名、参数数量、函数对象），而SQLAlchemy传入的第四个参数deterministic=True是SQLite 3.38.0及以上版本才支持的特性。

有三种解决方式：

1. 升级SQLite版本到3.38.0及以上：更新系统或项目依赖的SQLite，使其支持deterministic参数，即可兼容SQLAlchemy的调用逻辑。
2. 降级SQLAlchemy版本：选择与SQLite 3.37.2兼容的SQLAlchemy版本，比如1.4.46及以下版本，执行以下命令安装：

pip install sqlalchemy==1.4.46

3. 自定义SQLite连接的regexp函数注册：在创建引擎时，替换默认的regexp注册逻辑，不传入deterministic参数。示例代码：

from sqlalchemy import create_engine
import sqlite3

def custom_set_regexp(conn):
    def regexp(expr, item):
        import re
        return re.search(expr, item) is not None
    conn.create_function('regexp', 2, regexp)

# 替换成你的数据库路径
engine = create_engine('sqlite:///your_database.db', connect_args={"check_same_thread": False})
from sqlalchemy import event
event.listen(engine, 'connect', custom_set_regexp)

将原有代码中的engine替换为这个自定义配置的引擎即可。

内容的提问来源于stack exchange，提问作者Rafael Ribeiro