Python3 SQLite 行不会在 Ubuntu 上删除，但会在 Windows 上删除 [英] Python3 SQLite row won't be removed on Ubuntu, but will be removed on Windows

问题描述

我试图在 SQLite3 中删除一行，在 Windows 上它会删除该行，但在 Ubuntu 上不会.我不确定是什么原因造成的/如何解决它.两个系统都运行Python 3.6.5，我没有用pip安装SQLite3.

I'm trying to remove a row in SQLite3, on windows it removes the row, but on Ubuntu it won't. I'm not sure what's causing it/how to fix it. Both systems are running Python 3.6.5, and I did not install SQLite3 with pip.

我正在运行以下脚本，该脚本创建一个 db.sqlite，创建一个包含 (key, name) 的用户表并插入一个用户.然后它应该删除它:

I'm running the following script, which creates a db.sqlite, creates a user table with (key, name) and inserts one user. Then it should remove it:

import sqlite3

class DBHelper:
    def __init__(self, dbname="db.sqlite"):
        self.dbname = dbname
        self.conn = sqlite3.connect(dbname)
        self.conn.set_trace_callback(print)

    def setup(self):
        stmt = "CREATE TABLE IF NOT EXISTS users (key text PRIMARY KEY, name text)"
        self.conn.execute(stmt)
        stmt = "INSERT INTO users (key, name) VALUES ('Acf233146328cea01fe9648acc3053fa', 125368500)"
        self.conn.execute(stmt)
        self.conn.commit()

    def delete_user(self, name, key):
        stmt = "DELETE FROM users WHERE name = (?) AND key = (?)"
        args = (name, key)
        self.conn.execute(stmt, args)
        self.conn.commit()

    def get_all(self):
        stmt = "SELECT name, key FROM users"
        return [x for x in self.conn.execute(stmt)]


def get_db():
    db = DBHelper()
    return db


name = 125368500
key = 'Acf233146328cea01fe9648acc3053fa'

print('Delete {0} {1}'.format(name, key))
db = get_db()
db.setup()
db.delete_user(name, key)
for user_data in db.get_all():
    print('{0} {1}'.format(user_data[0], user_data[1]))

在 Ubuntu 上它返回(不正确):

On Ubuntu it returns (incorrect):

Delete 125368500 Acf233146328cea01fe9648acc3053fa
CREATE TABLE IF NOT EXISTS users (key text PRIMARY KEY, name text)
BEGIN
INSERT INTO users (key, name) VALUES ('Acf233146328cea01fe9648acc3053fa', 125368500)
COMMIT
BEGIN
DELETE FROM users WHERE name = (125368500) AND key = ('Acf233146328cea01fe9648acc3053fa')
COMMIT
SELECT name, key FROM users
125368500 Acf233146328cea01fe9648acc3053fa

在 Windows 上它返回(正确):

On Windows it returns (correct):

Delete 125368500 Acf233146328cea01fe9648acc3053fa
CREATE TABLE IF NOT EXISTS users (key text PRIMARY KEY, name text)
BEGIN
INSERT INTO users (key, name) VALUES ('Acf233146328cea01fe9648acc3053fa', 125368500)
COMMIT
BEGIN
DELETE FROM users WHERE name = (125368500) AND key = ('Acf233146328cea01fe9648acc3053fa')
COMMIT
SELECT name, key FROM users

更新:所以我似乎遇到了 SQLite 3.11.0 中引入的错误:https://sqlite.org/src/info/ef360601而且因为 Ubuntu 16.04.4 LTS 默认带有 3.11.0 我需要更新版本.

Update: So it seems like I'm experiencing a bug introduced in SQLite 3.11.0: https://sqlite.org/src/info/ef360601 And because Ubuntu 16.04.4 LTS comes with 3.11.0 by default I am going to need to update the version.

更新 2:通过将名称更改为字符串来更新类型也修复了它.在sqlite错误报告中似乎是相同的情况.

Update 2: Updating the typings by changing name to string fixes it as well. Seems to be the same case in the sqlite bug report.

推荐答案

因为我使用的是带有 SQLite 的 Ubuntu 16.04，所以它附带的 3.11.0 版有以下错误:https://sqlite.org/src/info/ef360601

Because I was using Ubuntu 16.04 with SQLite it ships with version 3.11.0, which has the following bug: https://sqlite.org/src/info/ef360601

为了解决这个问题，我需要在创建表/插入时使用正确的类型，然后才能删除行.

To solve this, I need to use correct types when creating the table/inserting before I can delete the rows.

固定代码:

import sqlite3

class DBHelper:
    def __init__(self, dbname="db.sqlite"):
        self.dbname = dbname
        self.conn = sqlite3.connect(dbname)
        self.conn.set_trace_callback(print)

    def setup(self):
        stmt = "CREATE TABLE IF NOT EXISTS users (key text PRIMARY KEY, name text)"
        self.conn.execute(stmt)
        stmt = "INSERT INTO users (key, name) VALUES ('Acf233146328cea01fe9648acc3053fa', '125368500')"
        self.conn.execute(stmt)
        self.conn.commit()

    def delete_user(self, name, key):
        stmt = "DELETE FROM users WHERE name = (?) AND key = (?)"
        args = (name, key)
        self.conn.execute(stmt, args)
        self.conn.commit()

    def get_all(self):
        stmt = "SELECT name, key FROM users"
        return [x for x in self.conn.execute(stmt)]


def get_db():
    db = DBHelper()
    return db


name = '125368500'
key = 'Acf233146328cea01fe9648acc3053fa'

print('Delete {0} {1}'.format(name, key))
db = get_db()
db.setup()
db.delete_user(name, key)
for user_data in db.get_all():
    print('{0} {1}'.format(user_data[0], user_data[1]))

这篇关于Python3 SQLite 行不会在 Ubuntu 上删除，但会在 Windows 上删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！