Psycopg2: as_string() function for psycopg2.sql module requires a "context" (connection or cursor).

[zlex7]

The as_string(conn) function from the psycopg2.sql module is used to convert a sql.SQL object to its underlying string form. This is causing me a few difficulties when trying to unit test some functions that use the sql.SQL module, as I do not want to spin up a database just to unit test. Yes, one solution could be to try to mock the connection or just test against the SQL objects before converting them to a string. However, is there any particular reason that the as_string() function necessitates a context? Could it not be treated as completely independent from any particular database?

[fogzot]

Unfortunately, no. The string should have the correct encoding and to know what encoding the backend expects, we need a connection.

We _could_ use a default encoding, probably UNICODE, if the connection is null or add an encoding argument to as_string(). It is a bit of work and should be done in a backward compatible way. Patches are welcome.

[dvarrazzo]

Not only certain results depend on the encoding. Several functions are just wrapper to the underlying libpq library, which takes a libpq connection object as argument: psycopg offers to wrap them and there is no plan for us to re-implement them in pure Python. So no, this won't happen.

[zlex7]

Ok, thank you for explaining. I am looking at the test_sql.py tests for the sql module, and it looks like it uses an actual connection to compare the string forms. However, this doesn't really seem like something I would want to do in order to run lots of fast, low overhead unit tests. Do you have any suggestions about how to workaround this or mock a database connection so I don't need to run a PostgreSQL instance simply to test a query builder? Thanks.

[fogzot]

Mocking thr connection is a bad idea. What I'd do is to abstract the db access in a XyzStore class (or classes) and then mock it to test the rest of my code.

Testing the sql is an integration test and should be done on a real db with the exact version used in ptoduction. PostgreSQL is known for introducing forward incompatible changes, especially when automatic type casting of parameters is involved.

[zlex7]

I think you might misunderstand my situation, most likely because I haven't explained it well. So, I do already have an XyzStore class (I just call it PostgreSQL) to abstract the connection. What I am attempting to test is a JSON-to-SQL parser that I have written, that uses psycopg2.sql to actually build the SQL query (for escaping/security purposes). I simply want to test if the parser is working correctly by feeding it specific JSON examples and confirming that it is parsing the JSON and converting to SQL correctly. However, it is very hard (and ugly) to compare the resulting SQL I have generated without being able to actually convert it to a string at the end (as I would have to directly compare against Composed, SQL, Identifier, etc. objects). Lastly, I do not want to start a database simply to test whether the SQL query I have dynamically built looks like how it should. I don't believe the encoding matters in my case, only that the formatting of the queries is consistent across tests. So basically all I want to do is have some way of getting the underlying string representation of my SQL query without making a database connection. I'm not sure that mocking is even possible, seeing that the code checks whether the given context is a Cursor or Connection object. And I am planning on using integration tests to actually confirm the SQL queries work, but I really do not see why I would want to use integration tests to test something that seems like it should not require any kind of external dependency (the JSON-to-SQL parser). Do you have any ideas about what I could do? Thanks for your help!

[zlex7]

I could be completely wrong here, but it appears that the only references to the given context inside of the exposed as_string() functions are

if hasattr(a, 'prepare'):
        a.prepare(conn)

and

if sys.version_info[0] >= 3 and isinstance(rv, bytes):
       rv = rv.decode(ext.encodings[conn.encoding])

and

def as_string(self, context):
      return ext.quote_ident(self._wrapped, context)

In the first and second case it appears that it is not absolutely necessary to supply a connection (can not have 'prepare' attribute for first case and provide just an encoding for the second). However, after looking deeper, the third case seems to be what is making this function require a connection:

The underlying function called for quote_ident() is actually psyco_quote_ident(), which then uses the (connectionObject* conn) struct only twice.

These two calls are to

```PyObject *
conn_text_from_chars(connectionObject *self, const char *str)
{
return psycopg_text_from_chars_safe(str, -1, self ? self->pydecoder : NULL);
}

**and**

char *
psycopg_escape_identifier(connectionObject *conn, const char *str, Py_ssize_t len)
{
char *rv = NULL;

if (!conn || !conn->pgconn) {
    PyErr_SetString(InterfaceError, "connection not valid");
    goto exit;
}

if (len < 0) { len = strlen(str); }

rv = PQescapeIdentifier(conn->pgconn, str, len);
if (!rv) {
    char *msg;
    msg = PQerrorMessage(conn->pgconn);
    if (!msg || !msg[0]) {
        msg = "no message provided";
    }
    PyErr_Format(InterfaceError, "failed to escape identifier: %s", msg);
}

exit:
return rv;
}
```

In the first case here, it appears that a non-null connection is not required (as it defaults to NULL), which is confirmed when looking at the underlying psycopg_text_from_chars_safe() function.

in the second case, the connection instance is only really needed in one line, a call to PQEscapeIdentifier(), which is part of the libpq library, as mentioned earlier by @dvarrazzo.

Mr. Varrazzo also mentioned that this function (taken from the libpq library) relies on taking a connection object as a parameter.

So, PQEscapeIdentifier() calls a function PQEscapeInternal(), which then only references the given connection object four times, twice to access the given encoding, and twice to simply change an error message variable.

Relevant Locations

charlen = pg_encoding_mblen(conn->client_encoding, s);

int i = pg_encoding_mblen(conn->client_encoding, s);

printfPQExpBuffer(&conn->errorMessage, libpq_gettext("incomplete multibyte character\n"));

printfPQExpBuffer(&conn->errorMessage, libpq_gettext("out of memory\n"));

So, to finally get to my point, is there any possibility of simply supplying a "mock" connection object that only has values set for the encoding (which is all that matters in this case)? This could, for example, be exposed through a mock_connection() function in the higher-level python module that would supply a "connection" with a given encoding and other relevant metadata (but no active connection to a real database instance AKA null values or whatever other form that would take) for exactly the type of purpose I want to use it for. I am assuming this could be mirrored at the c-level struct as well as long as the mock is restricted to only as_string() or other functions that do not completely rely on a real database instance (correct me if I am wrong here please!). Would this be feasible?

[dvarrazzo]

Man, if you want to write a lot of fast overhead unit test that's your mission. Our mission is to write a database driver and solving your use case is not relevant for us.

[zlex7]

Fair enough, and thanks for the response. However, if I get it working, can I submit a pull request for this issue?

[ackerleytng]

@zlex7 I felt that I needed this too.

For my rather basic test, I ended up using a helper test function that was able to form the query

def _join_seq(seq):
    parts = str(seq).split("'")
    return "".join([p for i, p in enumerate(parts) if i % 2 == 1])

and then I used this with

_join_seq(query.seq)

instead of

query.as_string(conn)