Issue 1:
Migrated issue, originally created by Anonymous

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.

Attachments: demoinserttext.py

Issue 2:
Migrated issue, originally created by jek (@jek)

(ProgrammingError) invalid reference to FROM-clause entry for table "principals" at character 737
HINT: There is an entry for table "principals", but it cannot be referenced from this part of the query.

'SELECT anon_1.tickets_owner_id AS anon_1_tickets_owner_id, anon_1.tickets_id AS anon_1_tickets_id, anon_1.tickets_subject AS anon_1_tickets_subject, anon_1.principals_id AS anon_1_principals_id, anon_1.users_id AS anon_1_users_id, anon_1.principals_principal_type AS anon_1_principals_principal_type, anon_1.users_name AS anon_1_users_name,
principals_1.id AS principals_1_id, principals_1.principal_type AS principals_1_principal_type
FROM (SELECT tickets.owner_id AS tickets_owner_id, tickets.id AS tickets_id, tickets.subject AS tickets_subject, principals.id AS principals_id, users.id AS users_id, principals.principal_type AS principals_principal_type, users.name AS users_name
FROM principals, tickets LEFT OUTER JOIN users ON principals.id = users.id
LIMIT 1 OFFSET 0) AS anon_1
LEFT OUTER JOIN principals AS principals_1 ON anon_1.tickets_owner_id = principals_1.id'

#!python
from sqlalchemy import *
from sqlalchemy.orm import *

metadata = MetaData()
Table('principals', metadata,
Column('id', Integer, primary_key=True),
Column('principal_type', String(16), nullable=False))

Table('users', metadata,
Column('id', Integer, ForeignKey('principals.id'), primary_key=True),
Column('name', String(200), nullable=False))

Table('groups', metadata,
Column('id', Integer, ForeignKey('principals.id'), primary_key=True),
Column('name', String(200), nullable=False),
Column('description', String(255)))

Table('tickets', metadata,
Column('id', Integer, primary_key=True),
Column('owner_id', Integer, ForeignKey('principals.id'), nullable=False),
Column('subject', String(200), default='[subject](no)'))

for name, value in metadata.tables.items():
locals()[name](name) = value

class Principal(object):
pass

class Group(Principal):
pass

class User(Principal):
pass

class Ticket(object):
pass

mapper(Principal, principals,
polymorphic_on=principals.c.principal_type)

mapper(User, users,
inherits=Principal,
polymorphic_identity='User')

mapper(Group, groups,
inherits=Principal,
polymorphic_identity='Group')

mapper(Ticket, tickets, properties={
'owner': relation(Principal, backref='tickets',
primaryjoin=(tickets.c.owner_id ==
principals.c.id))
})

def data():
principals.insert().execute(
{'id': 1, 'principal_type': 'User'},
{'id': 2, 'principal_type': 'Group'},
{'id': 3, 'principal_type': 'Group'},
{'id': 4, 'principal_type': 'User'})
users.insert().execute(
{'id': 1, 'name': 'user @ 1'},
{'id': 4, 'name': 'user @ 4'})
groups.insert().execute(
{'id': 2, 'name': 'group @ 2'},
{'id': 3, 'name': 'group @ 3'})
tickets.insert().execute(
{'id': 1, 'owner_id': 1, 'subject': 'Tix @ 1'},
{'id': 2, 'owner_id': 2, 'subject': 'Tix @ 2'},
{'id': 3, 'owner_id': 1, 'subject': 'Tix @ 3'},
{'id': 4, 'owner_id': 1, 'subject': 'Tix @ 4'})

def test():
s = create_session()
s.query(Ticket).options(eagerload(Ticket.owner)).with_polymorphic(User).first()

if __name__ == '__main__':
# passes on sqlite
metadata.bind = 'postgres:///test'
metadata.create_all()
try:
data()
test()
finally:
metadata.drop_all()