Python DB-API：如何处理不同的参数？

I'm implementing a Python ontology class that uses a database backend to store and query the ontology. The database schema is fixed (specified in advance), but I don't know what type of database engine is being used. However, I can rely on the fact that the Python interface of the database engine uses the Python DB-API 2.0 (PEP 249). A straightforward idea is to let the user pass a PEP 249-compliant Connection object to the constructor of my ontology, which will then use various hardcoded SQL queries to query the database:

我正在实现一个Python本体类，它使用数据库后端来存储和查询本体。数据库模式是固定的（事先指定），但我不知道正在使用什么类型的数据库引擎。但是，我可以依赖数据库引擎的Python接口使用Python DB-API 2.0（PEP 249）这一事实。一个直截了当的想法是让用户将符合PEP 249的Connection对象传递给我的本体的构造函数，然后使用各种硬编码的SQL查询来查询数据库：

class Ontology(object):
    def __init__(self, connection):
        self.connection = connection

    def get_term(self, term_id):
        cursor = self.connection.cursor()
        query = "SELECT * FROM term WHERE id = %s"
        cursor.execute(query, (term_id, ))
        [...]

My problem is that different database backends are allowed to support different parameter markers in the queries, defined by the paramstyle attribute of the backend module. For instance, if paramstyle = 'qmark', the interface supports the question mark style (SELECT * FROM term WHERE id = ?); paramstyle = 'numeric' means the numeric, positional style (SELECT * FROM term WHERE id = :1); paramstyle = 'format' means the ANSI C format string style (SELECT * FROM term WHERE id = %s). If I want to make my class be able to handle different database backends, it seems that I have to prepare for all the parameter marker styles. This seems to defeat the whole purpose of a common DB API for me as I can't use the same parameterised query with different database backends.

我的问题是允许不同的数据库后端支持查询中的不同参数标记，由后端模块的paramstyle属性定义。例如，如果paramstyle ='qmark'，则界面支持问号样式（SELECT * FROM term WHERE id =？）; paramstyle ='numeric'表示数字，位置样式（SELECT * FROM term WHERE id =：1）; paramstyle ='format'表示ANSI C格式的字符串样式（SELECT * FROM term WHERE id =％s）。如果我想让我的类能够处理不同的数据库后端，似乎我必须为所有参数标记样式做准备。这似乎打败了我的通用DB API的整个目的，因为我不能使用相同的参数化查询与不同的数据库后端。

Is there a way around it, and if so, what is the best approach? The DB API does not specify the existence of a generic escaping function with which I can sanitize my values in the query, so doing the escaping manually is not an option. I don't want to add an extra dependency to the project either by using an even higher level of abstraction (SQLAlchemy, for instance).

有没有办法绕过它，如果有的话，最好的办法是什么？ DB API没有指定是否存在通用转义函数，我可以使用该函数清理查询中的值，因此不能手动执行转义。我不想通过使用更高级别的抽象（例如SQLAlchemy）为项目添加额外的依赖项。

3 个解决方案

#1

Strictly speaking, the problem is not caused by the DB API allowing this, but by the different databases which use different SQL syntaxes. The DB API module passes the exact query string to the database, along with the parameters. "Resolving" the parameter markers is done by the database itself, not by the DB API module.

严格地说，问题不是由DB API允许的，而是由使用不同SQL语法的不同数据库引起的。 DB API模块将确切的查询字符串与参数一起传递给数据库。 “解析”参数标记由数据库本身完成，而不是由DB API模块完成。

That means that if you want to solve this, you have to introduce some higher level of abstraction. If you do not want to add extra dependencies, you will have to do it yourself. But rather than manually escaping and substituting, you could try to dynamically replace parameter markers in the query string with the desired parameter markers, based on the paramstyle of the backend module. Then pass the string, WITH parameter markers to the db. For example, you could use '%s' everywhere, and use python string substitution to replace the '%s' with ':1', ':2' etc. if the db uses 'numeric' style, and so on....

这意味着如果你想解决这个问题，你必须引入一些更高级别的抽象。如果您不想添加额外的依赖项，则必须自己完成。但是，您可以尝试根据后端模块的参数样式，使用所需的参数标记动态替换查询字符串中的参数标记，而不是手动转义和替换。然后将带有参数标记的字符串传递给db。例如，您可以在任何地方使用'％s'，并使用python字符串替换将'％s'替换为'：1'，'：2'等等，如果db使用'numeric'样式，依此类推。 ..

#2

• This Python recipe might be able to help. It introduces an extra layer of abstraction to wrap parameters in its own Param class.

  这个Python配方可能会有所帮助。它引入了一个额外的抽象层来包装自己的Param类中的参数。

• The PyDal project may also be closer to what you're trying to achieve: "PyDal makes it possible to use the same paramstyle and datetime types with any module that conforms to DBAPI 2.0. In addition, paramstyles and datetime types are configurable."

  PyDal项目也可能更接近你想要实现的目标：“PyDal使得可以使用与任何符合DBAPI 2.0的模块相同的paramstyle和datetime类型。此外，paramstyles和datetime类型是可配置的。”

#3

I don't want to add an extra dependency to the project either by using an even higher level of abstraction (SQLAlchemy, for instance).

我不想通过使用更高级别的抽象（例如SQLAlchemy）为项目添加额外的依赖项。

That's too bad, because SQLAlchemy would be a perfect solution for this problem. In theory, DB-API 2.0 is built to offer this kind of flexibility. But that would require every driver developer (for Oracle, MySQLdb, Postgres, etc) to implement all the different paramstyles in their drivers. They don't. So you get stuck with the "preferred" paramstyle for each database engine.

这太糟糕了，因为SQLAlchemy将是解决这个问题的完美解决方案。理论上，DB-API 2.0旨在提供这种灵活性。但这需要每个驱动程序开发人员（对于Oracle，MySQLdb，Postgres等）在其驱动程序中实现所有不同的参数。他们没有。因此，您会遇到每个数据库引擎的“首选”参数样式。

If you refuse to use SQLAlchemy or any other higher abstraction layer or modern MVC class library, yes you have to write your own higher level of abstraction for this. I don't recommend that, despite that being your chosen solution here. You're facing some devilish details there, and will waste time figuring out bugs that others have already solved.

如果您拒绝使用SQLAlchemy或任何其他更高的抽象层或现代MVC类库，那么您必须为此编写自己的更高级别的抽象。我不建议这样做，尽管这是你选择的解决方案。你在那里面临着一些恶魔般的细节，并且会花时间搞清楚其他人已经解决的错误。

Don't view an external library dependency as a bad thing. If that's your approach to Python, you are going to be missing out on some of the most powerful features of the language.

不要将外部库依赖关系视为坏事。如果这是你的Python方法，那么你将会错过一些最强大的语言功能。

Pick your poison.

选择你的毒药。