[sqlalchemy] Default behavior for sqlalchemy_utils.UUIDType

[Jason Libbey]

Hi, this is my first post, so if it does not adhere by your guidelines, 
please let me know and I'll fix it ASAP.

I'm using the sqlalchemy_utils.UUIDType as per backend-agnostic-guid-type 

.

from sqlalchemy_utils import UUIDType



class ApplicationStore(Base, Timestamp):

__tablename__ = 'applications'

id = Column(UUIDType(binary=False), primary_key=True, default=uuid.uuid4)

user_uuid = Column(UUIDType(binary=False), unique=True, index=True, 
nullable=False)



Since I am using a mysql db, the UUIDType is correctly falling back to the 
CHAR32 representation.  However, the value that gets written to the db is 
the uuid with no dashes.


application = application_service.create(user_uuid=uuid.uuid4())



The string representation of the uuid shows it with dashes, but the 
database char is saved without dashes.  So my questions are:
1. Am I using this correctly?
2. Is this the expected behavior?
3. How can I force it to save as uuid with dashes, while still using the 
backend agnostic guid type?

Python version: 2.7.10
SQLAlchemy version: 1.0.12

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Understanding behavior of association_proxy

[Eric Wittle]

Thank you, that helps!

-Eric

On Sunday, July 24, 2016 at 7:57:18 PM UTC-4, Eric Wittle wrote:
>
> I'd like to understand the behavior of association_proxy when creating new 
> records across a many-to-many table where, in some cases, the joining table 
> will have additional attribute values beyond the two primary keys. In the 
> below example, there is a many-many between Person and Events represented 
> by the PersonEvents table. If I create and commit the person record, create 
> and commit the event record, and then add the event to the person and try 
> to commit, I get a key error. If I create the Person record, create the 
> Event record, and then create the PersonEvent record before committing, I 
> get no error and my tests pass.
>
> There are two differences in the test approaches that I think I 
> understand; in the failing case the PersonEvent object is being created 
> implicitly through the association proxy definition, and the commit order 
> is different. However, as I read the documentation, I don't understand why 
> the results are different. I'd like to avoid building more complex logic 
> until I understand whether the association_proxy has use case limitations I 
> don't understand.
>
> Here's the data model code:
>
> class Person(Base):
> __tablename__ = 'Person'
> __table_args__ = {'mysql_charset':'utf8'}
> id = Column(Integer, primary_key=True)
> full_name = Column(String(240))
>
> email = Column(String(120),unique=True)
> other_data = Column(JSON)
> events = association_proxy('PersonEvent','Event')
>
>
> class PersonEvent(Base):
> __tablename__ = 'PersonEvent';
> __tableargs__ = {'mysql_charset':'utf8'}
> person_id = Column(Integer, ForeignKey('Person.id'), primary_key=True)
> event_id = Column(Integer, ForeignKey('Event.id'), primary_key = True)
> role = Column(String(40))
>
>
> # bi-directional attribute / collection of "Person" / "Event"
> person = relationship('Person',
>   backref=backref("PersonEvent",
>   cascade="all, delete-orphan"))
> 
> # reference to the Event object
> event = relationship('Event')
>
>
> class Event(Base):
> __tablename__ = 'Event'
> __table_args__ = {'mysql_charset':'utf8'}
> id = Column(Integer, primary_key=True)
> start = Column(DateTime)
> end = Column(DateTime)
> short_name = Column(String(40))
> name = Column(String(240))
> other_data = Column(JSON)
>
> The following code in the test setup method throws a KeyError on the last 
> commit from emit_backref_from_scalar_set_event child_impl = 
> child_state.manager[key].impl.
>
> session = DBSession()
> p1 = Person(id=1, first_name='Eric', last_name='Wittle',
> full_name='Eric L. Wittle', email='z...@wittle.net 
> ')
> p1.set('favorite_color','red')
> session.add(p1)
> session.commit()
> logger.debug('Added person 1')
> e1 = Event(id=1, name='Birth', 
>  start=datetime.strptime('01/25/1976',"%m/%d/%Y"), 
>  end=datetime.strptime('01/25/1976',"%m/%d/%Y"))
> session.add(e1)
> session.commit()
> logger.debug('Added event 1')
> p1.events.append(e1)
> session.add(p1)
> session.commit()
>
> However, if I replace it with the code below, the data setup completes 
> without error, and the test passes fine (looking up person with id 1 and 
> testing if the property events[0].name == 'Birth'):
>
> session = DBSession()
> p1 = Person(id=1, first_name='Eric', last_name='Wittle',
> full_name='Eric L. Wittle', email='z...@wittle.net 
> ')
> p1.set('favorite_color','red')
> e1 = Event(id=1, name='Birth', 
>  start=datetime.strptime('01/25/1976',"%m/%d/%Y"), 
>  end=datetime.strptime('01/25/1976',"%m/%d/%Y"))
> pe1 = PersonEvent(person=p1, event=e1, role = 'Owner')
> session.add(p1)
> session.commit()
>
> The first set of code that fails seems more similar to the example in the 
> association proxy documentation, section "Simplifying Association Objects" 
> than the code that passes.
>
> Thanks in advance for any advice & guidance. I've been really impressed 
> with the sqlalchemy orm so far, and am trying to use more of it. 
>
> -Eric
>
>
>
>
>
>
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Understanding behavior of association_proxy

[Mike Bayer]

On 07/29/2016 06:39 PM, Eric Wittle wrote:



I don't have sqlite installed, so I skipped actually creating the engine,


all Python versions have sqlite3 included in the distribution itself 
unless you're on Jython (which I doubt works with SQLA these days)?




|
fromsqlalchemy importColumn,String,Integer,ForeignKey
fromsqlalchemy.orm importrelationship,backref
fromsqlalchemy.ext.associationproxy importassociation_proxy
fromsqlalchemy.ext.declarative importdeclarative_base


Base=declarative_base()


classPerson(Base):
__tablename__ ='Person'
id =Column(Integer,primary_key=True)
first_name =Column(String(80))
last_name =Column(String(120))
events =association_proxy('person_events','Event')


classPersonEvent(Base):
__tablename__ ='PersonEvent';
person_id =Column(Integer,ForeignKey('Person.id'),primary_key=True)
event_id =Column(Integer,ForeignKey('Event.id'),primary_key =True)
role =Column(String(40))


# bi-directional attribute / collection of "Person" / "Event"
person =relationship(Person,
  backref=backref('person_events',
  cascade="all, delete-orphan"))

# reference to the Event object
event=relationship('Event')
def__init__(self,person=None,event=None,role=None):
self.person =person
self.event=event
self.role =role


classEvent(Base):
__tablename__ ='Event'
id =Column(Integer,primary_key=True)
short_name =Column(String(40))


p1 =Person(first_name='Eric',last_name='Wittle')
p1.events.append(Event(short_name='Birth'))


here's how you debug this.   Use pdb:

def __init__(self, person=None, event=None, role=None):
import pdb
pdb.set_trace()
self.person = person
self.event = event
self.role = role

then:

> /home/classic/dev/sqlalchemy/test.py(35)__init__()
-> self.person = person
(Pdb) !person
<__main__.Event object at 0x7ff6180f06d0>

we can see that your PersonEvent has a constructor that is not 
compatible with the documented default creator behavior:

http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/associationproxy.html#creation-of-new-values

now the "creator" thing in association_proxy is not very good or 
intuitive, but we are stuck with it for now.  So pass in a real creator:


class Person(Base):
__tablename__ = 'Person'
id = Column(Integer, primary_key=True)
first_name = Column(String(80))
last_name = Column(String(120))
events = association_proxy('person_events','Event',
creator=lambda event: PersonEvent(event=event))







|

I thought I was fairly faithfully following the example in Simplifying
Association Proxies. I'm assuming the first argument to assocation_proxy
is the name of the backref created in the relationship in the
association object, but I'm unclear on that. I tried your code
suggestion (minus actually creating the engine and adding in the
__init__ method to the association object to work around __init__ takes
1 positional argument but two were given error). The edited version I
ran is below, and it also gives KeyError:

|
fromsqlalchemy import*
fromsqlalchemy.orm import*
fromsqlalchemy.ext.declarative importdeclarative_base
importdatetime
fromsqlalchemy.ext.associationproxy importassociation_proxy

Base=declarative_base()

classPerson(Base):
__tablename__ ='Person'
__table_args__ ={'mysql_charset':'utf8'}
id =Column(Integer,primary_key=True)
full_name =Column(String(240))
email =Column(String(120),unique=True)
other_data =Column(String(50))
events =association_proxy('PersonEvent','Event')

classPersonEvent(Base):
__tablename__ ='PersonEvent'
__tableargs__ ={'mysql_charset':'utf8'}
person_id =Column(Integer,ForeignKey('Person.id'),primary_key=True)
event_id =Column(Integer,ForeignKey('Event.id'),primary_key =True)
role =Column(String(40))

 # bi-directional attribute / collection of "Person" / "Event"
person =relationship('Person',
  backref=backref("PersonEvent",
  cascade="all, delete-orphan"))

# reference to the Event object
event=relationship('Event')

def__init__(self,person=None,event=None,role=None):
self.person =person
self.event=event
self.role =role

classEvent(Base):
__tablename__ ='Event'
__table_args__ ={'mysql_charset':'utf8'}
id =Column(Integer,primary_key=True)
short_name =Column(String(40))
name =Column(String(240))
other_data =Column(String(100))

p1 =Person(id=1,full_name='Eric L. Wittle',email='er...@wittle.net')
e1 =Event(id=1,name='Birth')
p1.events.append(e1)
|

This is running python 3.5.1 with SQLAlchemy 1.1.0b2.

-Eric














--
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] Re: Understanding behavior of association_proxy

[Eric Wittle]

OK, my last post I tried to edit the code while I was posting to simplify 
it, and clearly missed a bit. Here's the case that is failing simplified as 
much as possible. I get a key error on the last line. I don't have sqlite 
installed, so I skipped actually creating the engine, but I'm not sure that 
matters. Feel free to correct me if I'm wrong.

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Person(Base):
__tablename__ = 'Person'
id = Column(Integer, primary_key=True)
first_name = Column(String(80))
last_name = Column(String(120))
events = association_proxy('person_events','Event')


class PersonEvent(Base):
__tablename__ = 'PersonEvent';
person_id = Column(Integer, ForeignKey('Person.id'), primary_key=True)
event_id = Column(Integer, ForeignKey('Event.id'), primary_key = True)
role = Column(String(40))


# bi-directional attribute / collection of "Person" / "Event"
person = relationship(Person,
  backref=backref('person_events',
  cascade="all, delete-orphan"))

# reference to the Event object
event = relationship('Event')
def __init__(self, person=None, event=None, role=None):
self.person = person
self.event = event
self.role = role


class Event(Base):
__tablename__ = 'Event'
id = Column(Integer, primary_key=True)
short_name = Column(String(40))


p1 = Person(first_name='Eric', last_name='Wittle')
p1.events.append(Event(short_name='Birth'))

I thought I was fairly faithfully following the example in Simplifying 
Association Proxies. I'm assuming the first argument to assocation_proxy is 
the name of the backref created in the relationship in the association 
object, but I'm unclear on that. I tried your code suggestion (minus 
actually creating the engine and adding in the __init__ method to the 
association object to work around __init__ takes 1 positional argument but 
two were given error). The edited version I ran is below, and it also gives 
KeyError:

