0

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)
  • 1
    Are you trying to solve a specific problem? Usually when I don't know the table structure, I just ask for a TOP (1) or LIMIT 1 to see what a row looks like (which includes the column names) and then I can construct a better query from there. There are also system views you can look at to determine schema without even looking at the table. I think you can use SHOW COLUMNS FROM cameras in MySQL. – Jonathan Fite Sep 8 '18 at 20:51
  • @JonathanFite I am writing my own python wrapper. I want to create method that would ask for a value based on row ID. – tmdag Sep 8 '18 at 20:53
0

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')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.