sorin
sorin

Reputation: 170330

How to specify the primary id when inserting rows with sqlalchemy when id dos not have autoincrement?

I do have database table that has an id primary key that is not an auto-increment (sequence). So it's up to the user to create an unique id or the insert will fail.

This table is not under my control, so I cannot change the database structure.

from sqlalchemy import create_engine, Table, MetaData
import psycopg2
db = create_engine('postgresql://...', echo=False).connect()
meta = MetaData()
meta.reflect(bind=db)
t = Table("mytable", meta, autoload=True, autoload_with=db)
values = { "title":"title", "id": ... }#  ???
t.insert(bind=db, values=values).execute()

Upvotes: 1

Views: 1251

Answers (1)

van
van

Reputation: 76952

Given this is "single-user" / "single-client" system, you should be able to use the Column defaults: Python-Executed Functions. The example on the documentation linked to is enough to get you started. I would, however, use python function but with proper initialization from the datatabase adn still stored in a global variable:

def new_id_factory():
    if not('_MYTABLE_ID_' in globals()):
        q = db.execute("select max(mytable.id) as max_id from mytable").fetchone()
        _MYTABLE_ID_ = (q and q.max_id) or 0
    _MYTABLE_ID_ += 1
    return _MYTABLE_ID_


t = Table("mytable", Base.metadata,
    Column('id', Integer, primary_key=True, default=new_id_factory), #
    autoload=True, autoload_with=db,
    )

Upvotes: 1

Related Questions