date:20190322

Re: [sqlalchemy] binding parameters in quotes

2019-03-22 Thread Mike Bayer

On Fri, Mar 22, 2019 at 1:59 PM Victor Olex
 wrote:
>
> It will however produce parameter for certain string literals in a valid 
> query. It may be a minute detail, but since you have that regex for detecting 
> parameters it might well avoid that (unless there is a case).
>
> >>> from sqlalchemy.sql import text
> >>> q = text("SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'")
> >>> print(q.compile())
> SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'
> >>> print(q.compile().params)
> {'param1': None, 'A': None}

Hi Victor -

yes, colons may appear in string literals, and on the PostgreSQL
backend they are also part of PG's shorthand CAST operator. so there
is a need to be able to render a colon character in text().  This is
achieved by escaping the colon with a backslash, as documented at
https://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=text#sqlalchemy.sql.expression.text
which illustrates a colon inside of a literal string.  Hope this
helps.


>
>
> On Thursday, March 21, 2019 at 4:11:45 PM UTC-4, Mike Bayer wrote:
>>
>> On Thu, Mar 21, 2019 at 3:33 PM Victor Olex
>>  wrote:
>> >
>> > Thanks Mike, though the question is valid - why does regex in SQLAlchemy 
>> > allow for discovering parameter token inside quotes? Have you seen a 
>> > legitimate case for that?
>>
>> the regex in SQLAlchemy is not a SQL parser, it's just putting bound
>> parameters where the user typed them. It does not detect invalid
>> SQL, that's the database's job.
>>
>> >
>> > On Wednesday, March 20, 2019 at 9:58:58 AM UTC-4, Mike Bayer wrote:
>> >>
>> >> On Wed, Mar 20, 2019 at 7:59 AM mdob  wrote:
>> >> >
>> >> > Hi,
>> >> >
>> >> >
>> >> > Is it correct behavior that parameter placeholders in quotes e.g. 
>> >> > SELECT * FROM Artist WHERE Name LIKE "%:pattern%" are recognized as 
>> >> > valid parameters?
>> >> >
>> >> >
>> >> > from sqlalchemy.sql import text
>> >> > from sqlalchemy.dialects import sqlite
>> >> > from sqlalchemy import create_engine
>> >> > engine = create_engine('sqlite:home/mike/Chinook.sqlite', echo=True)
>> >> >
>> >> >
>> >> > s = 'SELECT * FROM Artist WHERE Name LIKE "%:pattern%"'
>> >> > q = text(s)
>> >> >
>> >> > c = q.compile()
>> >> > print c.params
>> >> >
>> >> > {'pattern': None}
>> >> >
>> >> >
>> >> > If parameter is provided
>> >> > engine.execute(q, {'pattern': 'foo'})
>> >> >
>> >> > engine echo:
>> >> >
>> >> > 2019-03-20 12:44:14,668 INFO sqlalchemy.engine.base.Engine SELECT * 
>> >> > FROM Artist WHERE Name LIKE '%?'
>> >> > 2019-03-20 12:44:14,669 INFO sqlalchemy.engine.base.Engine ('foo',)
>> >> >
>> >> > and error is raised
>> >> >
>> >> >
>> >> > ProgrammingError: (sqlite3.ProgrammingError) Incorrect number of 
>> >> > bindings supplied. The current statement uses 0, and there are 1 
>> >> > supplied. [SQL: u'SELECT * FROM Artist WHERE Name LIKE "%?%"'] 
>> >> > [parameters: ('foo',)] (Background on this error at: 
>> >> > http://sqlalche.me/e/f405)
>> >> >
>> >> >
>> >> > When executed without parameters
>> >> >
>> >> > engine.execute(q, {'pattern': 'foo'})
>> >> >
>> >> > different error is raised
>> >> >
>> >> >
>> >> > StatementError: (sqlalchemy.exc.InvalidRequestError) A value is 
>> >> > required for bind parameter 'pattern' [SQL: u'SELECT * FROM Artist 
>> >> > WHERE Name LIKE "%?%"'] (Background on this error at: 
>> >> > http://sqlalche.me/e/cd3x)
>> >> >
>> >> > It feels to me like finding parameters in 
>> >> > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L895
>> >> >  should exclude placeholders inside quotes.
>> >>
>> >> This is not how bound parameters work in relational databases.  Bound
>> >> parameters are not Python substitution strings, they are more often
>> >> than not processed by the server, or at least have to behave as though
>> >> they were.  This means you cannot assume a parameter is substituted
>> >> inside of a value as you are doing here.  The correct syntax for what
>> >> you are trying to do is:
>> >>
>> >> s = 'SELECT * FROM Artist WHERE Name LIKE '%' || :pattern || '%'
>> >>
>> >> that is, the concatenation you are doing is explicit in SQL.
>> >>
>> >>
>> >> >
>> >> > Michal
>> >> >
>> >> > --
>> >> > 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+...@googlegroups.com.
>> >> > To post to this group, send email to sqlal...@googlegroups.com.
>> >> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> >> > For more options, visit https://groups.google.com/d/optout.
>> >
>> > --
>> >

Re: [sqlalchemy] binding parameters in quotes

2019-03-22 Thread Victor Olex

It will however produce parameter for certain string literals in a valid 
query. It may be a minute detail, but since you have that regex for 
detecting parameters it might well avoid that (unless there is a case).

>>> from sqlalchemy.sql import text
>>> q = text("SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'")
>>> print(q.compile())
SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'
>>> print(q.compile().params)
{'param1': None, 'A': None}


On Thursday, March 21, 2019 at 4:11:45 PM UTC-4, Mike Bayer wrote:
>
> On Thu, Mar 21, 2019 at 3:33 PM Victor Olex 
> > wrote: 
> > 
> > Thanks Mike, though the question is valid - why does regex in SQLAlchemy 
> allow for discovering parameter token inside quotes? Have you seen a 
> legitimate case for that? 
>
> the regex in SQLAlchemy is not a SQL parser, it's just putting bound 
> parameters where the user typed them. It does not detect invalid 
> SQL, that's the database's job. 
>
> > 
> > On Wednesday, March 20, 2019 at 9:58:58 AM UTC-4, Mike Bayer wrote: 
> >> 
> >> On Wed, Mar 20, 2019 at 7:59 AM mdob  wrote: 
> >> > 
> >> > Hi, 
> >> > 
> >> > 
> >> > Is it correct behavior that parameter placeholders in quotes e.g. 
> SELECT * FROM Artist WHERE Name LIKE "%:pattern%" are recognized as valid 
> parameters? 
> >> > 
> >> > 
> >> > from sqlalchemy.sql import text 
> >> > from sqlalchemy.dialects import sqlite 
> >> > from sqlalchemy import create_engine 
> >> > engine = create_engine('sqlite:home/mike/Chinook.sqlite', 
> echo=True) 
> >> > 
> >> > 
> >> > s = 'SELECT * FROM Artist WHERE Name LIKE "%:pattern%"' 
> >> > q = text(s) 
> >> > 
> >> > c = q.compile() 
> >> > print c.params 
> >> > 
> >> > {'pattern': None} 
> >> > 
> >> > 
> >> > If parameter is provided 
> >> > engine.execute(q, {'pattern': 'foo'}) 
> >> > 
> >> > engine echo: 
> >> > 
> >> > 2019-03-20 12:44:14,668 INFO sqlalchemy.engine.base.Engine SELECT * 
> FROM Artist WHERE Name LIKE '%?' 
> >> > 2019-03-20 12:44:14,669 INFO sqlalchemy.engine.base.Engine ('foo',) 
> >> > 
> >> > and error is raised 
> >> > 
> >> > 
> >> > ProgrammingError: (sqlite3.ProgrammingError) Incorrect number of 
> bindings supplied. The current statement uses 0, and there are 1 supplied. 
> [SQL: u'SELECT * FROM Artist WHERE Name LIKE "%?%"'] [parameters: ('foo',)] 
> (Background on this error at: http://sqlalche.me/e/f405) 
> >> > 
> >> > 
> >> > When executed without parameters 
> >> > 
> >> > engine.execute(q, {'pattern': 'foo'}) 
> >> > 
> >> > different error is raised 
> >> > 
> >> > 
> >> > StatementError: (sqlalchemy.exc.InvalidRequestError) A value is 
> required for bind parameter 'pattern' [SQL: u'SELECT * FROM Artist WHERE 
> Name LIKE "%?%"'] (Background on this error at: http://sqlalche.me/e/cd3x) 
>
> >> > 
> >> > It feels to me like finding parameters in 
> https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L895
>  
> should exclude placeholders inside quotes. 
> >> 
> >> This is not how bound parameters work in relational databases.  Bound 
> >> parameters are not Python substitution strings, they are more often 
> >> than not processed by the server, or at least have to behave as though 
> >> they were.  This means you cannot assume a parameter is substituted 
> >> inside of a value as you are doing here.  The correct syntax for what 
> >> you are trying to do is: 
> >> 
> >> s = 'SELECT * FROM Artist WHERE Name LIKE '%' || :pattern || '%' 
> >> 
> >> that is, the concatenation you are doing is explicit in SQL. 
> >> 
> >> 
> >> > 
> >> > Michal 
> >> > 
> >> > -- 
> >> > 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+...@googlegroups.com. 
> >> > To post to this group, send email to sqlal...@googlegroups.com. 
> >> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> >> > For more options, visit https://groups.google.com/d/optout. 
> > 
> > -- 
> > 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+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at

Re: [sqlalchemy] self relationship via intermediate table

2019-03-22 Thread Mike Bayer

On Fri, Mar 22, 2019 at 10:53 AM Konstantin Naumov  wrote:
>
> Great, thank you!
>
> I thought that I can use a field of relationship() - “invited_by" in User 
> model for emitting the similar query, but I'm getting an empty list.

take a look at SQL being emitted with echo=True


>
>
> On Mar 20, 2019, at 12:55 AM, Mike Bayer  wrote:
>
> On Tue, Mar 19, 2019 at 10:23 AM kosta  wrote:
>
>
> Hello Mike,
> Thank you for your response!
>
>
> First of all, I'm apologize, I have lack knowledge in sql. I guess my SQL 
> should be as:
> Get all users invited by specific user:
>
> SELECT u.name AS "sender", i.name AS "invitee"
> FROM invitation inv
> LEFT JOIN user u ON inv.sender_id=u.id
> LEFT JOIN user i ON inv.invitee_id=i.id
> WHERE inv.sender_id=?;
>
> Get user who invited specific user:
>
> SELECT u.name AS "invited_by", i.name AS "invitee"
> FROM invitation inv
> LEFT JOIN user u ON inv.sender_id=u.id
> LEFT JOIN user i ON inv.invitee_id=i.id
> WHERE inv.invitee_id=?;
>
>
> here's the form of the first one and the second is basically the same idea:
>
> from sqlalchemy.orm import aliased
>
> u = aliased(User, "u")
> i = aliased(User, "i")
>
> q = session.query(
>u.name.label("sender"),
>i.name.label("invitee")
> ).select_from(Invitation).\
>outerjoin(u, Invitation.sender).\
>outerjoin(i, Invitation.invitee).\
>filter(Invitation.sender_id=5)
>
>
>
> понедельник, 18 марта 2019 г., 17:48:26 UTC+3 пользователь Mike Bayer написал:
>
>
> On Sat, Mar 16, 2019 at 9:33 AM kosta  wrote:
>
>
> Hello everyone!
>
> I've designed invitation model
> class User(Base):
> __tablename__ = 'user'
>
> id = Column(Integer, primary_key=True)
> name = Column(String(64))
> email = Column(String(64))
> class Invitation(Base):
> __tablename__ = 'invitation'
>
> id = Column(Integer, primary_key=True)
> sender_id = Column(Integer, ForeignKey('user.id'))
> invitee_id = Column(Integer, ForeignKey('user.id'), unique=True)
> sender = relationship('User', foreign_keys=[sender_id], backref='invite_list')
> invitee = relationship('User', foreign_keys=[invitee_id], 
> backref='invited_by', uselist=False)
>
> email = Column(String)
> phone = Column(String)
> token = Column(String)
>
> My logic is:
>
> 1. Create a new record in Invitation table: sender_id - current user, email 
> or phone and unique generated token.
> 2. Create a new record in User table keep received token, commit it.
> 3. Find a record in Invitation table by filter token and update filed 
> invitee_id == new_user.id
>
>
> My problem is backref return value for invited_by - return (of course) 
> Invitation record.
> My question is whether I've possibility return for invited_by User record via 
> Invitation table or not?
>
>
> you can find any record via anything, what SQL would you like to emit please ?
>
>
>
>
>
> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> 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 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 -
> 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 a topic in the Google 
> Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit 
> https://groups.google.com/d/topic/sqlalchemy/gv8QmGnl6lg/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

Re: [sqlalchemy] self relationship via intermediate table

2019-03-22 Thread Konstantin Naumov

Great, thank you!

I thought that I can use a field of relationship() - “invited_by" in User model 
for emitting the similar query, but I'm getting an empty list.


> On Mar 20, 2019, at 12:55 AM, Mike Bayer  wrote:
> 
> On Tue, Mar 19, 2019 at 10:23 AM kosta  > wrote:
>> 
>> Hello Mike,
>> Thank you for your response!
>> 
>> 
>> First of all, I'm apologize, I have lack knowledge in sql. I guess my SQL 
>> should be as:
>> Get all users invited by specific user:
>> 
>> SELECT u.name AS "sender", i.name AS "invitee"
>> FROM invitation inv
>> LEFT JOIN user u ON inv.sender_id=u.id
>> LEFT JOIN user i ON inv.invitee_id=i.id
>> WHERE inv.sender_id=?;
>> 
>> Get user who invited specific user:
>> 
>> SELECT u.name AS "invited_by", i.name AS "invitee"
>> FROM invitation inv
>> LEFT JOIN user u ON inv.sender_id=u.id
>> LEFT JOIN user i ON inv.invitee_id=i.id
>> WHERE inv.invitee_id=?;
> 
> here's the form of the first one and the second is basically the same idea:
> 
> from sqlalchemy.orm import aliased
> 
> u = aliased(User, "u")
> i = aliased(User, "i")
> 
> q = session.query(
>u.name.label("sender"),
>i.name.label("invitee")
> ).select_from(Invitation).\
>outerjoin(u, Invitation.sender).\
>outerjoin(i, Invitation.invitee).\
>filter(Invitation.sender_id=5)
> 
>> 
>> 
>> понедельник, 18 марта 2019 г., 17:48:26 UTC+3 пользователь Mike Bayer 
>> написал:
>>> 
>>> On Sat, Mar 16, 2019 at 9:33 AM kosta  wrote:
 
 Hello everyone!
 
 I've designed invitation model
 class User(Base):
 __tablename__ = 'user'
 
 id = Column(Integer, primary_key=True)
 name = Column(String(64))
 email = Column(String(64))
 class Invitation(Base):
 __tablename__ = 'invitation'
 
 id = Column(Integer, primary_key=True)
 sender_id = Column(Integer, ForeignKey('user.id'))
 invitee_id = Column(Integer, ForeignKey('user.id'), unique=True)
 sender = relationship('User', foreign_keys=[sender_id], 
 backref='invite_list')
 invitee = relationship('User', foreign_keys=[invitee_id], 
 backref='invited_by', uselist=False)
 
 email = Column(String)
 phone = Column(String)
 token = Column(String)
 
 My logic is:
 
 1. Create a new record in Invitation table: sender_id - current user, 
 email or phone and unique generated token.
 2. Create a new record in User table keep received token, commit it.
 3. Find a record in Invitation table by filter token and update filed 
 invitee_id == new_user.id
 
 
 My problem is backref return value for invited_by - return (of course) 
 Invitation record.
 My question is whether I've possibility return for invited_by User record 
 via Invitation table or not?
>>> 
>>> you can find any record via anything, what SQL would you like to emit 
>>> please ?
>>> 
>>> 
>>> 
 
 
 --
 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+...@googlegroups.com.
 To post to this group, send email to sqlal...@googlegroups.com.
 Visit this group at https://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.
>> 
>> --
>> 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 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 - 
> 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 a topic in the Google 
> Groups

Re: [sqlalchemy] binding parameters in quotes

Re: [sqlalchemy] binding parameters in quotes

Re: [sqlalchemy] self relationship via intermediate table

Re: [sqlalchemy] self relationship via intermediate table

4 matches

Site Navigation

Mail list logo

Footer information