Edit Question - KnowledgeHub

Title Body

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: 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. 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 manuall

Tags (comma-separated) Cancel