Re: [sqlalchemy] How to use IBM i Access ODBC Driver

[Jack W.]

Mike --

I found *sqlalchemy-ibmi <https://github.com/IBM/sqlalchemy-ibmi> *which 
connects but doesn't work all the way because it's aimed at SQLAlchemy 
1.3.x.
I'm trying Apache Superset which wants SQLAlchemy>=1.4.49.
I've filed an issue Apache Superset requires SQLAlchemy 1.4.x 
<https://github.com/IBM/sqlalchemy-ibmi/issues/147> with IBM to see if we 
can get an update.
Noting this all for the next IBM i person running up against this problem!
Thanks again, Mike, for your help.

On Wednesday, October 11, 2023 at 10:08:56 AM UTC-6 Jack W. wrote:

> Thanks for the prompt response, Michael ...
>
> On Wednesday, October 11, 2023 at 7:01:47 AM UTC-6 Mike Bayer wrote:
>
> SQLAlchemy does not include support for this driver - the only IBM driver 
> is the ibm_db_sa you reference, so short of writing your own driver based 
> on that one, that's what's available.
>
>
> I infer there's a plugin regimen in the SQLAlchemy source to connect with 
> supported drivers ... and some technology for type conversions?
> I guess I should clone the source and see if it's possible to add a hookup 
> for the IBM i Access Client Solutions ODBC driver?
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/1ca9897d-8276-42e4-8943-9599d4366349n%40googlegroups.com.

Re: [sqlalchemy] How to use IBM i Access ODBC Driver

[Jack W.]

Thanks for the prompt response, Michael ...

On Wednesday, October 11, 2023 at 7:01:47 AM UTC-6 Mike Bayer wrote:

SQLAlchemy does not include support for this driver - the only IBM driver 
is the ibm_db_sa you reference, so short of writing your own driver based 
on that one, that's what's available.


I infer there's a plugin regimen in the SQLAlchemy source to connect with 
supported drivers ... and some technology for type conversions?
I guess I should clone the source and see if it's possible to add a hookup 
for the IBM i Access Client Solutions ODBC driver?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b520f2dd-0bb7-4b74-b1d6-5a145858cd6bn%40googlegroups.com.

[sqlalchemy] How to use IBM i Access ODBC Driver

[Jack W.]

I have the IBM i Access ODBC Driver 
 
installed on Linux and I'm trying out Apache Superset.

The SQLAlchemy URIs I create don't work, e.g.,

   - iaccess+pyodbc://x  driver not found
   - All the "traditional" URIs, e.g., using ibm_db_sa+pyodbc:///xxx 
   don't work because I don't have that older driver 
   

 
   installed (which does seem to be supported by SQLAlchemy).

TIA for any tips.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ba319cc9-ef50-4eee-b16c-989a3e4fd971n%40googlegroups.com.

[sqlalchemy] Relationship between two models without constraints or cascades

[Jack Matthews]

The database I am trying to maintain is a representation of a configuration 
file I have scraped from a network device. I have two models that when both 
exist in the configuration are related to each other, but it is also 
possible that one or the other may not be present. The schemas are included 
below.

These models have compound primary keys for node_id, interface and 
unit_num. Where these 3 values are the same on both objects, there is a one 
to one relationship between them. However, it's possible that a Unit object 
could exist without a matching CosInterface and vice versa.

class Unit(Base):
__tablename__ = 'units_juniper'

id = Column(Integer, autoincrement=True, nullable=False, unique=True)
node_id = Column(Integer, primary_key=True, nullable=False, index=True)
interface = Column(String(32), primary_key=True, nullable=False, 
index=True)
unit_num = Column(Integer, primary_key=True, nullable=False, index=True)


class CosInterface(Base):
__tablename__ = "cos_interfaces_juniper"
id = Column(Integer,autoincrement=True)
node_id = Column(Integer, ForeignKey("nodes_juniper.id"), 
primary_key=True, nullable=False, index=True)
interface = Column(String(15), primary_key=True, unique=False, 
nullable=False)
unit_num = Column(String(15), primary_key=True, unique=False, 
nullable=False)


unit = relationship('Unit', 
foreign_keys=[Unit.node_id,Unit.interface,Unit.unit_num], uselist=False, 
lazy='select', backref="cos_interface",
primaryjoin=and_(Unit.node_id == node_id, 
Unit.interface == Interface, Unit.unit_num == unit_num))

The scenario I am having an issue is when I have a Unit and CosInterface in 
the database with a working one to one relationship. I would like to delete 
the CosInterface object, and leave the Unit object in the database. 
Currently I get the following error:

