Auto increment issue in SQlite3
I have a table in Sqlite3 :
CREATE TABLE `shows` (
`index` INTEGER PRIMARY KEY AUTOINCREMENT,
`show` TEXT,
`createddate` TEXT,
`Batch` INTEGER
);
I'm loading data into the table using code below :
df = pd.DataFrame(np.column_stack([shows,dates, batch]),columns=['show', 'createddate','batch'])
df.to_sql('shows', conn,if_exists='append')
The first time I ran it with the table empty it works fine.
But if I run it again I get an error :
UNIQUE constraint failed: shows.index
Any ideas how I can get the index to auto-increment for the second load?
Edit : Show, Dates, Batch are just lists of text data. You'll notice I'm not passing in the index as don't think I need to with AUTOINCREMENT on the primary key.
python-3.x pandas sqlite
asked Nov 24 '18 at 16:03
NelsonNelson
427516
add a comment |
I have a table in Sqlite3 :
CREATE TABLE `shows` (
`index` INTEGER PRIMARY KEY AUTOINCREMENT,
`show` TEXT,
`createddate` TEXT,
`Batch` INTEGER
);
I'm loading data into the table using code below :
df = pd.DataFrame(np.column_stack([shows,dates, batch]),columns=['show', 'createddate','batch'])
df.to_sql('shows', conn,if_exists='append')
The first time I ran it with the table empty it works fine.
But if I run it again I get an error :
UNIQUE constraint failed: shows.index
Any ideas how I can get the index to auto-increment for the second load?
Edit : Show, Dates, Batch are just lists of text data. You'll notice I'm not passing in the index as don't think I need to with AUTOINCREMENT on the primary key.
python-3.x pandas sqlite
asked Nov 24 '18 at 16:03
NelsonNelson
427516
What areshows,dates, andbatchvariables? Please provide a mcve.
– TrebuchetMS
Nov 24 '18 at 16:05
Not an answer, but the error seems to imply that something is trying to insertindexvalues which are already present in the table. This shouldn't ever be happening, because ideally you would not need to manually assign values to theindexcolumn.
– Tim Biegeleisen
Nov 24 '18 at 16:06
It seems on the second load the auto increment is resetting. Will dig into SQLLite a bit more.
– Nelson
Nov 25 '18 at 7:40
add a comment |
I have a table in Sqlite3 :
CREATE TABLE `shows` (
`index` INTEGER PRIMARY KEY AUTOINCREMENT,
`show` TEXT,
`createddate` TEXT,
`Batch` INTEGER
);
I'm loading data into the table using code below :
df = pd.DataFrame(np.column_stack([shows,dates, batch]),columns=['show', 'createddate','batch'])
df.to_sql('shows', conn,if_exists='append')
The first time I ran it with the table empty it works fine.
But if I run it again I get an error :
UNIQUE constraint failed: shows.index
Any ideas how I can get the index to auto-increment for the second load?
Edit : Show, Dates, Batch are just lists of text data. You'll notice I'm not passing in the index as don't think I need to with AUTOINCREMENT on the primary key.
python-3.x pandas sqlite
asked Nov 24 '18 at 16:03
NelsonNelson
427516
I have a table in Sqlite3 :
CREATE TABLE `shows` (
`index` INTEGER PRIMARY KEY AUTOINCREMENT,
`show` TEXT,
`createddate` TEXT,
`Batch` INTEGER
);
I'm loading data into the table using code below :
df = pd.DataFrame(np.column_stack([shows,dates, batch]),columns=['show', 'createddate','batch'])
df.to_sql('shows', conn,if_exists='append')
The first time I ran it with the table empty it works fine.
But if I run it again I get an error :
UNIQUE constraint failed: shows.index
Any ideas how I can get the index to auto-increment for the second load?
Edit : Show, Dates, Batch are just lists of text data. You'll notice I'm not passing in the index as don't think I need to with AUTOINCREMENT on the primary key.
python-3.x pandas sqlite
python-3.x pandas sqlite
asked Nov 24 '18 at 16:03
NelsonNelson
427516
asked Nov 24 '18 at 16:03
NelsonNelson
427516
edited Nov 25 '18 at 8:14
Nelson
asked Nov 24 '18 at 16:03
NelsonNelson
427516
asked Nov 24 '18 at 16:03
NelsonNelson
427516
asked Nov 24 '18 at 16:03
NelsonNelson
427516
427516
What areshows,dates, andbatchvariables? Please provide a mcve.
– TrebuchetMS
Nov 24 '18 at 16:05
Not an answer, but the error seems to imply that something is trying to insertindexvalues which are already present in the table. This shouldn't ever be happening, because ideally you would not need to manually assign values to theindexcolumn.
– Tim Biegeleisen
Nov 24 '18 at 16:06
It seems on the second load the auto increment is resetting. Will dig into SQLLite a bit more.
– Nelson
Nov 25 '18 at 7:40
add a comment |
What areshows,dates, andbatchvariables? Please provide a mcve.
– TrebuchetMS
Nov 24 '18 at 16:05
Not an answer, but the error seems to imply that something is trying to insertindexvalues which are already present in the table. This shouldn't ever be happening, because ideally you would not need to manually assign values to theindexcolumn.
– Tim Biegeleisen
Nov 24 '18 at 16:06
It seems on the second load the auto increment is resetting. Will dig into SQLLite a bit more.
– Nelson
Nov 25 '18 at 7:40
What are
shows, dates, and batch variables? Please provide a mcve.– TrebuchetMS
Nov 24 '18 at 16:05
What are
shows, dates, and batch variables? Please provide a mcve.– TrebuchetMS
Nov 24 '18 at 16:05
Not an answer, but the error seems to imply that something is trying to insert
index values which are already present in the table. This shouldn't ever be happening, because ideally you would not need to manually assign values to the index column.– Tim Biegeleisen
Nov 24 '18 at 16:06
Not an answer, but the error seems to imply that something is trying to insert
index values which are already present in the table. This shouldn't ever be happening, because ideally you would not need to manually assign values to the index column.– Tim Biegeleisen
Nov 24 '18 at 16:06
It seems on the second load the auto increment is resetting. Will dig into SQLLite a bit more.
– Nelson
Nov 25 '18 at 7:40
It seems on the second load the auto increment is resetting. Will dig into SQLLite a bit more.
– Nelson
Nov 25 '18 at 7:40
add a comment |
1 Answer
1
active
oldest
votes
I managed to get it working by removing the primary key. Not ideal.
Seems the problem is Pandas to_sql() - it is looking for an index field that it's using but it resets on each execution.
The problem was caused with the latest version of Pandas defaulting to creating an Index column. This was conflicting with my primary key. I just had to add : index=False as a parameter to .to_sql
answered Nov 25 '18 at 8:13
NelsonNelson
427516
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53459934%2fauto-increment-issue-in-sqlite3%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I managed to get it working by removing the primary key. Not ideal.
Seems the problem is Pandas to_sql() - it is looking for an index field that it's using but it resets on each execution.
The problem was caused with the latest version of Pandas defaulting to creating an Index column. This was conflicting with my primary key. I just had to add : index=False as a parameter to .to_sql
answered Nov 25 '18 at 8:13
NelsonNelson
427516
add a comment |
I managed to get it working by removing the primary key. Not ideal.
Seems the problem is Pandas to_sql() - it is looking for an index field that it's using but it resets on each execution.
The problem was caused with the latest version of Pandas defaulting to creating an Index column. This was conflicting with my primary key. I just had to add : index=False as a parameter to .to_sql
answered Nov 25 '18 at 8:13
NelsonNelson
427516
add a comment |
I managed to get it working by removing the primary key. Not ideal.
Seems the problem is Pandas to_sql() - it is looking for an index field that it's using but it resets on each execution.
The problem was caused with the latest version of Pandas defaulting to creating an Index column. This was conflicting with my primary key. I just had to add : index=False as a parameter to .to_sql
answered Nov 25 '18 at 8:13
NelsonNelson
427516
I managed to get it working by removing the primary key. Not ideal.
Seems the problem is Pandas to_sql() - it is looking for an index field that it's using but it resets on each execution.
The problem was caused with the latest version of Pandas defaulting to creating an Index column. This was conflicting with my primary key. I just had to add : index=False as a parameter to .to_sql
answered Nov 25 '18 at 8:13
NelsonNelson
427516
edited Nov 26 '18 at 12:50
answered Nov 25 '18 at 8:13
NelsonNelson
427516
answered Nov 25 '18 at 8:13
NelsonNelson
427516
answered Nov 25 '18 at 8:13
NelsonNelson
427516
427516
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53459934%2fauto-increment-issue-in-sqlite3%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What are
shows,dates, andbatchvariables? Please provide a mcve.– TrebuchetMS
Nov 24 '18 at 16:05
Not an answer, but the error seems to imply that something is trying to insert
indexvalues which are already present in the table. This shouldn't ever be happening, because ideally you would not need to manually assign values to theindexcolumn.– Tim Biegeleisen
Nov 24 '18 at 16:06
It seems on the second load the auto increment is resetting. Will dig into SQLLite a bit more.
– Nelson
Nov 25 '18 at 7:40