如何使用 Python 在 SQLite 中正确设置列的 AUTO INCREMENT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26652393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to correctly set AUTO INCREMENT fo a column in SQLite, using Python?
提问by Avi
I have been trying with the below code:
我一直在尝试使用以下代码：
import sqlite3
data_person_name = [('Michael', 'Fox'),
('Adam', 'Miller'),
('Andrew', 'Peck'),
('James', 'Shroyer'),
('Eric', 'Burger')]
con = sqlite3.connect(":memory:")
c = con.cursor()
c.execute('''CREATE TABLE q1_person_name
(name_id integer auto_increment primary key,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL)''')
c.executemany('INSERT INTO q1_person_name VALUES (?,?,?)', data_person_name)
for row in c.execute('SELECT * FROM q1_person_name'):
print row
Can somebody help me in making the name_idautomatically incremented ?
有人可以帮我name_id自动增加吗？
采纳答案by Jan Bodnar
In SQLite, INTEGER PRIMARY KEYcolumn is auto-incremented. There is also an AUTOINCREMENTkeyword. When used in INTEGER PRIMARY KEY AUTOINCREMENT, a
slightly different algorithm for Id creation is used.
在 SQLite 中，INTEGER PRIMARY KEY列是自动递增的。还有一个AUTOINCREMENT关键词。在 中使用时INTEGER PRIMARY KEY AUTOINCREMENT，会使用稍微不同的 Id 创建算法。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import sqlite3
data_person_name = [('Michael', 'Fox'),
('Adam', 'Miller'),
('Andrew', 'Peck'),
('James', 'Shroyer'),
('Eric', 'Burger')]
con = sqlite3.connect(":memory:")
c = con.cursor()
c.execute('''CREATE TABLE q1_person_name
(name_id INTEGER PRIMARY KEY,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL)''')
c.executemany('INSERT INTO q1_person_name(first_name, last_name) VALUES (?,?)', data_person_name)
for row in c.execute('SELECT * FROM q1_person_name'):
print row
This code now works OK.
此代码现在可以正常工作。
c.executemany('INSERT INTO q1_person_name(first_name, last_name) VALUES (?,?)', data_person_name)
When using auto-increment, we have to explicitly state the column names, omitting the one that is auto-incremented.
使用自动递增时，我们必须明确说明列名，省略自动递增的列名。
$ ./test.py
(1, u'Michael', u'Fox')
(2, u'Adam', u'Miller')
(3, u'Andrew', u'Peck')
(4, u'James', u'Shroyer')
(5, u'Eric', u'Burger')
This is the output of the code example.
这是代码示例的输出。
回答by Naveed
Try like this:
像这样尝试：
c.execute('''CREATE TABLE q1_person_name
(name_id integer primary key AUTOINCREMENT,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL)''')
回答by Daniel Roseman
It seems that you've done it already. So there's no need to reference that field when you insert.
看来你已经做到了。因此，插入时无需引用该字段。
INSERT INTO q1_person_name (first_name, last_name) VALUES (?,?)
回答by Animesh Jaipurkar
Replace the first ?in executemanystatement with null.
用 替换第一个?inexecutemany语句null。
So the following line can be rewritten:
因此可以重写以下行：
c.executemany('INSERT INTO q1_person_name VALUES (?,?,?)', data_person_name)
as
作为
c.executemany('INSERT INTO q1_person_name VALUES (null,?,?)', data_person_name)