from sqlalchemy import * 
from sqlalchemy.orm import * 
from sqlalchemy.ext.declarative import declarative_base 
import datetime 
from sqlalchemy.ext.associationproxy import association_proxy 

Base = declarative_base() 

class Person(Base): 
__tablename__ = 'Person' 
__table_args__ = {'mysql_charset':'utf8'} 
id = Column(Integer, primary_key=True) 
full_name = Column(String(240)) 
email = Column(String(120),unique=True) 
other_data = Column(String(50)) 
events = association_proxy('PersonEvent','Event') 

class PersonEvent(Base): 
__tablename__ = 'PersonEvent'
__tableargs__ = {'mysql_charset':'utf8'} 
person_id = Column(Integer, ForeignKey('Person.id'), primary_key=True) 
event_id = Column(Integer, ForeignKey('Event.id'), primary_key = True) 
role = Column(String(40)) 

 # bi-directional attribute / collection of "Person" / "Event" 
person = relationship('Person', 
  backref=backref("PersonEvent", 
  cascade="all, delete-orphan")) 

# reference to the Event object 
event = relationship('Event') 

def __init__(self, person=None, event=None, role=None):
self.person = person
self.event = event
self.role = role

class Event(Base): 
__tablename__ = 'Event' 
__table_args__ = {'mysql_charset':'utf8'} 
id = Column(Integer, primary_key=True) 
short_name = Column(String(40)) 
name = Column(String(240)) 
other_data = Column(String(100)) 

