Skip to content
Advertisement

Creating dynamically-typed tables using psycopg2’s built-in formatting

I’m working on a project in Python that interacts with a PostgreSQL data warehouse, and I’m using the psycopg2 API. I am looking to create dynamically-typed tables.

For example: I would like to be able to execute the following code:

and end up with a table named “animals” that contains a column “name” of type VARCHAR. However, when I attempt to run this, I get an error: ‘type “VARCHAR” does not exist’. I assume psycopg2’s built-in formatter is putting double quotes around the VARCHAR type when there should not be any. Normally, I would just work around this myself, but the documentation is very clear that Python string concatenation should never be used for fear of SQL injection attacks. Security is a concern for this project, so I would like to know if it’s possible to create dynamically-typed tables in this fashion using pyscopg2, and if not, whether there exists another third-party API that can do so securely. Thank you!

Danny

Advertisement

Answer

I was also having so much trouble with this aspect. sql.Identifier is for double-quoted, well, SQL Identifiers which the datatypes (INTEGER, TEXT, etc.) are not. Looks like just making it plain SQL does the trick.

N.B. In your code, you should have pre-defined columns tuples and not expose their definition to the front-end. This is also why tuples are useful here as they are immutable.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement