查询数据库中的元组列表 [英] Querying a list of tuples in Database
问题描述
由于我是python编程的新手，所以我对从数据库查询数据一无所知.
首先，我在SQL Server中创建了数据库，该数据库有两列:String列和Numeric列.
Since I am new to python programming, I don’t know more about querying a data from a database.
First, I’ve created my database in SQL Server, with 2 columns: The String column and the Numeric column.
+--------+---------+
| String | Numeric |
+--------+---------+
| One | 1 |
| Five | 5 |
| Three | 3 |
| Seven | 7 |
| Eight | 8 |
| Two | 2 |
+--------+---------+
+--------+---------+
| String | Numeric |
+--------+---------+
| One | 1 |
| Five | 5 |
| Three | 3 |
| Seven | 7 |
| Eight | 8 |
| Two | 2 |
+--------+---------+
例如:
X = [(‘three’,’odd’), (‘one’,’odd’), (‘two’,’even’)]
现在，我希望输出遵循以下顺序:odd – even – odd，因此将类似于此1 2 3.
+-----+------+-----+
| Odd | Even | Odd |
+-----+------+-----+
| 1 | 2 | 3 |
+-----+------+-----+
Now I want that the output follow this sequence: odd – even – odd so it will be like this 1 2 3.
+-----+------+-----+
| Odd | Even | Odd |
+-----+------+-----+
| 1 | 2 | 3 |
+-----+------+-----+
如何根据odd或even string是否在数据库中来查询string的numeric值?
How to query the numeric value of the string based on if odd or even the string is in database?
推荐答案
您的查询对我来说并不明确.您混合使用SQL数据结构和Python数据结构，目前尚不清楚是从Python还是从SQL调用它.您的查询应包含您要执行的一些代码.
Your query is not clear for me. You mix SQL data structures and Python data structures and it is not clear if this will be called from Python or from SQL. Your query should contain some code you are trying to execute.
使用SQL表中的简单数据，您可以将SQL表视为Python字典. NUMSTR dict代表您第一个SQL表，我将X元组列表更改为dict:
With such a simple data in your SQL tables you can think of SQL tables as of Python dictionaries. NUMSTR dict represents you first SQL table, and I change X list of tuples into dict:
NUMSTR = {1: 'one', 2: 'two', 3: 'three'}
X = {'three': 'odd', 'one': 'odd', 'two': 'even'}
def show_even_odd(in_numbers):
numbers_info = []
for i in in_numbers:
try:
num_name = NUMSTR[i]
eo = X[num_name]
except KeyError:
eo = '???'
numbers_info.append(eo)
print(' | '.join(['%4s' % x for x in numbers_info]))
print(' | '.join(['%4s' % x for x in in_numbers]))
def test():
show_even_odd([1, 2, 3])
我将数据存储在numbers_info中以一行显示.在单独的行中显示有关每个数字的信息会更容易.
I stored data in numbers_info to display it in one line. It would be easier to show info about each number in separate line.
编辑
如果您的问题是获取每个元组的第一个值并显示其数值，则此类代码如下:
If your problem is to get first value of each tuple and display its numeric value then such code looks like:
X = [('three','odd'), ('one','odd'), ('two','even')]
for nn, eo in X:
print('%s - %s' % (get_number(nn), eo))
现在，您必须定义get_number()函数.使用全局cursor可能会起作用:
Now you must define get_number() function. With global cursor this may work:
def get_number(number_name):
result = number_name
cursor.execute('SELECT numeric FROM my_table WHERE String = ?', number_name)
for txt in cursor.fetchall():
result = txt[0]
return result
PS在此代码中，我使用了SELECT中的?来准备语句. ODBC驱动程序应将其替换为number_name.此类操作可以通过Python:"SELECT ... WHERE String = '%s'" % (number_name)完成，但预备语句要好得多.它们可以防止SQL注入，并且数据库可以更好地缓存此类语句的查询计划.
PS In this code I used ? in SELECT to make prepared statement. It should be replaced with number_name by ODBC driver. Such operation can be done by Python: "SELECT ... WHERE String = '%s'" % (number_name), but prepared statements are much better. They prevent against SQL Injection and database can better cache query plan for such statements.
这篇关于查询数据库中的元组列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