p1 = Person(id=1, full_name='Eric L. Wittle', email='er...@wittle.net') 
e1 = Event(id=1, name='Birth') 
p1.events.append(e1) 

This is running python 3.5.1 with SQLAlchemy 1.1.0b2.

-Eric














-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] relationship with DeferredReflection raises NoForeignKeysError

[Mike Bayer]

name the COUNTRY_ID column in lower case in your mapping, all UPPERCASE 
means case sensitive and it will be quoted "COUNTRY_ID", and not match 
the case-insensitive country_id name in your actual schema.




On 07/29/2016 03:29 PM, bsdz wrote:

I did some further checking and realized deeper down the real issue lies
column name case and with column_reflect where I map my column names to
lower case. This seems to confuse the ForeignKey function.

Here's a complete example with sqlite. Perhaps there's a simple workaround?

|
importsqlalchemy assa
fromsqlalchemy.orm importsessionmaker,relationship
fromsqlalchemy.ext.declarative importdeclarative_base,DeferredReflection
fromsqlalchemy.eventimportlistens_for

e =sa.create_engine("sqlite://",echo=True)

e.execute("""
 create table country (
   COUNTRY_ID integer primary key
 )
""")

e.execute("""
 create table main.user (
 user_id integer primary key,
 COUNTRY_ID integer,
 foreign key (country_id) references country(country_id)
 )
""")

Base=declarative_base(cls=DeferredReflection)

classCountry(Base):
 __tablename__ ='country'

classUser(Base):
 __tablename__ ='user'
 __table_args__ ={'schema':'main'}

 country_id
=sa.Column("COUNTRY_ID",sa.Integer,sa.ForeignKey('country.COUNTRY_ID'))
 country =relationship("Country",uselist=False)

classMyModel(object):
 def__init__(self,env):
 self._engine =e
 Base.metadata.bind =self._engine
 Base.prepare(self._engine)


 defcreate_session_maker(self):
 returnsessionmaker(bind=self._engine)

@listens_for(sa.Table,"column_reflect")
defcolumn_reflect(inspector,table,column_info):
 """
 Map upper case column names to lower case.
 """
 column_info['key']=column_info['name'].lower()

# This code is run in another module.
mymodel =MyModel("DEV")
Session=mymodel.create_session_maker()
session =Session()
l1 =session.query(User).all()

print(User.country.property.primaryjoin)
|


The code above produces the following error:

|

NoReferencedColumnError:Couldnotinitialize target column
forForeignKey'country.COUNTRY_ID'on table 'user':table 'country'has
nocolumn named 'COUNTRY_ID'


|

Thanks again :)


--
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 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 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] nested subqueries in a hybrid expression?

[Brian Cherinka]

The @expression as column thing is a bit confusing since in the correlated 
subquery example in the hybrid attribute section, it looks like you are 
returning a select?  Does the .label() effectively turn it into a column?

class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(100), nullable=False)

accounts = relationship("SavingsAccount", backref="owner")

@hybrid_property
def balance(self):
return sum(acc.balance for acc in self.accounts)

@balance.expression
def balance(cls):
return select([func.sum(SavingsAccount.balance)]).\
where(SavingsAccount.user_id==cls.id).\
label('total_balance')




On Friday, July 29, 2016 at 5:29:45 PM UTC-4, Brian Cherinka wrote:
>
>
> Oh interesting.  I didn't know that about the @expression.  I'll play 
> around with the as_scalar() as well, and see if I can get something to 
> work.  
>
> class Wavelength(Base):
> __tablename__ = 'wavelength'
> __table_args__ = {'autoload': True, 'schema': 'mangadatadb', 
> 'extend_existing': True}
>
>
> wavelength = deferred(Column(ARRAY_D(Float, zero_indexes=True)))
>
>
> The wavelength table has a single row and single column, which is an 
> array.  
>
> The other table of interest would look something like 
>
> class NSA(Base):
> __tablename__ = 'nsa'
> __table_args__ = ({'autoload': True, 'schema': 'mangasampledb'})
>
>
> z = Column(Float)
>
>
> This table basically has a float column that corresponds to objects in the 
> main cube (object) table. Each float value is used to modify the array in 
> wavelength to a unique array for that object. 
>
> The Cube class joins to NSA via two tables that are just intermediate 
> linking tables for this purpose  Cube -> Table A -> Table AToB - > Table B 
> (NSA)
>
> class MangaTarget(Base):
> __tablename__ = 'manga_target'
> __table_args__ = {'autoload': True, 'schema': 'mangasampledb'}
>
>
> class MangaTargetToNSA(Base):
> __tablename__ = 'manga_target_to_nsa'
> __table_args__ = (
> ForeignKeyConstraint(['manga_target_pk'],
>  ['mangasampledb.manga_target.pk']),
> ForeignKeyConstraint(['nsa_pk'], ['mangasampledb.nsa.pk']),
> {'autoload': True, 'schema': 'mangasampledb'})
>
>
>  The rest can probably be hacked together.   Let me know if you need 
> anything else.  
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] nested subqueries in a hybrid expression?

[Brian Cherinka]

Oh interesting.  I didn't know that about the @expression.  I'll play 
around with the as_scalar() as well, and see if I can get something to 
work.  

class Wavelength(Base):
__tablename__ = 'wavelength'
__table_args__ = {'autoload': True, 'schema': 'mangadatadb', 
'extend_existing': True}


wavelength = deferred(Column(ARRAY_D(Float, zero_indexes=True)))


The wavelength table has a single row and single column, which is an array. 
 

The other table of interest would look something like 

class NSA(Base):
__tablename__ = 'nsa'
__table_args__ = ({'autoload': True, 'schema': 'mangasampledb'})


z = Column(Float)


This table basically has a float column that corresponds to objects in the 
main cube (object) table. Each float value is used to modify the array in 
wavelength to a unique array for that object. 

The Cube class joins to NSA via two tables that are just intermediate 
linking tables for this purpose  Cube -> Table A -> Table AToB - > Table B 
(NSA)

class MangaTarget(Base):
__tablename__ = 'manga_target'
__table_args__ = {'autoload': True, 'schema': 'mangasampledb'}


class MangaTargetToNSA(Base):
__tablename__ = 'manga_target_to_nsa'
__table_args__ = (
ForeignKeyConstraint(['manga_target_pk'],
 ['mangasampledb.manga_target.pk']),
ForeignKeyConstraint(['nsa_pk'], ['mangasampledb.nsa.pk']),
{'autoload': True, 'schema': 'mangasampledb'})


 The rest can probably be hacked together.   Let me know if you need 