AssertionError: Dependency rule tried to blank-out primary key column 
'units_juniper.node_id' on instance ''

I understand why this is happening, and to solve this I should have a 
cascade delete rule to remove the corresponding Unit object. However, as I 
explained, the Unit object can exist without the CosInterface, so I don't 
want to delete the Unit object.

All I'm looking to achieve is for these models to have an attribute I can 
call (e.g. Unit.cos_interface), that when present returns the corresponding 
model object. If there isn't one in the database then it can just return 
None. I suspect I'm using Relationship() incorrectly here, but I haven't 
been able to find the proper solution to this online. I guess essentially I 
would just like to have an attribute on the model that returns the result 
of a query, e.g.

class Unit(Base):
__tablename__ = 'units_juniper'

id = Column(Integer, autoincrement=True, nullable=False, unique=True)
node_id = Column(Integer, primary_key=True, nullable=False, index=True)
interface = Column(String(32), primary_key=True, nullable=False, 
index=True)
unit_num = Column(Integer, primary_key=True, nullable=False, index=True)

*cos_interface = CosInterface.filter(CosInterface.node_id == node_id, 
CosInterface.interface == interface, CosInterface.unit_num == 
unit_num).first()*

Is there a way to do this?

Cheers,
Jack




-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/1581af13-9037-4ded-84c7-1585964e69f3n%40googlegroups.com.

Re: [sqlalchemy] back_populates doesn't work properly on one-to-one relationships (uselist=False)

[Jack Zhou]

Ah, hadn't realized that they don't work on load at all. Is there any
reason why they couldn't be made to communicate on load as well? It seems a
little weird that the behavior is subtly different when an object is loaded
vs when it's constructed.

On Fri, Mar 18, 2016 at 6:50 AM Mike Bayer <clas...@zzzcomputing.com> wrote:

>
>
> On 03/17/2016 09:15 PM, univerio wrote:
> > Consider these models:
> >
> > class Foo(Base):
> >  __tablename__ = 'foo'
> >
> >  id = Column(Integer, primary_key=True, autoincrement=True)
> >  bar_id = Column(Integer, ForeignKey("bar.id"), unique=True)
> >  bar = relationship(lambda: Bar, back_populates="foo")
> >
> >
> > class Bar(Base):
> >  __tablename__ = "bar"
> >
> >  id = Column(Integer, primary_key=True, autoincrement=True)
> >  foo = relationship(Foo, back_populates="bar", uselist=False)
> >
> >
> > When loading foo.bar, I would expect foo.bar.foo to be set to foo
> > without an additional query to the database, but this doesn't appear to
> > be the case. Test code:
> >
> >
> > engine = create_engine("sqlite://", echo=True)
> > Base.metadata.create_all(bind=engine)
> > session = Session(bind=engine)
> >
> > session.add(Foo(bar=Bar()))
> > session.flush()
> > session.expunge_all()
> >
> > f = session.query(Foo).first()
> > b = f.bar
> > print("there shouldn't be anymore SQL queries")
> > assert b.foo is f
>
> backrefs only communicate with each other when you manually set or
> remove items from an attribute.  They don't coordinate during a load
> operation.   To suit the common use case of a collection load where the
> other many-to-one side should not emit SQL, this is easy because the
> many-to-one side knows it can do a simple lookup in the identity map.
> With the one-to-many/one side, this is not possible because we don't
> know the primary key of the single element that would be here.
>
>
>
>
> >
> >
> > This results in the following output:
> >
> > [...]
> > 2016-03-17 18:04:46,858 INFO sqlalchemy.engine.base.Engine BEGIN
> > (implicit)
> > 2016-03-17 18:04:46,858 INFO sqlalchemy.engine.base.Engine INSERT
> > INTO bar DEFAULT VALUES
> > 2016-03-17 18:04:46,858 INFO sqlalchemy.engine.base.Engine ()
> > 2016-03-17 18:04:46,859 INFO sqlalchemy.engine.base.Engine INSERT
> > INTO foo (bar_id) VALUES (?)
> > 2016-03-17 18:04:46,859 INFO sqlalchemy.engine.base.Engine (1,)
> > 2016-03-17 18:04:46,860 INFO sqlalchemy.engine.base.Engine SELECT
> > foo.id AS foo_id, foo.bar_id AS foo_bar_id
> > FROM foo
> >   LIMIT ? OFFSET ?
> > 2016-03-17 18:04:46,860 INFO sqlalchemy.engine.base.Engine (1, 0)
> > 2016-03-17 18:04:46,861 INFO sqlalchemy.engine.base.Engine SELECT
> > bar.id AS bar_id
> > FROM bar
> > WHERE bar.id = ?
> > 2016-03-17 18:04:46,861 INFO sqlalchemy.engine.base.Engine (1,)
> > there shouldn't be anymore SQL queries
> > 2016-03-17 18:04:46,861 INFO sqlalchemy.engine.base.Engine SELECT
> > foo.id AS foo_id, foo.bar_id AS foo_bar_id
> > FROM foo
> > WHERE ? = foo.bar_id
> > 2016-03-17 18:04:46,861 INFO sqlalchemy.engine.base.Engine (1,)
> > 2016-03-17 18:04:46,862 INFO sqlalchemy.engine.base.Engine ROLLBACK
> >
> >
> > I understand that when uselist=True there might be other items in the
> > list so you can't populate that list, but when uselist=False, especially
> > when there's a unique constraint ensuring that there can only ever be
> > one entry, you should be able to populate it. Am I missing something?
> >
> >
> > Jack
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to sqlalchemy+unsubscr...@googlegroups.com
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> > To post to this group, send email to sqlalchemy@googlegroups.com
> > <mailto:sqlalchemy@googlegroups.com>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic,

