Issues with text() inside insert and PassiveDefault in general

from sqlalchemy import *
engine = create_engine('sqlite://filename=:memory:', echo=True)

ISSUE 1

issue1 = Table('issue1', engine, 
    Column('id', Integer, primary_key=True),
    Column('rnd', Integer, PassiveDefault('random()')))
issue1.create()
try:
    issue1.insert().execute()
except Exception, e:
    print e

This is not the issue with SA. Really, a silly example that I came up with when writing tests that follow. But, how do you create row made only of defaults? I can say id=1 and sqlite will ignore it and create automunber anyway, but other db engines won't

ISSUE 2: PassiveDefault

issue2 = Table('issue2', engine, 
    Column('id',  Integer, primary_key=True),
    Column('dummy', Integer),
    Column('rnd', Integer, PassiveDefault('random()')))
issue2.create()
issue2.insert().execute(dummy = 1, rnd = 123)
issue2.insert().execute(dummy = 2)
print issue2.select().execute().fetchall()

You get [1, 123), (2, 2, u'random()')]((1,)

Clearly the PassiveDefault is not suitable for this kind of work because it creates cols inside db like this (docs agree):

CREATE TABLE test(
  id INTEGER NOT NULL PRIMARY KEY,
  bu INTEGER,
  rnd INTEGER DEFAULT 'random()'
)

This is just a literal value for sqlite, and for Oracle it is a no-go. Oracle only accepts correctly typed literals and few special oracle functions like sysdate.

It seems to me that the more correct way would be to use the expression itself in the corresponding insert statement.

ISSUE 3: text

issue3 = issue2
rnd = text("random()")
print issue3.select(issue3.c.rnd == rnd).execute().fetchall()
try:
    issue3.insert().execute(dummy = 3, rnd = rnd)
except Exception, e:
    print e

text() is working fine in select, but not in insert. It produces: 'TextClause' object has no attribute 'key'

I was originally trying to do just that, insert values with some defaults without doing unnecessary roundtrips to database. It was something like this:

data = []
seq = text("myseq.nextval") #, key="myseq")
for i,datum in enumerate(datums):
    data.append(dict(id=seq, parent_id=pid, position=i+1, datum=datum))
MyTable.table.insert().execute(*data)

I don't even need those Id's afterwards, I'm just dumping data to db and later selecting it via parent_id.

I am however able to define table like this:

test = Table('test', engine, 
    Column('id',  Integer, primary_key=True),
    Column('dummy', Integer),
    Column('rnd', Integer, default=func.random()))
test.create()
test.insert().execute(dummy = 1)

Naturally, it is first selecting random(), and then using it for insert. Fine, and I use it, but it would be nice to save some roundtrips.

Comments (1)