无法在sqlite3 python中的闭合游标上操作

I've been following this 5 part tutorial from this YouTube playlist: https://www.youtube.com/playlist?list=PLQVvvaa0QuDezJh0sC5CqXLKZTSKU1YNo

我一直在关注此YouTube播放列表中的这个5部分教程:https://www.youtube.com/playlist?list = PLQVvvaa0QuDezJh0sC5CqXLKZTSKU1YNo

I'm using Jupyter notebook with Python with the following code:

我正在使用带有Python的Jupyter笔记本,代码如下:

import sqlite3
import time
import datetime
import random

conn = sqlite3.connect("tutorial2.db")
c = conn.cursor()

Then I create several functions.

然后我创建了几个函数。

def create_table():
    c.execute('CREATE TABLE IF NOT EXISTS stuffToPlot (unix REAL, datestamp TEXT,
    keyword TEXT, value REAL)')

def data_entry():

    c.execute("INSERT INTO stuffToPlot VALUES (145123542, '2016-01-03',
              'Python', 7)")
    conn.commit()
    c.close()
    conn.close()

create_table()
data_entry()

It works fine the first time, and generates a db file in C:\Users\Michael However, when I try to run only the create_table() function again, I get the following error:

它第一次工作正常,并在C:\ Users \ Michael中生成一个db文件但是,当我尝试再次只运行create_table()函数时,我收到以下错误:

ProgrammingError: Cannot operate on a closed cursor.

Anyone able to help resolving this issue would be greatly appreciated!

任何能够帮助解决这个问题的人将不胜感激!

1 个解决方案

#1

The error is pretty explicit: You cannot run queries on closed cursors. Here it's even worse since you have also closed the connection at the first call of the data_entry() function.

该错误非常明确:您无法对已关闭的游标运行查询。更糟糕的是,因为您在第一次调用data_entry()函数时也关闭了连接。

I would advise on opening a cursor for each query, and then closing it after completing the query, and only closing the connection at the end of your script:

我建议为每个查询打开一个游标,然后在完成查询后关闭它,并且只关闭脚本末尾的连接:

import sqlite3
import time
import datetime
import random

conn = sqlite3.connect("tutorial2.db")

def create_table():
    c = conn.cursor()
    c.execute('CREATE TABLE IF NOT EXISTS stuffToPlot (unix REAL, datestamp TEXT, keyword TEXT, value REAL)')
    c.close()

def data_entry():
    c = conn.cursor()
    c.execute("INSERT INTO stuffToPlot VALUES (145123542, '2016-01-03', 'Python', 7)")
    conn.commit()
    c.close()


create_table()
data_entry()

By moving the conn.close() statement after you have completed all of your queries, and opening the cursors only when you need them, the error won't occur anymore.

通过在完成所有查询后移动conn.close()语句,并仅在需要时打开游标,错误将不再发生。

EDIT : What is happening in your video is the following:

编辑:您的视频中发生了以下情况:

He first executes the whole script once.

他首先执行整个脚本一次。

He comments the line that creates the table.

他评论创建表的行。

He executes the whole script a second time.

他第二次执行整个脚本。

I think you are probably entering the commands in a python interactive session, which is not equivalent to what he is doing in the video, because when he re-executes the script, a new connection and cursor are created, whereas if you're only trying to call the function again, the cursor and connection are already closed, which causes the error.

我想你可能在python交互式会话中输入命令,这与他在视频中所做的不同,因为当他重新执行脚本时,会创建一个新的连接和光标,而如果你只是试图再次调用该函数,光标和连接已经关闭,这会导致错误。

#1