python - sqlite3.OperationalError:没有这样的表?
问题描述
import sqlite3
class Database1:
def __init__(self,**args):
self.filename = args.get('filename')
self.table = args.get('table','test')
def sql_do(self,sql,*params):
self._db.execute(sql,params)
def insert(self,row):
self._db.execute('insert into {}(t1,i1)values(?,?)'.format(self._table),(row['t1'],row['i1']))
def retrieve(self,key):
cursor = self._db.execute('select * from {} where i1 = ?'.format(self._table),(key,))
return dict(cursor.fetchone())
def update(self,row):
self._db.execute('update {} set t1 = ? where i1 = ?'.format(self._table),(row['t1'],row['i1']))
def delete(self,key):
self._db.execute('delete from {} where i1 = ?'.format(self._table))
def __iter__(self):
cs = self._db.execute('select * from {} order by i1'.format(self._table))
for i in cs:
yield dict(i) #no dict
@property
def filename(self): return self._filename
@filename.setter
def filename(self,fn):
self._filename = fn
self._db = sqlite3.connect(fn)
self._db.row_factory = sqlite3.Row
@filename.deleter
def filename(self): self.close()
@property
def table(self): return self._table
@table.setter
def table(self,T): self._table = T
@table.deleter
def table(self): self._table = "test"
def close(self):
self._db.close()
del self._filename
def main():
db = Database1(filename = "dbfile.db",table = "test")
db.sql_do('drop table if exists dbfile')
db.sql_do('create table dbfile(t1 text,i1 int)')
print('Insert data')
db.insert(dict(t1 = 'one',i1 = 1))
db.insert(dict(t1 = 'two',i1 = 2))
db.insert(dict(t1 = 'three',i1 = 3))
for disp in db: print(disp)
print('Retrieve data')
print(db.retrieve(1),db.retrieve(2),db.retrieve(3))
print('Updateed data')
db.update(dict(t1 = 'amir',i1 = 1))
db.update(dict(t1 = 'reza',i1 = 2))
for disp in db: print(disp)
print('deleteed data')
db.delete(3)
db.delete(1)
for disp in db: print(disp)
main()
为什么会出错?
Traceback (most recent call last):
File "c:\Users\Computer Parsian\Desktop\Untitled-1.py", line 76, in <module>
main()
File "c:\Users\Computer Parsian\Desktop\Untitled-1.py", line 57, in main
db.insert(dict(t1 = 'one',i1 = 1))
File "c:\Users\Computer Parsian\Desktop\Untitled-1.py", line 12, in insert
self._db.execute('insert into {}(t1,i1)values(?,?)'.format(self._table),(row['t1'],row['i1']))
sqlite3.OperationalError: no such table: test
解决方案
您的错误是因为您在创建表时已将表命名为dbfile
db.sql_do('drop table if exists dbfile')
db.sql_do('create table dbfile(t1 text,i1 int)')
您随后尝试将数据插入名为test的表中,该表不存在,因为唯一的表是dbfile。
此外,您在删除时省略了绑定参数:-
self._db.execute('delete from {} where i1 = ?'.format(self._table))
我相信以下内容符合您的要求(请参阅有关更改的评论):-
import sqlite3
class Database1:
def __init__(self,**args):
self.filename = args.get('filename')
self.table = args.get('table','test')
def sql_do(self,sql,*params):
self._db.execute(sql,params)
def insert(self,row):
self._db.execute('insert into {}(t1,i1)values(?,?)'.format(self._table),(row['t1'],row['i1']))
def retrieve(self,key):
cursor = self._db.execute('select * from {} where i1 = ?'.format(self._table),(key,))
return dict(cursor.fetchone())
def update(self,row):
self._db.execute('update {} set t1 = ? where i1 = ?'.format(self._table),(row['t1'],row['i1']))
def delete(self,key):
self._db.execute('delete from {} where i1 = ?'.format(self._table),(key,)) #<<<<< Amended
def __iter__(self):
cs = self._db.execute('select * from {} order by i1'.format(self._table))
for i in cs:
yield dict(i) #no dict
@property
def filename(self): return self._filename
@filename.setter
def filename(self,fn):
self._filename = fn
self._db = sqlite3.connect(fn)
self._db.row_factory = sqlite3.Row
@filename.deleter
def filename(self): self.close()
@property
def table(self): return self._table
@table.setter
def table(self,T): self._table = T
@table.deleter
def table(self): self._table = "test"
def close(self):
self._db.close()
del self._filename
def main():
table = "test"
db = Database1(filename = "dbfile.db",table = table) #<<<<< Optional use the single value previously defined
db.sql_do('drop table if exists ' + table) #<<<<< Amended
db.sql_do('create table ' + table + '(t1 text,i1 int)') #<<<<< Amended
print('Insert data')
db.insert(dict(t1 = 'one',i1 = 1))
db.insert(dict(t1 = 'two',i1 = 2))
db.insert(dict(t1 = 'three',i1 = 3))
for disp in db: print(disp)
print('Retrieve data')
print(db.retrieve(1),db.retrieve(2),db.retrieve(3))
print('Updateed data')
db.update(dict(t1 = 'amir',i1 = 1))
db.update(dict(t1 = 'reza',i1 = 2))
for disp in db: print(disp)
print('deleteed data')
db.delete(3)
db.delete(1)
for disp in db: print(disp)
main()
运行此结果:-
Insert data
{'t1': 'one', 'i1': 1}
{'t1': 'two', 'i1': 2}
{'t1': 'three', 'i1': 3}
Retrieve data
{'t1': 'one', 'i1': 1} {'t1': 'two', 'i1': 2} {'t1': 'three', 'i1': 3}
Updateed data
{'t1': 'amir', 'i1': 1}
{'t1': 'reza', 'i1': 2}
{'t1': 'three', 'i1': 3}
deleteed data
{'t1': 'reza', 'i1': 2}
Process finished with exit code 0
推荐阅读
- python - 如何将多个数组放入一个带有标题 Python 的 csv 文件
- visual-studio-code - .vscode/settings.json 和 .code-workspace 有什么区别
- php - 403 The request could not be seen response when hit the Cowin API
- swift - FSCalendar - 我想在 longPressGesture 上选择日期。迅速
- android - 比较两个 Android Rect 位置的更优雅的方法?
- python - 通过 pip -r requirements.txt 安装依赖项
- python - Django 模板四舍五入到最近的非零
- angular - 如何在 Angular 11 中重新渲染元素
- sql - 会计在 SQL(ssms) 中计算借方贷方
- java - 识别一个按钮点击列表视图android