python - 如何使用 sqlite3 制作内存数据库

Question

我想做一个内存数据库，但我知道这两个数据库之间的区别是内存数据库，我们不应该在最后一行关闭它。我试过了，但我做不到，我无法在网上搜索找到答案。

这是数据库的完整代码：

    def connect():
    conn = sqlite3.connect(":memory:")
    cur = conn.cursor()
    cur.execute(
       """CREATE TABLE itm (id INTEGER PRIMARY KEY , namee VARCHAR , numbers INTEGER , price INTEGER )""")
    conn.commit()


def insert(name, number, price):
    conn = sqlite3.connect(":memory:")
    cur = conn.cursor()
    cur.execute(
        """INSERT INTO itm VALUES(NULL , ?, ?,?)""",(name, number, price)
    )
    conn.commit()


def view():
    conn = sqlite3.connect(":memory:")
    cur = conn.cursor()
    cur.execute(
        "SELECT * FROM itm"
    )
    rows = cur.fetchall()
    return rows

def delete(id):
    conn = sqlite3.connect(":memory:")
    cur = conn.cursor()
    cur.execute("DELETE FROM itm WHERE id=?", (id,))
    conn.commit()




connect()

最后，当我运行项目 python 引发此错误：

    Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Green\AppData\Local\Programs\Python\Python39\lib\tkinter\__init__.py", line 1892, in __call__
    return self.func(*args)
  File "D:\python\WindowsProject\app\manager\manager_sign_in.py", line 44, in back_to_main_manager
    main_screen()
NameError: name 'main_screen' is not defined
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Green\AppData\Local\Programs\Python\Python39\lib\tkinter\__init__.py", line 1892, in __call__
    return self.func(*args)
  File "D:\python\WindowsProject\app\manager\sign.py", line 33, in back_to_main_mngr
    main_screen()
NameError: name 'main_screen' is not defined
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Green\AppData\Local\Programs\Python\Python39\lib\tkinter\__init__.py", line 1892, in __call__
    return self.func(*args)
  File "D:\python\WindowsProject\app\main.py", line 33, in user_sign
    user_screen()
NameError: name 'user_screen' is not defined
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Green\AppData\Local\Programs\Python\Python39\lib\tkinter\__init__.py", line 1892, in __call__
    return self.func(*args)
  File "D:\python\WindowsProject\app\user\user_sign_in.py", line 188, in choose
    item_memory = app.user.memory.insert(name_var.get(), numbers.get(), price,)
  File "D:\python\WindowsProject\app\user\memory.py", line 15, in insert
    cur.execute(
sqlite3.OperationalError: no such table: itm
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Green\AppData\Local\Programs\Python\Python39\lib\tkinter\__init__.py", line 1892, in __call__
    return self.func(*args)
  File "D:\python\WindowsProject\app\user\user_sign_in.py", line 188, in choose
    item_memory = app.user.memory.insert(name_var.get(), numbers.get(), price,)
  File "D:\python\WindowsProject\app\user\memory.py", line 15, in insert
    cur.execute(
sqlite3.OperationalError: no such table: itm

标签： pythonsqlite

Answer 1

你不能sqlite3.connect(":memory:")在每个函数中做；他们每个人都会得到一个新的空数据库。

相反，您可以这样做，例如，conn在您的流程中将单个动作保留为全局变量。

您也不需要明确关闭它；当您的程序退出时，它将被干净地关闭。

conn = None


def connect():
    global conn
    if conn:
        raise RuntimeError("You can't call connect() twice")
    conn = sqlite3.connect(":memory:")
    cur = conn.cursor()
    cur.execute("""CREATE TABLE itm (id INTEGER PRIMARY KEY , namee VARCHAR , numbers INTEGER , price INTEGER )""")
    conn.commit()


def insert(name, number, price):
    cur = conn.cursor()
    cur.execute("""INSERT INTO itm VALUES(NULL , ?, ?,?)""", (name, number, price))
    conn.commit()


def view():
    cur = conn.cursor()
    cur.execute("SELECT * FROM itm")
    rows = cur.fetchall()
    return rows


def delete(id):
    cur = conn.cursor()
    cur.execute("DELETE FROM itm WHERE id=?", (id,))
    conn.commit()


connect()