英文:
How to retrieve a value from a SQLite3 table by column name and matching row value
问题
我想要能够使用Python变量来从这个表中选择一个值，根据标题名称和“hand_total”列中的值。例如，如果我想要一个值，其中'3'列和hand_total=='11'的行相交，我希望返回值是'D'。但无论我尝试什么，我似乎无法让我的sqlite3查询返回我期望的结果。
我已经尝试过以下代码：
cur = conn.cursor()
cur.execute("SELECT `3` FROM test_table")
results = cur.fetchall()
我期望上面的代码会返回标题为'3'的列的所有值。但是我得到的结果似乎只显示：
[(3,), (3,), (3,), (3,) ....]
直到数据库的长度。
英文:
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
答案1
得分: 1
如果列名不是标识符，请使用双引号进行转义（ANSI SQL），或使用反引号（如MySQL），或使用方括号（如SQL Server）。
cur.execute("""SELECT "3" FROM test_table WHERE hand_total = 11""")
英文:
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""")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论