Re: [sqlalchemy] [ORM] Fetching columns that were assigned a SQL expression as part of INSERT...RETURNING and UPDATE...RETURNING

[Jack Zhou]

Great! That worked, though I had to add server_default=FetchedValue()
and server_onupdate=FetchedValue().
Would be nice if SQLAlchemy auto-detected this case, but no big deal
otherwise.


Thanks!

Jack

On Tue, Apr 28, 2015 at 4:42 PM Mike Bayer mike...@zzzcomputing.com wrote:

  use the eager_defaults flag:



 http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html?highlight=eager_defaults#sqlalchemy.orm.mapper.params.eager_defaults


 http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#orm-can-efficiently-fetch-just-generated-insert-update-defaults-using-returning


 not sure if this works for the ad-hoc set up updated_at, try it out.   if
 it's a server_default on the Column, should definitely work.





 On 4/28/15 7:35 PM, univerio wrote:

 Suppose I have a PostgreSQL backend and I have the following class:

  class Foo(Base):
 id = Column(Integer, primary_key=True)
 updated_at = Column(DateTime)

  and I do

  foo = Foo(updated_at=func.now())
 session.add(foo)
 session.flush()
 foo.id  # this is already loaded, no additional query emitted
 foo.updated_at  # this is not loaded, will cause an additional query
 to be emitted

  Is it possible to have the SQLAlchemy ORM fetch the actual value of
 updated_at as part of the INSERT...RETURNING statement like it does for id,
 instead of leaving it unloaded and having to issue a second query when I
 access it?


  Jack
  --

 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an
 email to sqlalchemy+unsubscr...@googlegroups.com.


 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.

  --
 You received this message because you are subscribed to a topic in the
 Google Groups sqlalchemy group.
 To unsubscribe from this topic, visit
 https://groups.google.com/d/topic/sqlalchemy/F2QKToSNKKM/unsubscribe.
 To unsubscribe from this group and all its topics, send an email to
 sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] subqueryload relationship in polymorphic base class with order_by on subclass column results in (ProgrammingError) missing FROM-clause entry

[Jack Zhou]

Thanks for the quick response, Mike!


