python的sqlite问题中的AUTO_INCREMENT [英] AUTO_INCREMENT in sqlite problem with python

查看:31
本文介绍了python的sqlite问题中的AUTO_INCREMENT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在 python 2.5 中使用 sqlite.我收到以下语法的 sqlite 错误.我环顾四周,在此页面上看到了 AUTOINCREMENT http://www.sqlite.org/syntaxdiagrams.html#column-constraint 但这也不起作用.没有 AUTO_INCREMENT 我可以创建表.

I am using sqlite with python 2.5. I get a sqlite error with the syntax below. I looked around and saw AUTOINCREMENT on this page http://www.sqlite.org/syntaxdiagrams.html#column-constraint but that did not work either. Without AUTO_INCREMENT my table can be created.

An error occurred: near "AUTO_INCREMENT": syntax error 
CREATE TABLE fileInfo
(
fileId int NOT NULL AUTO_INCREMENT,
name varchar(255),
status int NOT NULL,
PRIMARY KEY (fileId)
);

推荐答案

SQLite FAQ.问题 #1.

说明:

如何创建自动增量领域?

How do I create an AUTOINCREMENT field?

简答:声明了一个列整数主键将自增.

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.

这是一个很长的答案:如果你将表的一列声明为整数主键,那么无论何时你将 NULL 插入到表,NULL 是自动的转换为一个整数大于那个的最大值列在所有其他行之上表,如果表为空,则为 1.(如果最大可能的整数键,9223372036854775807,然后是一个未使用的键值是随机选择的.)对于例如,假设你有一张像这个:

Here is the long answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. (If the largest possible integer key, 9223372036854775807, then an unused key value is chosen at random.) For example, suppose you have a table like this:

创建表 t1(一个整数主KEY, b INTEGER ); 有了这个表,声明

INSERT INTO t1 VALUES(NULL,123); 是逻辑上相当于说:

INSERT INTO t1 VALUES((SELECT max(a)FROM t1)+1,123); 有个函数名为 sqlite3_last_insert_rowid()这将返回整数键最近的插入操作.

INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123); There is a function named sqlite3_last_insert_rowid() which will return the integer key for the most recent insert operation.

注意整数键是1大于最大的键在插入之前的表中.新密钥将是独一无二的键当前在表中,但它可能与已使用的键重叠之前从表中删除.至创建唯一的键表的生命周期,添加AUTOINCREMENT 关键字到 INTEGER主键声明.然后关键选择的将比有史以来最大的钥匙那张桌子.如果最大可能密钥以前存在于那个表,然后 INSERT 将失败SQLITE_FULL 错误代码.

Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table. To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration. Then the key chosen will be one more than than the largest key that has ever existed in that table. If the largest possible key has previously existed in that table, then the INSERT will fail with an SQLITE_FULL error code.

这篇关于python的sqlite问题中的AUTO_INCREMENT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