无法在关闭的数据库上操作

Question

我犯了这个错误，我不知道为什么。我试图删除db close代码，但没有出现错误，但是如果我不关闭数据库，可以吗

Traceback (most recent call last):
  File "C:/Users/hp/Downloads/SimpleCoin-master/SimpleCoin-master/simpleCoin/recent.py", line 55, in <module>
    st()
  File "C:/Users/hp/Downloads/SimpleCoin-master/SimpleCoin-master/simpleCoin/recent.py", line 31, in st
    tran()
sqlite3.ProgrammingError: Cannot operate on a closed database.

这是我的密码

import sqlite3, time
from datetime import datetime


    def st():
        with sqlite3.connect("newone1.db") as db2:
            cursor1 = db2.cursor()
            # create table
            cursor1.execute('''
                CREATE TABLE IF NOT EXISTS transactions(
                private_k VARCHAR(100) NOt NUll,
                sender_k VARCHAR(100) NOT NULL,
                receiver_k VARCHAR(100) NOT NULL,
                c_amount INTEGER NOT NULL,
                dNt TEXT NOT NULL);
                ''')
            # insert values to table from user input
    
            pri_key = input('Sender Private Key: ')
            pub_key = input('Sender Public Key: ')
            r_pub_k = input('Receiver Public Key: ')
            c_amount = input('Amount: ')
            dt_for = datetime.now().strftime("%B %d, %Y %I:%M%p")
            cursor1.execute(""" 
                INSERT INTO transactions(private_k, sender_k, receiver_k, c_amount, dNt)
                VALUES (?,?,?,?,?)   
                """, (pri_key, pub_key, r_pub_k, c_amount, dt_for))
            db2.commit()
            # print("Data entered successfully")
            db2.close()
    
    
    def tran():
        private_key = input("Enter your private key number: ")
        with sqlite3.connect("newone1.db") as db2:
            cursor1 = db2.cursor()
    
        sql_cmd = 'SELECT * FROM transactions WHERE private_k={}'.format(private_key)
        cursor1.execute(sql_cmd)
        for row in cursor1.fetchall():
            pri_key = row[0]
            pub_key = row[1]
            r_pub_k = row[2]
            c_amount = row[3]
            dt_for = row[4]
            # print(pri_key)
            print('-------------------------------')
            print("From:", pub_key)
            print("To:", r_pub_k)
            print("Amount: ", c_amount)
            print("Date and Time: ", dt_for)
            print('-------------------------------')
    
    
    st()
    tran()

Answer 1

我只是通过@snakecharmerb来扩展评论。这是我的功劳

with语句正在连接到数据库，并将该连接存储在db2变量中。当with语句的作用域结束时，db2被破坏，连接被关闭。在python中，作用域以取消身份结束。因此，您的连接在sql_cmd = 'SELECT...行之前被关闭。您需要在fetchall完成之前将其保留在范围内

因此，这应该更有效：

    def tran():
        private_key = input("Enter your private key number: ")
        with sqlite3.connect("newone1.db") as db2:
            cursor1 = db2.cursor()
    
            sql_cmd = 'SELECT * FROM transactions WHERE private_k={}'.format(private_key)
            cursor1.execute(sql_cmd)
            for row in cursor1.fetchall():
                pri_key = row[0]
                pub_key = row[1]
                r_pub_k = row[2]
                c_amount = row[3]
                dt_for = row[4]
                # print(pri_key)
                print('               -')
                print("From:", pub_key)
                print("To:", r_pub_k)
                print("Amount: ", c_amount)
                print("Date and Time: ", dt_for)
                print('               -')