Mysql – get row/value by primary key number

MySQLmysql-8.0python

I'm writing my own python wrapper on top of official mysqldb connector. I'm trying to design method that would dynamically extract row data knowing KEY ID.

 SELECT * FROM cameras;
+----------+----------------------+-----------+------------+
| cameraID | cameraName           | fullframe | sensorSize |
+----------+----------------------+-----------+------------+
|        2 | Canon EOS 5D Mark II |         1 | 36x24      |
|        3 | Nikon                |         1 | 10x10      |
+----------+----------------------+-----------+------------+

knowing table name ("cameras" in example above), and primaryID key integer value (but not primaryID column name), how can i get a row ?

if I had primary key column name, than i could construct:

mysql> SELECT * FROM cameras WHERE cameraID=2;

But How can I ask for it without column name ? Is there something like "WHERE ID=#" or "WHERE 'COLUMN KEY'=#"?

Edit:
I was trying to create multistatement:

mysql> SELECT @columnID:=`COLUMN_NAME` AS columnID FROM `information_schema`.`COLUMNS` WHERE (`TABLE_NAME` = 'cameras') AND (`COLUMN_KEY` = 'PRI');
+----------+
| columnID |
+----------+
| cameraID |
+----------+

but mysql did not allow me to use variable as column name directly:

mysql> SELECT * FROM cameras WHERE ', @columnID, '= '2';
Empty set (0.00 sec)

Best Answer

So I found one 'solution' for mysql:

SELECT @columnID:=`COLUMN_NAME` AS columnID FROM `information_schema`.`COLUMNS` WHERE (`TABLE_NAME` = 'cameras') AND (`COLUMN_KEY` = 'PRI');
SET @SQLCOMMAND = CONCAT('SELECT * FROM cameras WHERE ', @columnID, '= 2');

PREPARE stmt1 FROM @SQLCOMMAND;
EXECUTE stmt1;
+----------+----------------------+-----------+------------+
| cameraID | cameraName           | fullframe | sensorSize |
+----------+----------------------+-----------+------------+
|        2 | Canon EOS 5D Mark II |         1 | 36x24      |
+----------+----------------------+-----------+------------+
1 row in set (0.00 sec)
DEALLOCATE PREPARE stmt1;

As I am using python connector to execute those commands, I had to modify it to different approach. Multiquery was not working for me in this case

cursor = self.conn.cursor(buffered=True)

query1 = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE (`TABLE_NAME` = {0!r}) AND (`COLUMN_KEY` = 'PRI');".format(tablename)
cursor.execute(query1)
columnID = cursor.fetchone()[0]

query2 = "SELECT * FROM {0:s} WHERE {1:s} = {2!r};".format(tablename, columnID, str(idx))
cursor.execute(query2)
single_row = cursor.fetchone()
cursor.close()

(2, 'Canon EOS 5D Mark II', 1, '36x24')

Related Question