anything else.  

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] nested subqueries in a hybrid expression?

[Mike Bayer]

you might need to change more than this, but at least the fundamental 
thing about @expression is that it has to return a column, not a Query 
or a select().   On either one, calling as_scalar() will give you a 
scalar subquery, e.g. a SELECT interpreted as a column.


Assuming there's still problems because once array_agg is involved, 
things generally get crazy, send along a Wavelength, NSA and MangaNSA 
model with that Cube and I can try putting it together.



On 07/29/2016 02:11 PM, Brian Cherinka wrote:


I'm trying to build a hybrid property / expression in one of my SQLA
base classes, and the expression side requires several subqueries and I
cannot for the life of me figure out the correct syntax.  I've tried
many different versions / syntaxes for the expression, using the SQL
alchemy expression language, the ORM language but I can't get any of it
work.  What am I missing here?  Here is my latest attempt.

The setup is this.  I have a class called Cube, which is my base object.
 For each object in that cube table, I need to grab an attribute from a
table A and use that attribute to modify an array column from a second
table B.  And I want to make this new array queryable, thus the
hybridization.

The equivalent raw SQL is

|
select(select(array_agg(unwave.restw))asrestwarr
from(select(unnest(w.wavelength)/(1+n.z))asrestw
frommangadatadb.wavelength asw)asunwave)frommangadatadb.cube asc join
mangasampledb.manga_target asm on m.pk=c.manga_target_pk join
mangasampledb.manga_target_to_nsa ast on t.manga_target_pk=m.pk join
mangasampledb.nsa asn on n.pk=t.nsa_pk join mangadatadb.wavelength asw
on w.pk=c.wavelength_pk limit 5;

|

The instance side is quite easy.  The class side is proving difficult.
What is the proper way to write this?  I've read through the
documentation countless times now and it hasn't helped much.

|
classCube
@hybrid_property
defrestwave(self):
ifself.target:
redshift =self.target.NSA_objects[0].z
wave =np.array(self.wavelength.wavelength)
restwave =wave/(1+redshift)
returnrestwave
else:
returnNone

@restwave.expression
defrestwave(cls):
session =db.Session()
nsaz =session.query(sampledb.NSA.z.label('z')).\
join(sampledb.MangaTargetToNSA,sampledb.MangaTarget,Cube).\
filter(Cube.mangaid
==cls.mangaid).subquery('nsaz',with_labels=True)
unwave
=session.query((func.unnest(Wavelength.wavelength)/(1+nsaz.c.z)).label('restw')).subquery('unwave',with_labels=True)
restwave =session.query(func.array_agg(unwave.c.restw))

returnrestwave
|


Trying
|

session.query(datadb.Cube.restwave).filter(datadb.Cube.mangaid=='1-113520').all()
|

fails with
|
AttributeError:'Query'objecthas noattribute 'is_clause_element'
|


I've also tried this.

|
@restwave.expression
defrestwave(cls):
unwave
=select([(func.unnest(Wavelength.wavelength)/(1+sampledb.NSA.z)).label('restw')])
restwave
=select([func.array_agg(unwave.c.restw).label('restwarr')]).select_from(
Cube.__table__.join(Wavelength.wavelength).
join(sampledb.MangaTarget).
join(sampledb.MangaTargetToNSA).
join(sampledb.NSA)
)
   returnrestwave

|

and this fails

|
In[6]:datadb.Cube.restwave
---
ArgumentErrorTraceback(most recent call last)
in()
>1datadb.Cube.restwave


/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/ext/hybrid.pyc
in__get__(self,instance,owner)
738def__get__(self,instance,owner):
739ifinstance isNone:
-->740returnself.expr(owner)
741else:
742returnself.fget(instance)


