sqlite3 simple query in python hangs

Question

i'm trying to make a simple query in python and sqlite3:

#!/usr/bin/env python
# -*- coding: utf-8; -*-

import sqlite3

db = sqlite3.connect('test.db')
query = """CREATE TABLE `home` (
   `id` int(11) not null auto_increment,
   `full_name` char(255) not null,
   `display_name` char(255),
   `ip_address` char(255) not null,
   `user`  char(255) not null,
   PRIMARY KEY (`id`)
);"""
db.execute(query)
db.commit()
db.close()

But when i run the script, nothing happens; i mean: a file called test.db is created in the directory, but after that the shell remain there without return anything (even the prompt) and i need to kill the script with kill -9

Any help?

I get a sqlite3.OperationalError: near "auto_increment": syntax error exception instead. SQLite has AUTOINCREMENT without an underscore. Even with that corrected, there are more syntax errors still, because AUTOINCREMENT can only be used with a PRIMARY KEY column. — Martijn Pieters
– Martijn Pieters, Commented Jan 14, 2014 at 18:59
@JoelCornett: SQLite3 is pretty tolerant of other types, actually. int works just fine, but you probably don't want to use it if you can help it. — Martijn Pieters
– Martijn Pieters, Commented Jan 14, 2014 at 19:12
@JoelCornett: See the type affinity documentation; char has TEXT affinity, int is mostly aligned with INTEGER. — Martijn Pieters
– Martijn Pieters, Commented Jan 14, 2014 at 19:15

Martijn Pieters · Accepted Answer · 2014-01-14 19:12:57Z

2

I don't know why your script apparently hangs, but there are SQL syntax errors in your query:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "auto_increment": syntax error

SQLite only have a AUTOINCREMENT keyword, and that only is supported after the PRIMARY KEY constraint; you'll have to move your PRIMARY KEY line from the bottom to the id column. You'll have to use the proper INTEGER type as well, instead of int.

SQLite also ignores column sizes, you may as well drop those.

The following query works:

query = """CREATE TABLE `home` (
   `id` integer primary key autoincrement,
   `full_name` char(255) not null,
   `display_name` char(255),
   `ip_address` char(255) not null,
   `user`  char(255) not null
);"""

where I left in the column sizes for the char columns, but you may as well make those TEXT (without a size) and be done with it.

edited Jan 14, 2014 at 19:12

answered Jan 14, 2014 at 19:06

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

maurelio79 Over a year ago

Thanks man! Now i know because script keep hanging: if a create it in a NFS share, it hangs! Why? No idea. The only thing i know is that on NFS share it create a regluar file and not a db file. :-O

Martijn Pieters Over a year ago

SQLite requires proper locking to work, something NFS doesn't always support.

Collectives™ on Stack Overflow

sqlite3 simple query in python hangs

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related