Recommended workaround for () in a named parameter?

[polynomialherder]

Hi there

I see from the documentation and recent issues that using () in named parameters isn't supported. Are there any recommended workarounds?

In this case I would like to avoid mangling the columns / replacing () with other characters if possible, especially since postgres supports parentheses in columns and this just seems to be a limitation of the psycopg2 mogrify parser.

I'm happy to make a PR to modify the mogrify parser to handle this situation if the reason these characters aren't supported is a matter of bandwidth/prioritization. I'm also interested to know if there are any preferred workarounds for the meanwhile or if there's no intention to ever support parens in column names

Thank you!

[dvarrazzo]

The fact that you cannot use a ) in a parameter name doesn't stop you to use ) in postgres fields names, if that sounds a good idea to you.

Workarounds are pretty trivial: just drop the ) or replace them with another character. Any Python tutorial can teach how to do it.

I don't think that you can write a parser that can decide whether %(foo)) is a foo placeholder followed by a ) or a foo) placeholder. So no, we are not interested in a MR and we don't think the limitation is a problem.

[polynomialherder]

My question was about if there is a preferred workaround within psycopg2 before I do something adhoc to support it myself, since constraints in the system I'm working on require the funny names. The ANSI standard, Postgres, and nearly every real-world RDBMS allow it, so hopefully you can understand it was bit unexpected and surprising for me that the driver disallows it and breaks ungracefully in this case. After your explanation I understand why, no worries

I've been writing Python for a decade now, so indeed I know how to replace a character in a string, but thank you anyway for taking the time.

[dvarrazzo]

No, I don't think that there is a "canonical solution" because it doesn't happen often that people wants to use non-alphanumeric characters in the placeholder names. I guess there are two possible scenarios:

• someone writes a query and parameters "by hand" as part of the program
• someone writes an automatic system to generate queries

In the first case, people writing a query can use other names, it makes little sense to try to fight the system to use a ).

In the second case you can do something like replacing every non-alnum char with a supported char such as _ or, if there is the risk of collisions, create an explicit mapping from values to parameter name (a map like {"value 1": "p0", "some(thing)": "p1"} that maps the values potentially containing braces to safe-to-use values).

[polynomialherder]

I can appreciate that a small number of people face this requirement, but (I think) a database driver should mostly get out of your way and facilitate the interaction without imposing e.g. different naming conventions. Databases in particular have lots of use cases, and certainly not every use case needs to be supported, but this one doesn't seem unreasonable. At the very least limitations like this should be more transparent.

My use case is a small automated system that loads data files uploaded by internal users. The goal is for this process to be as transparent to the users as possible and to stay as true to the original file as possible so that they can query them transparently.

It's really about accessibility: it's not uncommon in a business setting that people work with data files that have columns like margin (net). Most other data software can handle this just fine, including most RDBMS and Postgres itself as I mentioned earlier. Like, it's a bit weird to have to explain to someone that, sorry, your column is now called margin net or margin_net or something due to technical limitations -- I mean, it's not the 1980s anymore, it's the year 2023 after all :)

I appreciate that this project is maintained by volunteers who have limited time and need to prioritize the most common use cases. In any case, I am offering to do the work of either improving this limitation or at the very least making the error message or documentation more transparent. It wasn't easy to locate the issue and I only found the documentation after looking at the relevant lines in cursor_type.c.

Perhaps we can add a mechanism for escaping parens or configuring a different sigil that signals a named parameter? Or at the very least mention unsupported characters and other failure modes when mogrify fails?

[dvarrazzo]

Psycopg doesn't put any constraint in the column names: brackets or any other character in column names is supported. It is true that Postgres doesn't offer limitation in column names, but it does with parameters: should you ever write queries in pure postgres parametric functions you could only use numbers as parameters ($1, $2, etc). Parameters substitution is a language inside the language and has its own limitations which are not related to the outermost one.

Query parameters in psycopg2 have their language, which is the one of Python string formatting: the limitation comes from there.

>>> "%(foo()s" % {"foo(": 10}
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: incomplete format key

>>> "%(foo))s" % {"foo)": 10}
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
KeyError: 'foo'

Given these tools, you can still do everything. The only thing you cannot have is a 1:1 mapping from column name to placeholder name. This is a non-limitation: you can write whatever Python code to manage the translation. As I said earlier, it's not a matter of lack of resource to address this problem: the language of %(name)s placeholders doesn't support ) in the name for reasons that frankly are pretty obvious and easy to work around. If you want to embed a ) between () delimiters you have to come up with an escaping mechanism that probably 1) will create non-backward compatibilities and 2) if you want to use you will still need to add an escaping function to your program. So you may as well write an escaping function that instead mangles the column name into a string using the supported alphabet.

[polynomialherder]

Got it -- I apologize for the misunderstanding. The named parameter itself is only used by psycopg2 to know how to interpolate the values from the dictionary I hand it. In particular it doesn't necessarily have anything to do with the names of columns in the database, since I'm parametrizing, for example, the VALUES part of my insert, not the column specification. Sorry for being dense here! Thanks again for your time