Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

bulk_insert produces invalid SQL if rows is empty #168

Closed
sqlalchemy-bot opened this issue Jan 23, 2014 · 4 comments
Closed

bulk_insert produces invalid SQL if rows is empty #168

sqlalchemy-bot opened this issue Jan 23, 2014 · 4 comments

Comments

@sqlalchemy-bot
Copy link

@sqlalchemy-bot sqlalchemy-bot commented Jan 23, 2014

Migrated issue, originally created by Michael Howitz (@icemac)

If rows is empty bulk_insert should do nothing but using a Postgres database I get broken SQL, see the example:

users_svgs = sa.sql.table(
    'users_svgs',
    sa.sql.column(u'user_id', sa.Integer()),
    sa.sql.column(u'svg_id', sa.String(10)))

op.bulk_insert(users_svgs, [])

Traceback (most recent call last):
...
'INSERT INTO users_svgs DEFAULT VALUES' {}
@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jan 23, 2014

Michael Bayer (@zzzeek) wrote:

that SQL isn't broken btw, that is exactly how to render an INSERT with no parameters:

psql (9.3.2)
Type "help" for help.

test=> CREATE TABLE my_table (id SERIAL PRIMARY KEY);
CREATE TABLE
test=> INSERT INTO my_table DEFAULT VALUES;
INSERT 0 1
test=> SELECT * FROM my_table;
 id 
----
  1
(1 row)

your code example above isn't clear in that you suggest there's a stack trace but you aren't illustrating what the error message is.

Sending an empty collection to bulk_insert() IMO should just raise an error, it is ambiguous what one's intent would be in sending such a list. I don't know that I'm comfortable with an op.XYZ() directive deciding not to emit any SQL based on parameters.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jan 24, 2014

Michael Howitz (@icemac) wrote:

Sorry for being not complete. My use case is a bit more complex than described.

users_svgs is an n-m relation table, so the columns are foreign keys and part of the primary key. In the migration I switch from a single svg_id on the user table to the new users_svgs n-m table.

The actual error message is:

sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column "user_id" violates not-null constraint

The empty list shown for insert is actually the result of a select on users. It might return no results. I'd prefer bulk_insert to do nothing on an empty rows list so it does not break in this edge case.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jan 24, 2014

Michael Bayer (@zzzeek) wrote:

--sql mode already did this, so this is in dccc558.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jan 24, 2014

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant
You can’t perform that action at this time.