On Mon, Jun 30, 2014 at 4:26 PM, Mike Bayer mike...@zzzcomputing.com
wrote:


 On 6/30/14, 7:03 PM, Mike Bayer wrote:



 there's a little bit of a glitch here, however in any case, the ORDER BY
 would be from E.Engineer.specialty.

 The glitch is that the subq load at the moment seems to need an additional
 hint as to what its selecting from:


 session.query(E).select_from(E).options(subqueryload(E.cars)).order_by(E.Engineer.specialty).first()

 will look into that.


 OK, for now
 session.query(E).select_from(E).options(subqueryload(E.cars)).order_by(Engineer.specialty).first()
 is fine (don't need E.Engineer in the order_by), the issue is
 https://bitbucket.org/zzzeek/sqlalchemy/issue/3106/subquery-load-on-with_polymorphic-entity
 and with the patch there once I get tests in, the query will work as you
 had it originally.



  --
 You received this message because you are subscribed to a topic in the
 Google Groups sqlalchemy group.
 To unsubscribe from this topic, visit
 https://groups.google.com/d/topic/sqlalchemy/gvOJr4cSO-A/unsubscribe.
 To unsubscribe from this group and all its topics, send an email to
 sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Combining aliases with labels

[Jack Kordas]

On Sep 9, 9:53 am, Conor conor.edward.da...@gmail.com wrote:
 On 09/08/2010 01:05 PM, Jack Kordas wrote:



  When I try to use both aliases and labels, the results are not named
  as expected.

  Instead of being able to access the columns as label-name_column-
  name it appears as original-table-name_numeric-sequence_column-
  name

  Thanks,
    Jack

  Sample code follows:

  parent = Table('parent', metadata,
      Column('id', INTEGER(), primary_key=True),
      Column('name', VARCHAR(length=128)),
      Column('first_id', INTEGER(),
          ForeignKey(u'child.id')),
    )

  child = Table('child', metadata,
      Column('id', INTEGER(), primary_key=True),
      Column('name', VARCHAR(length=128))
    )

  def test_labels1(conn):
      s = select([parent,child], use_labels=True)
      s = s.where(parent.c.first_id==child.c.id)
      return conn.execute(s).fetchone()

  def test_alias1(conn):
      firstchild = child.alias()
      s = select([parent,firstchild], use_labels=True)
      s = s.where(parent.c.first_id==firstchild.c.id)
      return conn.execute(s).fetchone()

  conn = engine.connect()

  results = test_labels1(conn)
  print results.parent_name
  print results.child_name

  results = test_alias1(conn)
  print 'alias1 results: '
  print results.parent_name
  #print results.firstchild_name # expected this to work
  print results.child_1_name # this worked instead

 You need to set an explicit name for the alias to prevent SQLAlchemy
 from generating an anonymous name[1]:

 firstchild = child.alias(firstchild)

 -Conor

 [1]http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sq...

Thanks, that did the trick.  I didn't appreciate the difference
between using the aliased variable in the from clause and generating
names for the selected columns.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Combining aliases with labels

[Jack Kordas]

When I try to use both aliases and labels, the results are not named
as expected.

Instead of being able to access the columns as label-name_column-
name it appears as original-table-name_numeric-sequence_column-
name

Thanks,
  Jack

Sample code follows:

parent = Table('parent', metadata,
Column('id', INTEGER(), primary_key=True),
Column('name', VARCHAR(length=128)),
Column('first_id', INTEGER(),
ForeignKey(u'child.id')),
  )

child = Table('child', metadata,
Column('id', INTEGER(), primary_key=True),
Column('name', VARCHAR(length=128))
  )

def test_labels1(conn):
s = select([parent,child], use_labels=True)
s = s.where(parent.c.first_id==child.c.id)
return conn.execute(s).fetchone()

def test_alias1(conn):
firstchild = child.alias()
s = select([parent,firstchild], use_labels=True)
s = s.where(parent.c.first_id==firstchild.c.id)
return conn.execute(s).fetchone()

conn = engine.connect()

results = test_labels1(conn)
print results.parent_name
print results.child_name

results = test_alias1(conn)
print 'alias1 results: '
print results.parent_name
#print results.firstchild_name # expected this to work
print results.child_1_name # this worked instead

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Can not access the __table__ attribute in string-based primaryjoin parameter

[Jack]

People can use string-based primaryjoin parameter to define the
sqlalchemy.orm.relation instance if people use declarative base
class. But the __table__ attribute is not available. Look at this
example.

Chassis and Blade are sub-class of System.  The sysid property
shares the same name in parent and children classes. And there is one-
to-many relationship between Chassis and Blade. So for example I have
to use Chassis.__table__.c.sysid instead of Chassis.sysid to
define join condition. The error line marked # error happens
# can cause following exception.

I know work-around, like renaming sysid, or using Python clause
instead of string in primaryjoin parameter and defining relation
outside class definition. But I wonder whether it is problem or is by
design. Anyone can help? Thx!

p.s. my sqlalchemy is latest 0.6 (changeset:   6410:09e0ec53d4d0)

##
#exception
##
  File d:\program\src\python\sqlalchemy\lib\sqlalchemy\orm
\mapper.py, line 822, in _get_property
raise sa_exc.InvalidRequestError(Mapper '%s' has no property
'%s' % (str(self), key))
InvalidRequestError: Mapper 'Mapper|Chassis|tbl_chassis' has no
property '__table__'


##
#code
##
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import (Column, ForeignKey, Integer, String)
from sqlalchemy.orm import relation


SYS_TYPE_UNKNOWN = 0
SYS_TYPE_CHASSIS = 1
SYS_TYPE_BLADE = 2

Base = declarative_base()

class System(Base):
'''Generic system'''
__tablename__ = 'tbl_system'
sysid = Column(Integer, primary_key=True, autoincrement=True)
sys_type = Column(Integer)
__mapper_args__ = {'polymorphic_on': sys_type,
'polymorphic_identity': SYS_TYPE_UNKNOWN}


class Chassis(System):
'''BladeCenter Chassis'''
__tablename__ = 'tbl_chassis'
__mapper_args__ = {'polymorphic_identity': SYS_TYPE_CHASSIS}
sysid = Column(Integer, ForeignKey('tbl_system.sysid'),
primary_key=True)

# error happens #
blades = relation('Blade', primaryjoin='Chassis.__table__.c.sysid
== Blade.chassis_sysid', backref='chassis')


class Blade(System):
'''Blade server'''
__tablename__ = 'tbl_blade'
__mapper_args__ = {'polymorphic_identity': SYS_TYPE_BLADE}
sysid = Column(Integer, ForeignKey('tbl_system.sysid'),
primary_key=True)
chassis_sysid = Column(Integer, ForeignKey('tbl_chassis.sysid'))

if __name__ == '__main__':
ch1 = Chassis()

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] checking if a column is in a list

[Jack Stahl]

Hello,

I've got a few SQL interfaces where I'd like to change my query based on the
columns required by the client.  For example, in one situation, I only join
against my User table if my client requires a photo id:

# cols is the list of columns the client would like selected
if User.c.photo_id in cols:
table = table.join(User.table)

In another situation, I'd like to include the flags column in the query even
if the client ask for it

if not cols:
cols = [cls]
elif not (cls.c.flags in cols):
# ensure flags are there so we can add is_active
cols.append(cls.c.flags)

However, my tests for membership pass regardless of whether column is
actually in the list cols.  That is,

Column('flags', BitField(), table=foo_table) in [Column('foo',
String(length=84, convert_unicode=False, assert_unicode=None),
table=foo_table), Column('bar', Text(length=None, convert_unicode=False,
table=foo_table)]

evaluates to True

Is the == operator not properly implemented for SQLAlchemy Columns?  (I'm
using version 0.42)

Thanks,
Jack

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: checking if a column is in a list

[Jack Stahl]

Thanks Michael!
Properly was a poor choice of words on my part.  Yes, of course, == is
overloaded to make where (etc) clauses pretty, I just didn't put two and two
together.
--jack

On Wed, Jan 21, 2009 at 5:38 PM, Michael Bayer mike...@zzzcomputing.comwrote:



 On Jan 21, 2009, at 7:46 PM, Jack Stahl wrote:

  Hello,
 
  I've got a few SQL interfaces where I'd like to change my query
  based on the columns required by the client.  For example, in one
  situation, I only join against my User table if my client requires a
  photo id:
 
  # cols is the list of columns the client would like selected
  if User.c.photo_id in cols:
  table = table.join(User.table)
 
  In another situation, I'd like to include the flags column in the
  query even if the client ask for it
 
  if not cols:
  cols = [cls]
  elif not (cls.c.flags in cols):
  # ensure flags are there so we can add is_active
  cols.append(cls.c.flags)
 
  However, my tests for membership pass regardless of whether column
  is actually in the list cols.  That is,
 
  Column('flags', BitField(), table=foo_table) in [Column('foo',
  String(length=84, convert_unicode=False, assert_unicode=None),
  table=foo_table), Column('bar', Text(length=None,
  convert_unicode=False, table=foo_table)]
 
  evaluates to True
 
  Is the == operator not properly implemented for SQLAlchemy Columns?
  (I'm using version 0.42)

 it is not properly implemented in the sense that it does not return
 True or False, but if you''ve used SQLalchemy, you'd know that we
 redefine the == operator to return ClauseElement objects.   That's
 why you can say somecolumn==5 and get an expression from it.

 So you cannot use the in operator to search for a Column object in a
 list.  The solution however is simple.  Use a set() instead, where the
 objects will be compared based on the return value of __hash__() which
 results in an object identity comparison.

if User.c.photo_id in set(cols):


 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Insert Multiple Items into a list

[Jack Stahl]

Hello all,

I'd like to use SQL Alchemy to do a single insert with multiple
values , such as:

INSERT INTO foobar (foo, bar) VALUES ('value1', 0), ('value2', 1);

I've found a few ways to do this using a SQLAlchemy connection /
the .execute() method on the Insert object.
However, my problem is that my system is designed such that I
compiling queries and executing them through my own MySQL cursor
query.compile():

query = table.table.insert(values=values).compile()
cursor.execute(str(query), query.params)

Is there support for multiple values in compiled query objects?  It
appeared that I could somehow use compile bind parameters to support,
but my efforts were futile.

Thanks,
Jack Stahl

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---