DB insert with no values doesn't work with mysql #49

Closed
anandology opened this Issue Mar 14, 2011 · 6 comments

Comments

Projects
None yet
3 participants
Contributor

anandology commented Mar 14, 2011

DB insert with no values doesn't work with mysql, even though it works with sqlite.

>>> db.insert("mytable")
ERR: INSERT INTO mytable DEFAULT VALUES

The correct syntax for MySQL is:

INSERT INTO mytable () VALUES();

(Originally reported by Ole Trenner in the mailing list.)

Found a workaround when there is an auto-incrementing column in the table. Instead of just insert('table') you do:

my_id = db.insert('table', id=web.db.SQLLiteral('NULL'))

This works in MySQL and SQLite.

Contributor

aaronsw commented Apr 11, 2011

What SQL does that result in?

In [8]: db.insert('table', id=web.db.SQLLiteral('NULL'), _test=True)
Out[8]: <sql: 'INSERT INTO table (id) VALUES (NULL)'>

Obviously this will only work if the id column has been set to something like id integer primary key auto_increment (mysql) or id integer primary key (sqlite).

Contributor

aaronsw commented Apr 12, 2011

Yeah, don't think we want to count on that. I think just crashing is safer if the SQL engine doesn't support default values.

@aaronsw aaronsw closed this Apr 12, 2011

Uhm. MySQL does support default values, it just uses a different syntax:
INSERT INTO tbl_name () VALUES();

I was just searching for a solution that would work for both Sqlite and MySQL. Maybe the MySQL specific subclass in web.py could honor the MySQL syntax.

@aaronsw aaronsw reopened this Apr 12, 2011

@ghost ghost assigned anandology Apr 12, 2011

@anandology anandology closed this in b1417f5 May 2, 2011

gardiner commented May 2, 2011

Thanks :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment