sqlite主键创建选项卡

Question

import sqlite3

# set database variables
sqlite_db_filename = '/samba/anonymous/vacuum_control.sqlite'
table_name = 'user_recipe'
new_field_1 = 'recipe_id'
field_type_1 = 'INTEGER PRIMARY KEY'
new_field_2 = 'recipe_name'
field_type_2 = 'TEXT'
new_field_3 = 'use_vacuum'
field_type_3 = 'INTEGER'


# Connecting to the database file
conn = sqlite3.connect(sqlite_db_filename)
c = conn.cursor()

# Creating a new SQLite table with x columns

c.execute('CREATE TABLE {tn} ({nf1} {ft1} {nf2} {ft2})'\
    .format(tn=table_name, nf1=new_field_1, ft1=field_type_1, nf2=new_field_2, ft2=field_type_2))

# Committing changes and closing the connection to the database file
conn.commit()

问题

当前代码产生错误

"Traceback (most recent call last):
  File "data_base_config.py", line 20, in <module>
    .format(tn=table_name, nf1=new_field_1, ft1=field_type_1, nf2=new_field_2, ft2=field_type_2))
sqlite3.OperationalError: near "recipe_name": syntax error"

如果我把field_type_1改成"INTEGER"，效果就很好了。我看了很多其他的例子，其中包含INTEGER PRIMARY KEY似乎是有效的

SQLite版本3.8.7.1

谢谢

Answer 1

{ft1}后面缺少逗号：

c.execute('CREATE TABLE {tn} ({nf1} {ft1}, {nf2} {ft2})'\

Answer 2

我不确定，如果您只想设置列的on作为主键，那么我建议使用这种语法。你知道吗

    # Creating a second table with 1 column and set it as PRIMARY KEY
    # note that PRIMARY KEY column must consist of unique values!
    c.execute('CREATE TABLE {tn} ({nf} {ft} PRIMARY KEY)'\
            .format(tn=table_name2, nf=new_field, ft=field_type))

您可以看到这个例子here