我想SELECT value of column_2 from the sqlite3 TABLE table_name WHERE, column_3 value is 'Waiting'
。你知道吗
我想传递列名、表名和条件检查值作为变量。你知道吗
我在课堂上写过这样的脚本,但失败了。你知道吗
#!/usr/bin/python
import sys
import os
import sqlite3
import re
class Read_class(object):
def __init__(self,db_name,table_name,status,column_to_read,column_state):
self.db_name = db_name
self.table_name = table_name
self.status = status
self.column_to_read = column_to_read
self.column_state = column_state
def read_waiting(self):
db_name = self.db_name
column_2 = self.column_to_read
table_name = self.table_name
column_3 = self.status
print column_3
column_state = self.column_state
print column_state
conn = sqlite3.connect(db_name)
print "Opened database successfully";
c = conn.cursor()
c.execute("SELECT ({coi}) FROM {tn} WHERE {cn} = {cs}".\
format(coi=column_2, tn=table_name, cn=column_3,cs=column_state))
conn.commit()
row = c.fetchall()
print row
return row
C = Read_class('test.db', 'Test_Task', 'status', 'Task_ID','Waiting')
task_id = C.read_waiting()
我得到以下错误:
dccom@dccom-vm:~/auto/scripts$ python class.py
status
Waiting
Opened database successfully
Traceback (most recent call last):
File "class.py", line 70, in <module>
task_id = C.read_waiting()
File "class.py", line 26, in read_waiting
format(coi=column_2, tn=table_name, cn=column_3,cs=column_state))
sqlite3.OperationalError: no such column: Waiting.
My sqlite3 db schema and data stored on the db is as follows:
CREATE TABLE Test_Task
(User_Name TEXT NOT NULL,
User_Email varchar(20),
Image_Name varchar(30),
UGW_Tag varchar(20),
WAN_Mode varchar(10),
Test_Type varchar(20),
Sub_Type varchar(20),
Task_ID INT NOT NULL,
status varchar(20));
数据库中存储的数据:
sqlite> SELECT * from Test_Task;
nagesh|nag@gmail.com|20161020134234_CSX5000.tar|Ubuntu16.04xenial|WAN|SANITY|xxxxxi|1|Running
ramesh|ramesh@gamil.com|123_wan.tar|12ertf|LAN|FULL|aqbc|2|Waiting
vishal|vish@gmail.com|20161020135030_CSX5000.tar|Ubuntu16.04xenial|WAN|SANITY|xxxxxi|3|Running
ramesh|ramesh@gamil.com|123_wan.tar|12ertf|LAN|FULL|aqbc|4|Waiting
请建议如何做到这一点。你知道吗
如果格式化SQL并将其打印出来,您将很快看到它包含
WHERE status = Waiting
。您希望它包含WHERE status = 'Waiting'
(注意文本字符串周围的单引号)。您的版本会导致SQL查找名为Waiting的列的内容并进行比较,而不是字符串“Waiting”。你知道吗搜索
status
列包含单词Waiting的记录的正确SQL是:但你产生的是:
第二段SQL正在搜索列
status
和Waiting
具有相同值的记录。因为表Test_Task
不包含名为Waiting的列,SQLite无法解释SQL并产生您看到的错误。你知道吗请注意,您提供的值应该而不是通过格式化(如您所做的那样)放置在字符串中,但应该由一个参数表示并分别传递到execute中。这是因为1)如果值来自用户输入,那么SQL注入是可能的;2)如果值本身包含一个单引号(例如姓O'Malley),那么它将不起作用。你知道吗
您还可以消除方法
read_waiting
中的所有局部变量,并直接引用实例值(self.table_name
,等等)。你知道吗相关问题 更多 >
编程相关推荐