python - 限制输入到SQLite3的字符数

Question

我正在尝试创建一个包含以下字段的 SQL 数据库:

connection= sqlite3.connect('Main Database')
crsr = connection.cursor()
#Creates a table for the teacher data if no table is found on the system
crsr.execute("""CREATE TABLE IF NOT EXISTS Teacher_Table(Teacher_ID INTEGER PRIMARY KEY,
                    TFirst_Name VARCHAR(25) NOT NULL,
                    TLast_Name VARCHAR (25) NOT NULL,
                    Gender CHAR(1) NOT NULL,
                    Home_Address VARCHAR (50) NOT NULL,
                    Contact_Number VARCHAR (14) NOT NULL);""")
connection.commit()
connection.close()

但是当我输入值时，性别字段接受多个值 Database View

如何确保该字段只接受一个字符

Answer 1

How can I make sure it only accepts one character for that field

SQLite 不检查类型级别定义的长度约束，如 documentation on types 中指定的那样。 :

(...) Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

因此您无法在数据库级别强制这一点。因此，您需要通过您的观点等来强制执行这一点。

但是我们可以，例如 @Ilja Everilä说，使用 CHECK约束:

CREATE TABLE IF NOT EXISTS Teacher_Table(
    Teacher_ID INTEGER PRIMARY KEY,
    TFirst_Name VARCHAR(25) NOT NULL,
    TLast_Name VARCHAR (25) NOT NULL,
    Gender CHAR(1) NOT NULL <b>CHECK (length(Gender) < 2)</b>,
    Home_Address VARCHAR (50) NOT NULL,
    Contact_Number VARCHAR (14) NOT NULL
)