arbitrary strings and ints in where

[paul....@gmail.com]

Hi! I like this library. The where and joins kinda remind me of linq expressions in ef.

One thing I'm finding out about this and I'm not sure if this is possible or not but I'm not able to createarbitrary where int or strings expressions without passing a table column:

Works:
select.where = my_table.id == 1

Works:
tmp = 'abc'
select.where = my_table.id == tmp


Doesn't work:
tmp = 'abc'
select.where = tmp == 'abc'

What I would like to see in the where statement:
WHERE 'abc' = 'abc'

Same things goes for something like this:
select.where = 1 == 1

I would expect to see in the SQL:
WHERE 1 = 1

Is this something that's possible?

[Cédric Krier]

On 2019-03-17 19:42, paul....@gmail.com wrote:
> One thing I'm finding out about this and I'm not sure if this is possible or not but I'm not able to createarbitrary where int or strings expressions without passing a table column:
>

> Doesn't work:
> tmp = 'abc'
> select.where = tmp == 'abc'
>
> What I would like to see in the where statement:
> WHERE 'abc' = 'abc'
>
> Same things goes for something like this:
> select.where = 1 == 1
>
> I would expect to see in the SQL:
> WHERE 1 = 1
>
> Is this something that's possible?

Yes, you must use explicit Literal:

select.where = Literal(1) == Literal(1)



--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Brosefski]

Thank you!  I have one more question:

I'm not sure how I would do this but I was wondering if there was a programmatic way to select items from a list.

Ie:  I have a table:

table1 = Table("table1", "public")

and I have a list of fields names like so:

["field1", "field2"]

I would like to be able to feed that list rather than type out the select explicitly.  What this allows me change what I'm selecting dynamically.

Thanks,

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

[Cédric Krier]

On 2019-03-19 21:12, Brosefski wrote:
> I'm not sure how I would do this but I was wondering if there was a
> programmatic way to select items from a list.
>
> Ie: I have a table:
> table1 = Table("table1", "public")
>
> and I have a list of fields names like so:
> ["field1", "field2"]
>
> I would like to be able to feed that list rather than type out the select
> explicitly. What this allows me change what I'm selecting dynamically.

Yes you can. You could use getattr on Table instance but there is a risk
that the attribute name collide with an attribute of the class.
So the best option is to instantiate columns:

columns = [Column(table1, f) for f in ["field1", "field2"]]
query = table1.select(columns)

[Brosefski]

Thanks Cedric!

I do have one more question actually.  What is the technology you're using that allows direct parsing of where statements?  I find this interesting from python and I haven't come across another example of it or documentation on it.

[Cédric Krier]

On 2019-03-20 09:37, Brosefski wrote:
> I do have one more question actually. What is the technology you're using
> that allows direct parsing of where statements? I find this interesting
> from python and I haven't come across another example of it or
> documentation on it.

This is simply done by override the standard operators of Python in the
Expression class (which derive all SQL expressions)

[Brosefski]

Aha!  Right, operator overloading.  Thanks!

[Brosefski]

Hi Cedric, with [Column(table1, f) for f in ["field2", "field3"]], is it possible to do column aliases this way?

I'm looking at your codebase and it doesn't appear to be so.  I'm looking for Column(table, column_name, select_alias) so that "select_alias" is the column name for "column_name" in the result set.

Thank you,

[Nicolas Évrard]

* Brosefski [2019-03-21 09:07:55]:
> Hi Cedric,

Hello,

> with [Column(table1, f) for f in ["field2", "field3"]], is it
> possible to do column aliases this way?

You can do something like

[Column(table, f).as_(some_code) for f in …]

Regards,

--
Nicolas Évrard - B2CK SPRL
E-mail/Jabber: nicolas...@b2ck.com