How to open again a database after being closed and deleted on SQLITE3

Question

I create a database, then I closed and finally delete it with os.remove, but if I try to create the same database with the same line and then insert a new table the compiler says that I cannot operate in a closed database. If I add a db.open() the compiler says that it hasnt an open attribute.

I tried adding the same connect line on another def but I still cannot operate on a "closed" database when its obvious that I deleted it and then created a new database. I use pyqt5 too, thats why I wrote "QMainWindow"

import sys
from PyQt5.QtWidgets import QApplication, QMainWindow
from PyQt5 import uic
import sqlite3
import os

db = sqlite3.connect("prueba.db")
puntero = db.cursor()

#ayuda a este pobre noob que no sabe sqlite3 ni como funciona los argumentos dentro de un def()de python
class Ventana(QMainWindow):

    def __init__(self):
        QMainWindow.__init__(self)
        uic.loadUi("base.ui",self)

        self.btn_Tabla.clicked.connect(self.createTabla)
        self.btn_Insertar.clicked.connect(self.createDatos)
        self.btn_Borrar.clicked.connect(self.deleteBase)
        self.btn_Crear.clicked.connect(self.createBase)

    def createBase(self):
        db = sqlite3.connect("prueba.db")
        puntero = db.cursor()
        self.txt_Base.setText("database created")

    def createDatos(self):
        x=1

    def createTabla(self):

        puntero.execute('''
    CREATE TABLE Usuarios(id INTEGER PRIMARY KEY, Nombre TEXT,
                       Telefono TEXT, Correo TEXT unique, Contraseña TEXT)
''')
        db.commit()
        self.txt_Base.setText("tables inserted")

    def deleteBase(self):
        db.close()
        os.remove("prueba.db")
        self.txt_Base.setText("deleted database")       



app = QApplication(sys.argv)
_ventana = Ventana()
_ventana.show()
app.exec_()

Are these methods inside of a class that you just didn't paste in the question? — Error - Syntactical Remorse, Mar 25 '19 at 20:52
The code you've provided does not produce the error you've described. Please provide the code that actually produces the error. — mypetlion, Mar 25 '19 at 20:52

score 0 · Accepted Answer · answered Mar 25 '19 at 21:39

A very common error is to create variables with the same name that have different scopes thinking that one will replace the other, in your case you have 2 variables db: one with global scope and another with scope within the function createBase. Instead, reuse the same variable, Also you should not assume that everything works, you must establish the rules in the case that could fail for example if you call more than 2 times to createTabla Do not you think that generates problems ?, Check if the .db exists before trying to delete it.

import sys
import os
import sqlite3
from PyQt5 import QtWidgets, uic

class Ventana(QtWidgets.QMainWindow):
    def __init__(self):
        super(Ventana, self).__init__()
        uic.loadUi("base.ui",self)
        self.btn_Tabla.clicked.connect(self.createTabla)
        self.btn_Insertar.clicked.connect(self.createDatos)
        self.btn_Borrar.clicked.connect(self.deleteBase)
        self.btn_Crear.clicked.connect(self.createBase)
        self.createBase()

    def createBase(self):
        self.db = sqlite3.connect("prueba.db")
        self.puntero = self.db.cursor()
        self.txt_Base.setText("database created")

    def createDatos(self):
        x=1

    def createTabla(self):
        try:
            self.puntero.execute('''
                        CREATE TABLE IF NOT EXISTS Usuarios(id INTEGER PRIMARY KEY, Nombre TEXT,
                       Telefono TEXT, Correo TEXT unique, Contraseña TEXT)
                       ''')
            self.db.commit()
            self.txt_Base.setText("tables inserted")
        except sqlite3.ProgrammingError as e:
            print("Error: ", e)

    def deleteBase(self):
        self.db.close()
        if os.path.exists("prueba.db"):
            os.remove("prueba.db")
            self.txt_Base.setText("deleted database")       


if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    _ventana = Ventana()
    _ventana.show()
    sys.exit(app.exec_())

It worked! But my question is why you added the ```super``` line?? Im curious, and about the createTable def it was just for testing. — , Mar 26 '19 at 03:22
@zemkohai read https://stackoverflow.com/questions/222877/what-does-super-do-in-python — eyllanesc, Mar 26 '19 at 03:23

How to open again a database after being closed and deleted on SQLITE3

1 Answers1