<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import *
engine = create_engine('sqlite://filename=:memory:', echo=True)"><pre class="notranslate"><code class="notranslate">from sqlalchemy import *
engine = create_engine('sqlite://filename=:memory:', echo=True)
</code></pre></div>
<p dir="auto"><strong>ISSUE 1</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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"><pre class="notranslate"><code class="notranslate">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
</code></pre></div>
<p dir="auto">This is not the issue with SA.<br>
Really, a silly example that I came up with when writing tests that follow.<br>
But, how do you create row made only of defaults?<br>
I can say id=1 and sqlite will ignore it and create<br>
automunber anyway, but other db engines won't</p>
<p dir="auto"><strong>ISSUE 2: PassiveDefault</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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()"><pre class="notranslate"><code class="notranslate">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()
</code></pre></div>
<p dir="auto">You get [1, 123), (2, 2, u'random()')]((1,)</p>
<p dir="auto">Clearly the PassiveDefault is not suitable for this kind of<br>
work because it creates cols inside db like this (docs agree):</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="CREATE TABLE test(
  id INTEGER NOT NULL PRIMARY KEY,
  bu INTEGER,
  rnd INTEGER DEFAULT 'random()'
)"><pre class="notranslate"><code class="notranslate">CREATE TABLE test(
  id INTEGER NOT NULL PRIMARY KEY,
  bu INTEGER,
  rnd INTEGER DEFAULT 'random()'
)
</code></pre></div>
<p dir="auto">This is just a literal value for sqlite, and for Oracle<br>
it is a no-go. Oracle only accepts correctly typed literals<br>
and few special oracle functions like sysdate.</p>
<p dir="auto">It seems to me that the more correct way would be to use the<br>
expression itself in the corresponding insert statement.</p>
<p dir="auto"><strong>ISSUE 3: text</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="issue3 = issue2
rnd = text(&quot;random()&quot;)
print issue3.select(issue3.c.rnd == rnd).execute().fetchall()
try:
    issue3.insert().execute(dummy = 3, rnd = rnd)
except Exception, e:
    print e"><pre class="notranslate"><code class="notranslate">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
</code></pre></div>
<p dir="auto">text() is working fine in select, but not in insert.<br>
It produces: 'TextClause' object has no attribute 'key'</p>
<p dir="auto">I was originally trying to do just that, insert values with<br>
some defaults without doing unnecessary roundtrips to database.<br>
It was something like this:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="data = []
seq = text(&quot;myseq.nextval&quot;) #, key=&quot;myseq&quot;)
for i,datum in enumerate(datums):
    data.append(dict(id=seq, parent_id=pid, position=i+1, datum=datum))
MyTable.table.insert().execute(*data)"><pre class="notranslate"><code class="notranslate">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)
</code></pre></div>
<p dir="auto">I don't even need those Id's afterwards, I'm just dumping data to db<br>
and later selecting it via parent_id.</p>
<p dir="auto">I am however able to define table like this:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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)"><pre class="notranslate"><code class="notranslate">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)
</code></pre></div>
<p dir="auto">Naturally, it is first selecting random(), and then using it for insert.<br>
Fine, and I use it, but it would be nice to save some roundtrips.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/119/demoinserttext.py">demoinserttext.py</a></p>
<p dir="auto"><strong>Migrated issue, originally created by jek (<a href="https://github.com/jek">@jek</a>)</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="(ProgrammingError) invalid reference to FROM-clause entry for table &quot;principals&quot; at character 737
HINT:  There is an entry for table &quot;principals&quot;, 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()"><pre class="notranslate"><code class="notranslate">(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()
</code></pre></div>