integrity error not raised for null column
Jonathon Anderson
---
from sqlalchemy.orm import scoped_session, sessionmaker, relation
from sqlalchemy import exceptions, create_engine, MetaData, Table,
Column, types
metadata = MetaData()
users_table = Table("users", metadata,
Column("id", types.Integer, primary_key=True),
Column("host", types.String(16), nullable=False),
Column("name", types.String(16), nullable=False),
)
class User (object):
def __init__ (self, **kwargs):
self.id = kwargs.get("id")
self.name = kwargs.get("name")
self.host = kwargs.get("host")
Session = scoped_session(sessionmaker(transactional=True,
autoflush=True))
Session.mapper(User, users_table, properties=dict(
id = users_table.c.id,
name = users_table.c.name,
host = users_table.c.host,
))
def test_host ():
metadata.bind = create_engine("sqlite:///:memory:")
metadata.create_all()
user = User(name="monty", host="localhost")
Session.commit()
metadata.drop_all()
metadata.create_all()
user = User(name="python")
assert user.host is None
try:
Session.commit()
except exceptions.IntegrityError:
pass
else:
assert False
Session.close()
metadata.drop_all()
Michael Bayer
theres no SQLAlchemy ;). So I think you should submit this to the bug
tracker on www.sqlite.org.
Actually this is sorta interesting since it would impact our own unit
tests regarding sqlite as well (which is why we run them with mysql
and postgres as part of our build as well).
import sqlite3
def test(createdrop=True):
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute("CREATE TABLE users(id INTEGER PRIMARY KEY, host
VARCHAR(16) NOT NULL, name VARCHAR(16) NOT NULL)")
cursor.execute("INSERT INTO users (host, name) VALUES (?, ?)",
["localhost", "monty"])
cursor.close()
cursor = conn.cursor()
if createdrop:
cursor.execute("DROP TABLE users")
cursor.execute("CREATE TABLE users(id INTEGER PRIMARY KEY,
host VARCHAR(16) NOT NULL, name VARCHAR(16) NOT NULL)")
try:
cursor.execute("INSERT INTO users (host, name) VALUES
(?, ?)", [None, "python"])
assert False, repr(cursor.execute("select * from
users").fetchall())
except sqlite3.IntegrityError:
assert True
test(True)
Michael Schlenker
> no idea. below is a revised version, where the main revision is that
> theres no SQLAlchemy ;). So I think you should submit this to the bug
> tracker on www.sqlite.org.
>
> Actually this is sorta interesting since it would impact our own unit
> tests regarding sqlite as well (which is why we run them with mysql
> and postgres as part of our build as well).
TABLE manpage for sqlite.
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax: +49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: m...@contact.de
Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
Jonathon Anderson
between every test, and I had a test failing when it was the second
test that was run.
I'll submit this to the sqlite guys, and try upgrading my sqlite
module in the mean time. (I'm running the one that comes with Python
2.5.1.)
~jon
Jonathon Anderson
The problem doesn't seem to occur on sqlite3 proper. Rather, it seems
to only occur when using the python module.
janderson@Kay:~$ sqlite3 testdb.sqlite
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> CREATE TABLE users(id INTEGER PRIMARY KEY, host VARCHAR(16)
NOT NULL, name VARCHAR(16) NOT NULL);
sqlite> INSERT INTO users (host, name) VALUES ("localhost", "monty");
sqlite> DROP TABLE users;
sqlite> CREATE TABLE users(id INTEGER PRIMARY KEY, host VARCHAR(16)
NOT NULL, name VARCHAR(16) NOT NULL);
sqlite> INSERT INTO users (host, name) VALUES (null, "python");
SQL error: users.host may not be NULL
sqlite>
On Feb 15, 10:14 am, Michael Schlenker <m...@contact.de> wrote:
> Michael Bayer schrieb:> no idea. below is a revised version, where the main revision is that
> > theres no SQLAlchemy ;). So I think you should submit this to the bug
> > tracker onwww.sqlite.org.
>
> > Actually this is sorta interesting since it would impact our own unit
> > tests regarding sqlite as well (which is why we run them with mysql
> > and postgres as part of our build as well).
>
> This is a known misfeature of sqlite..., its even documented in the CREATE
> TABLE manpage for sqlite.
>
> Michael
>
> --
> Michael Schlenker
> Software Engineer
>
> CONTACT Software GmbH Tel.: +49 (421) 20153-80
> Wiener Straße 1-3 Fax: +49 (421) 20153-41
Michael Bayer
On Feb 15, 2008, at 11:14 AM, Michael Schlenker wrote:
>
> Michael Bayer schrieb:
>> no idea. below is a revised version, where the main revision is that
>> theres no SQLAlchemy ;). So I think you should submit this to the
>> bug
>> tracker on www.sqlite.org.
>>
>> Actually this is sorta interesting since it would impact our own unit
>> tests regarding sqlite as well (which is why we run them with mysql
>> and postgres as part of our build as well).
> This is a known misfeature of sqlite..., its even documented in the
> CREATE
> TABLE manpage for sqlite.
I wouldn't be so sure about that. Not sure what you mean by
"manpage" (since man sqlite3 just produces a single brief usage page),
but the docs on the site at http://www.sqlite.org/
lang_createtable.html don't mention anything about "NOT NULL" failing;
only that PRIMARY KEY does not imply NOT NULL, which is not all whats
going on here. This issue is specifically, "NOT NULL will fail to
issue an exception when used with a CREATE TABLE statement following a
DROP of that same table, which had to have at least one row before it
was dropped". All of those conditions are needed to reproduce the bug
(but note, the column in question is not a PRIMARY KEY column). The
bug is also not a failure of the constraint; sqlite3 does not allow
the NULL value to go in and no row is inserted. Its just that the
error is not propagated the second time around. So this is definitely
just a bug, and may even be within pysqlite as opposed to sqlite itself.
Michael Schlenker
> What, exactly, is a "misfeature" and where is this documentation?
>
> The problem doesn't seem to occur on sqlite3 proper. Rather, it seems
> to only occur when using the python module.
>
> janderson@Kay:~$ sqlite3 testdb.sqlite
> SQLite version 3.5.4
> Enter ".help" for instructions
> sqlite> CREATE TABLE users(id INTEGER PRIMARY KEY, host VARCHAR(16)
> NOT NULL, name VARCHAR(16) NOT NULL);
> sqlite> INSERT INTO users (host, name) VALUES ("localhost", "monty");
> sqlite> DROP TABLE users;
> sqlite> CREATE TABLE users(id INTEGER PRIMARY KEY, host VARCHAR(16)
> NOT NULL, name VARCHAR(16) NOT NULL);
> sqlite> INSERT INTO users (host, name) VALUES (null, "python");
> SQL error: users.host may not be NULL
> sqlite>
>
I was refering to the NULL in primary key columns feature of SQLite as
documented in:
http://sqlite.org/lang_createtable.html
"According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not the case
in SQLite. SQLite allows NULL values in a PRIMARY KEY column. We could
change SQLite to conform to the standard (and we might do so in the future),
but by the time the oversight was discovered, SQLite was in such wide use
that we feared breaking legacy code if we fixed the problem. So for now we
have chosen to contain allowing NULLs in PRIMARY KEY columns. Developers
should be aware, however, that we may change SQLite to conform to the SQL
standard in future and should design new programs accordingly."
So basically i missed the point as you don't insert into the PK column.
Jonathon Anderson
Your example (and I suppose sqlalchemy) use an sqlite3 module, which
seems to only exist within the python source tree. upgrading pysqlite
from initd.org gives me a pysqlite2 module. (Using the
pysqlite2.dbapi2 module does not seem to carry the same error.)
So the bug seems present only in the python 2.5 sqlite3 module. What
next?
~jon
> > theres no SQLAlchemy ;). So I think you should submit this to the bug
> > tracker onwww.sqlite.org.
>
> > Actually this is sorta interesting since it would impact our own unit
> > tests regarding sqlite as well (which is why we run them with mysql
> > and postgres as part of our build as well).
>
> This is a known misfeature of sqlite..., its even documented in the CREATE
> TABLE manpage for sqlite.
>
> Michael
>
> --
> Michael Schlenker
> Software Engineer
>
> CONTACT Software GmbH Tel.: +49 (421) 20153-80
> Wiener Straße 1-3 Fax: +49 (421) 20153-41
Jonathon Anderson
(the second being a stdlib snapshot of the first) and have found the
code in sqlalchemy that will use pysqlite2, if present, over sqlite3,
so having installed the latest version of pysqlite2, I should be fine.
Any idea why the library name changed? I think that's confusing me
more than anything.
I assume the stdlib sqlite module will be upgraded at Python 2.6?
~jon
Michael Bayer
On Feb 15, 2008, at 3:23 PM, Jonathon Anderson wrote:
>
> I think I understand the relationship between pysqlite2 and sqlite3
> (the second being a stdlib snapshot of the first) and have found the
> code in sqlalchemy that will use pysqlite2, if present, over sqlite3,
> so having installed the latest version of pysqlite2, I should be fine.
>
> Any idea why the library name changed? I think that's confusing me
> more than anything.
>
probably so you can have a separate pysqlite2 installed without
conflicts over the built-in sqlite3.
> I assume the stdlib sqlite module will be upgraded at Python 2.6?
hopefully. apparently theres unit tests in python 2.5 which are
hardwired against an older version of sqlite (fail with newer versions).