-4

I have a table that looks like this (summarized, the first row is the column names):

'hand_total', '2', '3', '4', '5', '6', '7', '8', '9', '10', 'A'
('5', 'H', 'H', 'H', 'H', 'H', 'H', 'H', 'H', 'H', 'H')
('6', 'H', 'H', 'H', 'H', 'H', 'H', 'H', 'H', 'H', 'H')
('7', 'H', 'D', 'D', 'D', 'D', 'H', 'H', 'H', 'H', 'H')
('8', 'D', 'D', 'D', 'D', 'D', 'D', 'D', 'D', 'H', 'H')
('9', 'H', 'D', 'D', 'D', 'D', 'H', 'H', 'H', 'H', 'H')
('10', 'D', 'D', 'D', 'D', 'D', 'D', 'D', 'D', 'H', 'H')
('11', 'D', 'D', 'D', 'D', 'D', 'D', 'D', 'D', 'D', 'H')
('12', 'H', 'H', 'S', 'S', 'S', 'H', 'H', 'H', 'H', 'H')
('13', 'S', 'S', 'S', 'S', 'S', 'H', 'H', 'H', 'H', 'H')

I want to able to use a python variable to pick a value from this table by the header name and the values present in the 'hand_total' column. For example, if I want a value where the '3' column and the row with hand_total=='11' intersect, I would expect to return with the value 'D'. But no matter what I try I can't seem to get my sqlite3 queries to return what I'm expecting.

I have attempted

cur == conn.cursor()
cur.execute("SELECT 3 FROM test_table")
results = cur.fetchall()

I'm expecting the above to return to me all of the column values from the column titled '3'. But the results I get end up just showing

[(3,), (3,), (3,), (3,) ....]

until all length of db

LaBeaux
  • 80
  • 1
  • 7
  • 3
    If a column name is a number, you need to put it inside backticks, otherwise it's treated as a number literal. Try to avoid column names like this. – Barmar Jan 08 '23 at 19:01

1 Answers1

1

If a column name is not an identifier, escape it with double quotes (ANSI SQL), backticks (like MySQL), or square brackets (like SQL-Server).

cur.execute("""SELECT "3" FROM test_table WHERE hand_total = 11""")
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you for explaining that for me! I was stuck on this for a while! I am still facing some issues however. The ```WHERE``` filter isn't doing what I expected. I thought this would filter the column to the specific row I wanted, effectively getting me that cell. However when I run the statement you specified I receive the column contents: ```[('H',), ('H',), ('D',), ('D',) ...]``` and so on. – LaBeaux Jan 09 '23 at 03:31
  • I just figured it out. I was using formatted strings to input python variables as the filter values. In my code I was using double quotes around the WERE filter value when I should have been using single quote marks since the column titles all had quotes in themselves, e.g.: ``` cur.execute(f"""SELECT "{column}" FROM {table_name} WHERE hand_total='{total}' """) ``` – LaBeaux Jan 09 '23 at 03:36
  • Don't use string formatting to insert variables into SQL. See https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python – Barmar Jan 09 '23 at 08:28