-3

I have an SQL DB with 3/4 tables in it. I would like to write 1 Python function that could use parameters to search the relevant tables. Is this possible?

The line I was thinking would be

self.cur.execute(select * from ? (Table))

this obviously works when choosing a column but I cannot get it to work for table. Is it possible, or should I change plan?

rdulantoc
  • 44
  • 7
CS1234
  • 9
  • 1
  • It's not possible in that way. You can do string interpolation, but be aware of [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). – mechanical_meat Feb 21 '20 at 15:25
  • 1
    No. You cannot parameterize objects. The reason for this is that your SQL is sent into the database before the parameters. Your database parses it and generates the execution path (determines how to best select the data and make the result set). Then the parameters are considered. It can't create the execution path if it doesn't know which table is being read. – JNevill Feb 21 '20 at 15:26
  • 1
    No. Tables are similar to types in a strongly-typed language, not parameters. Queries are compiled to execution plans. Parameters are fed to that execution plan. Execution plans though, depend not only on the query, but the table schema, indexes, number and distribution of values. Even for the same table, a very different execution plan may be generated if the table contains a few dozen or a few million rows – Panagiotis Kanavos Feb 21 '20 at 15:27
  • Does this answer your question? [Passing table name as a parameter in psycopg2](https://stackoverflow.com/questions/13793399/passing-table-name-as-a-parameter-in-psycopg2) – Mihai Chelaru Feb 21 '20 at 15:32

1 Answers1

1

No. Tables are similar to types in a strongly-typed language, not parameters.

Queries aren't executed like scripts. They are compiled into execution plans, using different operators depending on the table schema, indexes and statistics, ie the number of rows and distribution of values. For the same JOIN, the query optimizer may decide to use a HASH JOIN for unordered, unindexed data or nested loops if the join columns are indexed. Or a MERGE join can be used if the data from both tables is ordered.

Even for the same query, a very different execution plan may be generated if the table contains a few dozen or a few million rows

Parameters are passed to that execution plan the same way parameters are passed to a method. They are even passed separately from the SQL text in the RPC call from client to server. That's why they aren't vulnerable to SQL injection - they are never part of the query itself.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236