/Users/Brian/Work/sdss/repo/sdss/sdss_python_module/marvin/python/sdss/internal/database/utah/mangadb/DataModelClasses.pyc
inrestwave(cls)
454# unwave =
select([(func.unnest(Wavelength.wavelength)/(1+sampledb.NSA.z)).label('restw')])
455# restwave =
select([func.array_agg(unwave.c.restw).label('restwarr')]).select_from(
-->456# Cube.__table__.join(Wavelength.wavelength).
457# join(sampledb.MangaTarget).
458# join(sampledb.MangaTargetToNSA).


/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable.pyc
injoin(self,right,onclause,isouter)
350"""
351
--> 352 return Join(self, right, onclause, isouter)
353
354 def outerjoin(self, right, onclause=None):


/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable.pyc
in __init__(self, left, right, onclause, isouter)
658 """
659self.left =_interpret_as_from(left)
-->660self.right =_interpret_as_from(right).self_group()
661
662ifonclause isNone:

Re: [sqlalchemy] relationship with DeferredReflection raises NoForeignKeysError

[Blair Azzopardi]

I rechecked everything and I realised that the ForeignKey column is case
sensitive. Thanks for your help!

On 29 July 2016 at 17:58, Blair Azzopardi  wrote:

> Interesting. Yes each table was in a different schema in pre-simplified
> code and I am using SQL server via pyodbc from Linux. I'll try and get a
> more complete test case shortly.
>
> On 29 Jul 2016 5:53 p.m., "Mike Bayer"  wrote:
>
>>
>>
>> On 07/29/2016 12:27 PM, bsdz wrote:
>>
>>> Hi
>>>
>>> I'm trying to use DeferredReflection to encapsulate my data model so
>>> that it can easily be instantiated for different environments. However,
>>> I am having trouble creating relationships with a NoForeignKeysError
>>> being raised. I am guessing it is because the table metadata generation
>>> is being deferred, there's not enough information to create the foreign
>>> key information and therefore the relationships.
>>>
>>> I've simplified my code to the following:
>>>
>>> |
>>> importsqlalchemy assa
>>> fromsqlalchemy.orm importsessionmaker,relationship
>>> fromsqlalchemy.ext.declarative importdeclarative_base,DeferredReflection
>>>
>>>
>>> Base=declarative_base(cls=DeferredReflection)
>>>
>>> classCountry(Base):
>>> __tablename__ ='country'
>>>
>>> classUser(Base):
>>> __tablename__ ='user'
>>>
>>> country_id =sa.Column(sa.Integer,sa.ForeignKey('country.country_id'))
>>> country =relationship("Country",uselist=False)
>>>
>>> classMyModel(object):
>>> def__init__(self,env):
>>> self._engine =sa.create_engine("...")
>>> Base.metadata.bind =self._engine
>>> Base.prepare(self._engine)
>>>
>>> defcreate_session_maker(self):
>>> returnsessionmaker(bind=self._engine)
>>>
>>> # This code is run in another module.
>>> mymodel =MyModel("DEV")
>>> Session=model.create_session_maker()
>>> session =Session()
>>> l1 =session.query(User).all()
>>> |
>>>
>>> The following error comes back:
>>>
>>> |
>>> NoForeignKeysError:Couldnotdetermine join condition between parent/child
>>> tables on relationship User.country -there are noforeign keys linking
>>> these tables.Ensurethat referencing columns are associated witha
>>> ForeignKeyorForeignKeyConstraint,orspecify a 'primaryjoin'expression.
>>>
>>
>> can't reproduce.  Need information on database in use, and if distinct
>> schemas are involved.  See below, and perhaps try to modify this test case
>> in your environment to reproduce your failure.
>>
>>
>> import sqlalchemy as sa
>> from sqlalchemy.orm import sessionmaker, relationship
>> from sqlalchemy.ext.declarative import declarative_base,
>> DeferredReflection
>>
>> e = sa.create_engine("sqlite://", echo=True)
>>
>> e.execute("""
>> create table country (
>>   country_id integer primary key
>> )
>> """)
>>
>> e.execute("""
>> create table user (
>> user_id integer primary key,
>> country_id integer,
>> foreign key (country_id) references country(country_id)
>> )
>> """)
>>
>>
>> Base = declarative_base(cls=DeferredReflection)
>>
>>
>> class Country(Base):
>> __tablename__ = 'country'
>>
>>
>> class User(Base):
>> __tablename__ = 'user'
>>
>> country_id = sa.Column(sa.Integer,
>> sa.ForeignKey('country.country_id'))
>> country = relationship("Country", uselist=False)
>>
>>
>> class MyModel(object):
>> def __init__(self, env):
>> self._engine = e
>> Base.metadata.bind = self._engine
>> Base.prepare(self._engine)
>>
>> def create_session_maker(self):
>> return sessionmaker(bind=self._engine)
>>
>> # This code is run in another module.
>> mymodel = MyModel("DEV")
>> Session = mymodel.create_session_maker()
>> session = Session()
>> l1 = session.query(User).all()
>>
>> print(User.country.property.primaryjoin)
>>
>>
>>
>>
>> |
>>>
>>> Any suggestions on how I might get around this?
>>>
>>>
>>> Thanks in advance,
>>> Blair
>>>
>>>
>>> --
>>> 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 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, visit
>> https://groups.google.com/d/topic/sqlalchemy/hZ3NVf1S2cE/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 https://groups.google.com/group/sqlalchemy.
>> For more options, visit 

[sqlalchemy] nested subqueries in a hybrid expression?

[Brian Cherinka]

I'm trying to build a hybrid property / expression in one of my SQLA base 
classes, and the expression side requires several subqueries and I cannot 
for the life of me figure out the correct syntax.  I've tried many 
different versions / syntaxes for the expression, using the SQL alchemy 
expression language, the ORM language but I can't get any of it work.  What 
am I missing here?  Here is my latest attempt. 

The setup is this.  I have a class called Cube, which is my base object. 
 For each object in that cube table, I need to grab an attribute from a 
table A and use that attribute to modify an array column from a second 
table B.  And I want to make this new array queryable, thus the 
hybridization.   

The equivalent raw SQL is 

select (select (array_agg(unwave.restw)) as restwarr from (select (unnest(w.
wavelength)/(1+n.z)) as restw from mangadatadb.wavelength as w) as unwave) 
from mangadatadb.cube as c join mangasampledb.manga_target as m on 
m.pk=c.manga_target_pk 
join mangasampledb.manga_target_to_nsa as t on t.manga_target_pk=m.pk join 
mangasampledb.nsa as n on n.pk=t.nsa_pk join mangadatadb.wavelength as w on 
w.pk=c.wavelength_pk limit 5;


The instance side is quite easy.  The class side is proving difficult.   
What is the proper way to write this?  I've read through the 
documentation countless times now and it hasn't helped much.  

class Cube
@hybrid_property
def restwave(self):
if self.target:
redshift = self.target.NSA_objects[0].z
wave = np.array(self.wavelength.wavelength)
restwave = wave/(1+redshift)
return restwave
else:
return None

@restwave.expression
def restwave(cls):
session = db.Session()
nsaz = session.query(sampledb.NSA.z.label('z')).\
join(sampledb.MangaTargetToNSA, sampledb.MangaTarget, Cube).\
filter(Cube.mangaid == cls.mangaid).subquery('nsaz', with_labels
=True)
unwave = session.query((func.unnest(Wavelength.wavelength)/(1+nsaz.c
.z)).label('restw')).subquery('unwave', with_labels=True)
restwave = session.query(func.array_agg(unwave.c.restw))

return restwave


Trying 
session.query(datadb.Cube.restwave).filter(datadb.Cube.mangaid==
'1-113520').all()

fails with 
AttributeError: 'Query' object has no attribute 'is_clause_element'


I've also tried this. 

@restwave.expression
def restwave(cls):
unwave = select([(func.unnest(Wavelength.wavelength)/(1+sampledb.NSA
.z)).label('restw')])
restwave = select([func.array_agg(unwave.c.restw).label('restwarr'
)]).select_from(
Cube.__table__.join(Wavelength.wavelength).
join(sampledb.MangaTarget).
join(sampledb.MangaTargetToNSA).
join(sampledb.NSA)
)
   return restwave


and this fails

In [6]: datadb.Cube.restwave
---
ArgumentError Traceback (most recent call last)
 in ()
> 1 datadb.Cube.restwave


/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/ext/hybrid.pyc 
in __get__(self, instance, owner)
738 def __get__(self, instance, owner):
739 if instance is None:
--> 740 return self.expr(owner)
741 else:
742 return self.fget(instance)


/Users/Brian/Work/sdss/repo/sdss/sdss_python_module/marvin/python/sdss/
internal/database/utah/mangadb/DataModelClasses.pyc in restwave(cls)
454 # unwave = 
select([(func.unnest(Wavelength.wavelength)/(1+sampledb.NSA.z)).label('restw')])
455 # restwave = 
select([func.array_agg(unwave.c.restw).label('restwarr')]).select_from(
--> 456 # Cube.__table__.join(Wavelength.wavelength).
457 # join(sampledb.MangaTarget).
458 # join(sampledb.MangaTargetToNSA).


/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable
.pyc in join(self, right, onclause, isouter)
350 """
351
--> 352 return Join(self, right, onclause, isouter)
353
354 def outerjoin(self, right, onclause=None):


/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable.pyc
 
in __init__(self, left, right, onclause, isouter)
658 """
659 self.left = _interpret_as_from(left)
--> 660 self.right = _interpret_as_from(right).self_group()
661
662 if onclause is None:


/Users/Brian/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable
.pyc in _interpret_as_from(element)
 47 return insp.selectable
 48 except AttributeError:
---> 49 raise exc.ArgumentError("FROM expression expected")
 50
 51

ArgumentError: FROM expression expected



-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from 

Re: [sqlalchemy] relationship with DeferredReflection raises NoForeignKeysError

[Mike Bayer]

On 07/29/2016 12:27 PM, bsdz wrote:

Hi

I'm trying to use DeferredReflection to encapsulate my data model so
that it can easily be instantiated for different environments. However,
I am having trouble creating relationships with a NoForeignKeysError
being raised. I am guessing it is because the table metadata generation
is being deferred, there's not enough information to create the foreign
key information and therefore the relationships.

I've simplified my code to the following:

|
importsqlalchemy assa
fromsqlalchemy.orm importsessionmaker,relationship
fromsqlalchemy.ext.declarative importdeclarative_base,DeferredReflection


Base=declarative_base(cls=DeferredReflection)

classCountry(Base):
__tablename__ ='country'

classUser(Base):
__tablename__ ='user'

country_id =sa.Column(sa.Integer,sa.ForeignKey('country.country_id'))
country =relationship("Country",uselist=False)

classMyModel(object):
def__init__(self,env):
self._engine =sa.create_engine("...")
Base.metadata.bind =self._engine
Base.prepare(self._engine)

defcreate_session_maker(self):
returnsessionmaker(bind=self._engine)

# This code is run in another module.
mymodel =MyModel("DEV")
Session=model.create_session_maker()
session =Session()
l1 =session.query(User).all()
|

The following error comes back:

|
NoForeignKeysError:Couldnotdetermine join condition between parent/child
tables on relationship User.country -there are noforeign keys linking
these tables.Ensurethat referencing columns are associated witha
ForeignKeyorForeignKeyConstraint,orspecify a 'primaryjoin'expression.


can't reproduce.  Need information on database in use, and if distinct 
schemas are involved.  See below, and perhaps try to modify this test 
case in your environment to reproduce your failure.



import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection

e = sa.create_engine("sqlite://", echo=True)

e.execute("""
create table country (
  country_id integer primary key
)
""")

e.execute("""
create table user (
user_id integer primary key,
country_id integer,
foreign key (country_id) references country(country_id)
)
""")


Base = declarative_base(cls=DeferredReflection)


class Country(Base):
__tablename__ = 'country'


class User(Base):
__tablename__ = 'user'

country_id = sa.Column(sa.Integer, sa.ForeignKey('country.country_id'))
country = relationship("Country", uselist=False)


class MyModel(object):
def __init__(self, env):
self._engine = e
Base.metadata.bind = self._engine
Base.prepare(self._engine)

def create_session_maker(self):
return sessionmaker(bind=self._engine)

# This code is run in another module.
mymodel = MyModel("DEV")
Session = mymodel.create_session_maker()
session = Session()
l1 = session.query(User).all()

print(User.country.property.primaryjoin)





|

Any suggestions on how I might get around this?


Thanks in advance,
Blair


--
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 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 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] relationship with DeferredReflection raises NoForeignKeysError

[Blair Azzopardi]

Interesting. Yes each table was in a different schema in pre-simplified
code and I am using SQL server via pyodbc from Linux. I'll try and get a
more complete test case shortly.

On 29 Jul 2016 5:53 p.m., "Mike Bayer"  wrote:

>
>
> On 07/29/2016 12:27 PM, bsdz wrote:
>
>> Hi
>>
>> I'm trying to use DeferredReflection to encapsulate my data model so
>> that it can easily be instantiated for different environments. However,
>> I am having trouble creating relationships with a NoForeignKeysError
>> being raised. I am guessing it is because the table metadata generation
>> is being deferred, there's not enough information to create the foreign
>> key information and therefore the relationships.
>>
>> I've simplified my code to the following:
>>
>> |
>> importsqlalchemy assa
>> fromsqlalchemy.orm importsessionmaker,relationship
>> fromsqlalchemy.ext.declarative importdeclarative_base,DeferredReflection
>>
>>
>> Base=declarative_base(cls=DeferredReflection)
>>
>> classCountry(Base):
>> __tablename__ ='country'
>>
>> classUser(Base):
>> __tablename__ ='user'
>>
>> country_id =sa.Column(sa.Integer,sa.ForeignKey('country.country_id'))
>> country =relationship("Country",uselist=False)
>>
>> classMyModel(object):
>> def__init__(self,env):
>> self._engine =sa.create_engine("...")
>> Base.metadata.bind =self._engine
>> Base.prepare(self._engine)
>>
>> defcreate_session_maker(self):
>> returnsessionmaker(bind=self._engine)
>>
>> # This code is run in another module.
>> mymodel =MyModel("DEV")
>> Session=model.create_session_maker()
>> session =Session()
>> l1 =session.query(User).all()
>> |
>>
>> The following error comes back:
>>
>> |
>> NoForeignKeysError:Couldnotdetermine join condition between parent/child
>> tables on relationship User.country -there are noforeign keys linking
>> these tables.Ensurethat referencing columns are associated witha
>> ForeignKeyorForeignKeyConstraint,orspecify a 'primaryjoin'expression.
>>
>
> can't reproduce.  Need information on database in use, and if distinct
> schemas are involved.  See below, and perhaps try to modify this test case
> in your environment to reproduce your failure.
>
>
> import sqlalchemy as sa
> from sqlalchemy.orm import sessionmaker, relationship
> from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
>
> e = sa.create_engine("sqlite://", echo=True)
>
> e.execute("""
> create table country (
>   country_id integer primary key
> )
> """)
>
> e.execute("""
> create table user (
> user_id integer primary key,
> country_id integer,
> foreign key (country_id) references country(country_id)
> )
> """)
>
>
> Base = declarative_base(cls=DeferredReflection)
>
>
> class Country(Base):
> __tablename__ = 'country'
>
>
> class User(Base):
> __tablename__ = 'user'
>
> country_id = sa.Column(sa.Integer, sa.ForeignKey('country.country_id'))
> country = relationship("Country", uselist=False)
>
>
> class MyModel(object):
> def __init__(self, env):
> self._engine = e
> Base.metadata.bind = self._engine
> Base.prepare(self._engine)
>
> def create_session_maker(self):
> return sessionmaker(bind=self._engine)
>
> # This code is run in another module.
> mymodel = MyModel("DEV")
> Session = mymodel.create_session_maker()
> session = Session()
> l1 = session.query(User).all()
>
> print(User.country.property.primaryjoin)
>
>
>
>
> |
>>
>> Any suggestions on how I might get around this?
>>
>>
>> Thanks in advance,
>> Blair
>>
>>
>> --
>> 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 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, visit
> https://groups.google.com/d/topic/sqlalchemy/hZ3NVf1S2cE/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 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 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 

[sqlalchemy] relationship with DeferredReflection raises NoForeignKeysError

[bsdz]

Hi

I'm trying to use DeferredReflection to encapsulate my data model so that 
it can easily be instantiated for different environments. However, I am 
having trouble creating relationships with a NoForeignKeysError being 
raised. I am guessing it is because the table metadata generation is being 
deferred, there's not enough information to create the foreign key 
information and therefore the relationships.

I've simplified my code to the following:

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection


Base = declarative_base(cls=DeferredReflection)
  
class Country(Base):
__tablename__ = 'country'

class User(Base):
__tablename__ = 'user'

country_id = sa.Column(sa.Integer, sa.ForeignKey('country.country_id'))
country = relationship("Country", uselist=False)

class MyModel(object):
def __init__(self, env):
self._engine = sa.create_engine("...")
Base.metadata.bind = self._engine
Base.prepare(self._engine)

def create_session_maker(self):
return sessionmaker(bind=self._engine)
 
# This code is run in another module.
mymodel = MyModel("DEV")
Session = model.create_session_maker()
session = Session()
l1 = session.query(User).all() 

The following error comes back: 

NoForeignKeysError: Could not determine join condition between parent/child 
tables on relationship User.country - there are no foreign keys linking these 
tables. Ensure that referencing columns are associated with a ForeignKey or 
ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Any suggestions on how I might get around this?


Thanks in advance,
Blair


-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Parameter injection issue upon db engine creation (tested with db2)

[Mike Bayer]

Hello -

This issue is specific to the workings of the dialect, in that it is 
concatenating the URL components into a single string for the DBAPI 
connect function.  As ibm_db_sa is not part of SQLAlchemy, you'd need to 
report this issue to them.


However, the same technique you refer to here can be applied to those 
SQLAlchemy dialects that do construct a single-string URL, which from a 
scan applies to:  connectors/pyodbc.py, mssql/adodbapi.py.  so i've 
created 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3762/semicolon-injection-for-a-password-passed. 
 thanks!







On 07/29/2016 10:05 AM, Miklós Zakar wrote:

Hi,

I discovered an issue in db engine creation code.
when the code prepares the db connection string it fails to escape the
semicolon char. This makes it possible to inject arbitrary parameters in
the connection string.
Under some circumstances this can be a security issue.

PoC:
I created two db2 instances in docker one is listening on  tcp/5
(default) and the other is on tcp/50001.

ActivePython 2.7.8.10 (ActiveState Software Inc.) based on
Python 2.7.8 (default, Jul  2 2014, 19:48:49) [MSC v.1500 64 bit
(AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.

import sqlalchemy
from sqlalchemy import *
import ibm_db_sa
from urllib import quote
import sys

*pw="aaa"*
db2 =

sqlalchemy.create_engine('ibm_db_sa://db2inst1:%s@192.168.99.100/test'
%  quote(pw))

conn = db2.connect()
conn.execute("select host_name from

TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO").fetchall()
*[(u'b5b9411e889e',)]*

conn.close()

*pw="aaa;PORT=50001"*
db2 =

sqlalchemy.create_engine('ibm_db_sa://db2inst1:%s@192.168.99.100/test'
%  quote(pw))

conn = db2.connect()
conn.execute("select host_name from

TABLE(SYSPROC.ENV_GET_SYS_INFO()) AS SYSTEMINFO").fetchall()
*[(u'e6aa666c18bf',)]*

conn.close()
sqlalchemy.__version__

'0.9.8'

As you can see I was able to trick the program to connect to an
alternative database via the password. (Which is a realistic vector I
think.)

My sqlalchemy version is not the latest because I installed it from the
ActiveState repo. If you've already fixed this then ignore my message.
Unfortunately I don't have time to further test this with other versions
or DB drivers.

Regards

Miklós

--
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 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 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] refresh_flush is not triggered when only the primary key is updated

[Mike Bayer]

Thanks for the great test case and this would be appropriate to be 
posted as a bug, since it is a complete description.


So, the reason the primary key is not included right now is because 
primary keys are populated in many different ways on the object, meaning 
if your code relies upon refresh_flush(), it will break if you switch to 
another database, or use an old version of Postgresql that doesn't 
support RETURNING, or the table/dialect is set up to no longer use 
RETURNING, etc.   The primary key is not actually populated at that 
point the way the other defaults are (it was already populated 
previously, so I guess to the event consumer, it doesn't make that much 
difference).


Looking at where refresh_flush is called, there is still inconsistency 
for other kinds of defaults too; if you use eager_defaults with a MySQL 
database and server-side defaults it looks like you'd get called in the 
ordinary refresh() event anyway (that is, if you try to eager_defaults 
against a server_default="0", a Postgresql DB will call refresh_flush, a 
MySQL DB will call refresh()).


Still, a primary key that's an autoincrement will never come back for 
MySQL within either of these events - there's no RETURNING for MySQL 
(and others).


refresh_flush() is very new and not widely used and it is likely 
harmless to just add what we have here (not to mention document it 
either way).  But for any change I'd first ask, what are you trying to 
do?   The primary keys are excluded from "refresh" because they are not 
usually a part of that concept, primary keys are "first class" and 
always get pulled no matter what, you can grab them in after_insert(), 
for example.






On 07/29/2016 04:25 AM, Lenar Imamutdinov wrote:

Hello!

Looks like the refresh_flush event is not triggered when the only field
to update after flush is the primary key fed from RETURNING clause.
However it should, as far as I understand what is mentioned in the
documentation.

Environment: SQLAlchemy 1.0.14, PostgreSQL 9.5, Python 3.4

Here is how to reproduce this problem:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy import event

Base = declarative_base()

def receive_refresh_flush(target, context, attrs):
print('refresh_flush received')

class Test(Base):
__tablename__ = 'refresh_flush_test'
id = Column(Integer, primary_key=True)
# uncomment the field below to receive the event
# dummy = Column(Integer, default=0)

engine = create_engine('postgresql://test:test@localhost:5432/test')
Base.metadata.create_all(engine)
session = Session(engine)

event.listen(Test, 'refresh_flush', receive_refresh_flush)

obj = Test()
session.add(obj)
session.commit()

--
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 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 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Parameter injection issue upon db engine creation (tested with db2)

[Miklós Zakar]

Hi,

I discovered an issue in db engine creation code.
when the code prepares the db connection string it fails to escape the 
semicolon char. This makes it possible to inject arbitrary parameters in 
the connection string.
Under some circumstances this can be a security issue.

PoC:
I created two db2 instances in docker one is listening on  tcp/5 
(default) and the other is on tcp/50001.

ActivePython 2.7.8.10 (ActiveState Software Inc.) based on
Python 2.7.8 (default, Jul  2 2014, 19:48:49) [MSC v.1500 64 bit (AMD64)] 
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> from sqlalchemy import *
>>> import ibm_db_sa
>>> from urllib import quote
>>> import sys
>>>
>>>* pw="aaa"*
>>> db2 = 
sqlalchemy.create_engine('ibm_db_sa://db2inst1:%s@192.168.99.100/test' %  
quote(pw))
>>> conn = db2.connect()
>>> conn.execute("select host_name from TABLE(SYSPROC.ENV_GET_SYS_INFO()) 
AS SYSTEMINFO").fetchall()
*[(u'b5b9411e889e',)]*
>>> conn.close()
>>>
>>>* pw="aaa;PORT=50001"*
>>> db2 = 
sqlalchemy.create_engine('ibm_db_sa://db2inst1:%s@192.168.99.100/test' %  
quote(pw))
>>> conn = db2.connect()
>>> conn.execute("select host_name from TABLE(SYSPROC.ENV_GET_SYS_INFO()) 
AS SYSTEMINFO").fetchall()
*[(u'e6aa666c18bf',)]*
>>> conn.close()
>>> sqlalchemy.__version__
'0.9.8'

As you can see I was able to trick the program to connect to an alternative 
database via the password. (Which is a realistic vector I think.)

My sqlalchemy version is not the latest because I installed it from the 
ActiveState repo. If you've already fixed this then ignore my message. 
Unfortunately I don't have time to further test this with other versions or 
DB drivers.

Regards

Miklós

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Generating models for Flask-AppBuilder using flask-sqlacodegen

[Aleksandr Blekh]

Hi All,

I'm a beginner in Python and Flask ecosystems, trying to create a small 
proof-of-concept Web application for a research project. I'm using Debian 
Linux 7.9, PostgreSQL 9.5, SQLAlchemy (latest) and Flask-AppBuilder 
(latest). Since creating models manually is tedious and error-prone, I 
searched the mighty Internet and discovered the "flask-sqlacodegen" project 
(note that this a fork of "sqlacodegen" with improved features for Flask 
users). I installed "flask-sqlqcodegen" from GitHub (cloned repo and then 
ran "python setup.py install"). However, when trying to use it to generate 
models, it produces an error, as follows:

> sqlacodegen postgresql+psycopg2://USER:PASS@HOST/DBNAME --flask

Traceback (most recent call last):
  File "/usr/local/bin/sqlacodegen", line 9, in 
load_entry_point('sqlacodegen==1.1.5.pre2', 'console_scripts', 
'sqlacodegen')()
  File 
"/usr/local/lib/python2.7/dist-packages/sqlacodegen-1.1.5.pre2-py2.7.egg/sqlacodegen/main.py",
 
line 57, in main
args.flask, fkcols)
  File 
"/usr/local/lib/python2.7/dist-packages/sqlacodegen-1.1.5.pre2-py2.7.egg/sqlacodegen/codegen.py",
 
line 597, in __init__
model = ModelClass(table, links[table.name], inflect_engine, not 
nojoined)
  File 
"/usr/local/lib/python2.7/dist-packages/sqlacodegen-1.1.5.pre2-py2.7.egg/sqlacodegen/codegen.py",
 
line 319, in __init__
relationship_ = ManyToOneRelationship(self.name, target_cls, 
constraint, inflect_engine)
  File 
"/usr/local/lib/python2.7/dist-packages/sqlacodegen-1.1.5.pre2-py2.7.egg/sqlacodegen/codegen.py",
 
line 455, in __init__
colname = constraint.columns[0]
  File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/_collections.py", 
line 194, in __getitem__
return self._data[key]
KeyError: 0

What is going on? Any help will be much appreciated. I hope that it's OK to 
repost this question to StackOverflow to increase exposure.

Sincerely,
Alex

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Implementation of params for textual SQL prevents its use in column names (use Python format as workaround)

[Simon King]

On Thu, Jul 28, 2016 at 4:33 AM, Andrew M  wrote:
> Okay, thanks Mike for your comprehensive reply. There is still so much to
> learn ... *sigh*.
>
> Perhaps it's worth including a sentence or two in the docs, helping
> overconfident people like myself to understand the benefits / importance of
> params (that it's not just .format)?
>

If you want to filter your query by a variable column name, you can
either use getattr() on a mapped class, or the ".c" dictionary on the
underlying table. For example:

# If Table is a mapped class
colname = 'some_column'
column = getattr(Table, colname)
session.query(Table).filter(column == value)

# If Table is an instance of sqlalchemy.Table
colname = 'some_column'
column = Table.c[colname]
session.query(Table).filter(column == value)

Hope that helps,

Simon

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] refresh_flush is not triggered when only the primary key is updated

[Lenar Imamutdinov]

Hello!

Looks like the refresh_flush event is not triggered when the only field to 
update after flush is the primary key fed from RETURNING clause. However it 
should, as far as I understand what is mentioned in the documentation.

Environment: SQLAlchemy 1.0.14, PostgreSQL 9.5, Python 3.4

Here is how to reproduce this problem:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy import event

Base = declarative_base()

def receive_refresh_flush(target, context, attrs):
print('refresh_flush received')

class Test(Base):
__tablename__ = 'refresh_flush_test'
id = Column(Integer, primary_key=True)
# uncomment the field below to receive the event
# dummy = Column(Integer, default=0)

engine = create_engine('postgresql://test:test@localhost:5432/test')
Base.metadata.create_all(engine)
session = Session(engine)

event.listen(Test, 'refresh_flush', receive_refresh_flush)

obj = Test()
session.add(obj)
session.commit()

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.