Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/env python
- # Copyright (c) 2012, Tim Henderson
- # All rights reserved.
- #
- # Redistribution and use in source and binary forms, with or without
- # modification, are permitted provided that the following conditions are
- # met:
- #
- # - Redistributions of source code must retain the above copyright notice,
- # this list of conditions and the following disclaimer.
- # - Redistributions in binary form must reproduce the above copyright
- # notice, this list of conditions and the following disclaimer in the
- # documentation and/or other materials provided with the distribution.
- # - Neither the name of this software nor the names of its contributors
- # may be used to endorse or promote products derived from this software
- # without specific prior written permission.
- #
- # THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
- # IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
- # TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
- # PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
- # HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
- # SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
- # TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
- # PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
- # LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
- # NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
- # SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
- """
- This module provides an "Upsert" statement for SQL Alchemy which uses
- ON DUPLICATE KEY UPDATE to implement the insert or update semantics. It
- supports doing a bulk insert.
- """
- import sqlalchemy as sa
- from sqlalchemy.ext.compiler import compiles
- class Upsert(expr.Insert): pass
- @compiles(Upsert, "mysql")
- def compile_upsert(insert_stmt, compiler, **kwargs):
- if insert_stmt._has_multi_parameters:
- keys = insert_stmt.parameters[0].keys()
- else:
- keys = insert_stmt.parameters.keys()
- pk = insert_stmt.table.primary_key
- auto = None
- if (len(pk.columns) == 1 and
- isinstance(pk.columns.values()[0].type, sa.Integer) and
- pk.columns.values()[0].autoincrement):
- auto = pk.columns.keys()[0]
- if auto in keys:
- keys.remove(auto)
- insert = compiler.visit_insert(insert_stmt, **kwargs)
- ondup = 'ON DUPLICATE KEY UPDATE'
- updates = ', '.join(
- '%s = VALUES(%s)' % (c.name, c.name)
- for c in insert_stmt.table.columns
- if c.name in keys
- )
- if auto is not None:
- last_id = '%s = LAST_INSERT_ID(%s)' % (auto, auto)
- if updates:
- updates = ', '.join((last_id, updates))
- else:
- updates = last_id
- upsert = ' '.join((insert, ondup, updates))
- return upsert
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement