Perfect, thank you Mike.
Best regards,
Matthew
On Wednesday, October 5, 2022 at 11:51:39 AM UTC-7 Mike Bayer wrote:
> Select does not have a hook for that particular spot in the SQL
> compilation, so unless you rewrote all of visit_select, your best bet is to
> stick with simple string
Select does not have a hook for that particular spot in the SQL compilation, so
unless you rewrote all of visit_select, your best bet is to stick with simple
string replacement.
this does not in any way preclude you from using the compiler extension, get
the text from the compiler then do
Hello,
I am generating Oracle PLSQL using sqlalchemy core.
I would like to generate the following output:
SELECT foo, bar
BULK COLLECT INTO l_foos
FROM baz;
Is there a mechanism to insert a string after the select column list and
before the FROM?
In sql/compiler.py:visit_select
Yaakov Bressler wrote:
>
> *I saw the following Q posted on SO:*
>
> Difference between SQLAlchemy Select and Query API
> <https://stackoverflow.com/questions/72828293/difference-between-sqlalchemy-select-and-query-api>
> Not sure if this has been asked before, but in the S
On Sun, Jul 3, 2022, at 10:41 AM, Yaakov Bressler wrote:
> *I saw the following Q posted on SO:*
>
> Difference between SQLAlchemy Select and Query API
> <https://stackoverflow.com/questions/72828293/difference-between-sqlalchemy-select-and-query-api>
> Not sure if this
*I saw the following Q posted on SO:*
Difference between SQLAlchemy Select and Query API
<https://stackoverflow.com/questions/72828293/difference-between-sqlalchemy-select-and-query-api>
Not sure if this has been asked before, but in the SQLAlchemy docs
<https://docs.sqlalchemy.
there is a filter:
stmt = await session.execute(
select(PersonParticipant).where(
PersonParticipant.request_group.has(
GroupChat.admin_group
) == request.user.user_id,
)
)
get_admin_group =
On Tue, Nov 6, 2018 at 10:33 PM Paul Becotte wrote:
>
> Hi!
>
> I recently needed to use the select ... into ... construct to build a temp
> table in Redshift. I used the recipe
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SelectInto here to
> get a good idea of how to go about
Hi!
I recently needed to use the select ... into ... construct to build a temp
table in Redshift. I used the
recipe https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SelectInto
here to get a good idea of how to go about this, and was wondering if I
should contribute the final working
On 06/05/2017 05:52 PM, Uri Okrent wrote:
On Monday, May 29, 2017 at 12:27:51 PM UTC-4, Mike Bayer wrote:
I'm assuming this "invalidates the transaction" is on the Postgresql
side, e.g. you get "current transaction is aborted". There is a
simple
solution for that which is
On Monday, May 29, 2017 at 12:27:51 PM UTC-4, Mike Bayer wrote:
>
> I'm assuming this "invalidates the transaction" is on the Postgresql
> side, e.g. you get "current transaction is aborted". There is a simple
> solution for that which is to use a savepoint, which with the ORM is via
>
On 05/28/2017 11:16 AM, Uri Okrent wrote:
Background:
I have a postgres database containing objects with 1-N (parent-child)
relationships. Each object can have several of these types of
relationships (i.e., more than one parent). These are connected by
foreign keys with 'on delete set
Background:
I have a postgres database containing objects with 1-N (parent-child)
relationships. Each object can have several of these types of
relationships (i.e., more than one parent). These are connected by foreign
keys with 'on delete set null' since a child can lose a parent and that is
Thanks mike, that was a helpful explanation.
2017-03-06 18:59 GMT-03:00 mike bayer :
>
>
> On 03/06/2017 04:47 PM, Leonardo L. P. da Mata wrote:
>
>> Hello, thanks for the help.
>>
>> This makes sense but it looks like that the value is calculated twice,
>> one in the
On 03/06/2017 04:47 PM, Leonardo L. P. da Mata wrote:
Hello, thanks for the help.
This makes sense but it looks like that the value is calculated twice,
one in the query and the other accessing the property.
My idea is to have the result on the query return itself.
"lat" and "lng" here
Hello, thanks for the help.
This makes sense but it looks like that the value is calculated twice, one
in the query and the other accessing the property.
My idea is to have the result on the query return itself.
2017-03-06 18:31 GMT-03:00 mike bayer :
>
>
> On
On 03/06/2017 04:16 PM, Leonardo Mata wrote:
Hello, My applications does some ordering using the distance from
latitude and longitude haversine distance, i was able to calculate this
using @hybrid.method and @.*expression, but i can't output the
calculated distance:
/class
Hello, My applications does some ordering using the distance from latitude
and longitude haversine distance, i was able to calculate this using
@hybrid.method and @.*expression, but i can't output the calculated
distance:
*class PartnerAddress(db.Model, WithTimestampsModel, SerializeMixin):*
On 03/09/2016 01:38 PM, Alex Hall wrote:
Hi all,
I want to select * from a table, getting all columns. However, the
only rows I want are where the item number is distinct. I've got:
items = session.query(itemTable)\
.distinct()\
.limit(10)
But that doesn't apply "distinct" to just item_number.
Hi all,
I want to select * from a table, getting all columns. However, the
only rows I want are where the item number is distinct. I've got:
items = session.query(itemTable)\
.distinct()\
.limit(10)
But that doesn't apply "distinct" to just item_number. I'm not the
best with SQL in general or I'd
On Tue, Aug 25, 2015 at 6:43 PM, Abhishek Sharma abhisharma8...@gmail.com
wrote:
Hi Team,
We are executing select query using
self.session.query(Model).filter(filter_conditions).first()
Then we are storing the about query result in result variable.
Then we are trying to
Hi Team,
We are executing select query using
self.session.query(Model).filter(filter_conditions).first()
Then we are storing the about query result in result variable.
Then we are trying to update one of model attribute like
result.description=value
Even though we have not added
I am using session.query(Model).filter(conditions) but still getting
UnicodeErrors
On Wed, Aug 12, 2015 at 10:07 AM, Mike Bayer mike...@zzzcomputing.com
wrote:
On 8/12/15 9:55 AM, Abhishek Sharma wrote:
Hi Team,
We are not calling all or first method on got query object.
We
Hi Team,
We are not calling all or first method on got query object.
We are just passing query object to set method of python and we are getting
Unicode Error ASCII CODEC Can not decode this means I am not getting
Unicode Object from SQLAlchemy side.
Column('dlrprod_name_pri',
On 8/11/15 1:58 PM, Abhishek Sharma wrote:
Hi Team,
With asynchronous request my model object Unicode type
column not returning Unicode object but if I do same action using
synchronous I am getting Unicode object
SQLAlchemy has no asynchrnous API itself so this has to do
Hi Team,
With asynchronous request my model object Unicode type
column not returning Unicode object but if I do same action using
synchronous I am getting Unicode object
On 05-Aug-2015 11:43 PM, Abhishek Sharma abhisharma8...@gmail.com wrote:
thanks for your help.
its seems to
Hi Team,
I have created customized data type using TypeDecorator approach.
*from sqlalchemy import TypeDecorator, CLOB*
*class ForceUnicodeClob(TypeDecorator): impl = CLOB*
* def process_bind_param(self, value, dialect): if
isinstance(value, str):value =
On 8/5/15 9:24 AM, Abhishek Sharma wrote:
Hi Team,
I have created customized data type using TypeDecorator approach.
*from sqlalchemy import TypeDecorator, CLOB*
*class ForceUnicodeClob(TypeDecorator):
impl = CLOB*
* def process_bind_param(self, value, dialect):
if
that object already got saved in DB after session.commit(). After that i am
retrieving object which already stored in db. still same issue.
On Wed, Aug 5, 2015 at 7:41 PM, Mike Bayer mike...@zzzcomputing.com wrote:
On 8/5/15 9:24 AM, Abhishek Sharma wrote:
Hi Team,
I have created
Attached is a test script illustrating the code you have below along
with a round trip verification using a simple Model class mapped to the
table. Please confirm this script works as expected, as it does here.
Assuming that works, determine what's different about your real-world
environment
thanks for your help.
its seems to be working. i will troubleshoot in my development environment.
On Wed, Aug 5, 2015 at 10:37 PM, Mike Bayer mike...@zzzcomputing.com
wrote:
Attached is a test script illustrating the code you have below along with
a round trip verification using a simple Model
applying convert_unicode to CLOB type does not have any effect. Still I am
getting str type object from sqlalchemy for CLOB type column
On Mon, Aug 3, 2015 at 1:27 PM, Mike Bayer mike...@zzzcomputing.com wrote:
On 8/3/15 1:04 PM, Abhishek Sharma wrote:
what about CLOB type? Unicode only
On 8/4/15 11:29 AM, Mike Bayer wrote:
On 8/4/15 7:41 AM, Abhishek Sharma wrote:
in case lot of overhead will be there so it is better to use that
column label only
well it doesn't work anyway because data from a CLOB is not in
cx_oracle's world a String, it's a LOB. The CLOB / NCLOB
On 8/4/15 7:41 AM, Abhishek Sharma wrote:
in case lot of overhead will be there so it is better to use that
column label only
well it doesn't work anyway because data from a CLOB is not in
cx_oracle's world a String, it's a LOB. The CLOB / NCLOB types for
cx_oracle are organized in their
is this followings two instructions compulsory while defining new type?
m.drop_all(e)
m.create_all(e)
this instructions are not feasible , because DB team already defined schema
and normal user can not drop and create table.
On Tue, Aug 4, 2015 at 8:59 PM, Mike Bayer mike...@zzzcomputing.com
On 8/4/15 1:41 PM, Abhishek Sharma wrote:
is this followings two instructions compulsory while defining new type?
m.drop_all(e)
m.create_all(e)
no that is just part of the demonstration script.
this instructions are not feasible , because DB team already defined
schema and normal user
what about CLOB type? Unicode only handles String type. Do i need to use
convert_unicode there?
On Mon, Aug 3, 2015 at 6:56 PM, Mike Bayer mike...@zzzcomputing.com wrote:
On 8/1/15 12:12 PM, Abhishek Sharma wrote:
Thanks for help. But still i have confusion over encoding and decoding
On 8/3/15 1:04 PM, Abhishek Sharma wrote:
what about CLOB type? Unicode only handles String type. Do i need to
use convert_unicode there?
if your CLOB expects non-ascii characters then yes.
though on Oracle I thought you really need to be using NCLOB for a col
that stores unicode.
On
On 8/1/15 12:12 PM, Abhishek Sharma wrote:
Thanks for help. But still i have confusion over encoding and decoding
procedure which will take place before retrieving and storing the
results in DB.
In case if i am not using convert_unicode option and data type is
String so python process will
Thanks for help. But still i have confusion over encoding and decoding
procedure which will take place before retrieving and storing the results
in DB.
In case if i am not using convert_unicode option and data type is String so
python process will give str object to sqlalchemy at the time of
We are using sqlalchemy version 0.7, python 2.7 and oracle Database.
We have ASCII as default python encoding and DB have ISO-8052 encoding. Our
DB tables contains some of characters which are out of ASCII range. So when
we are running query on those tables we are getting Unicode Decode error
On 7/29/15 2:23 PM, Abhishek Sharma wrote:
We are using sqlalchemy version 0.7, python 2.7 and oracle Database.
We have ASCII as default python encoding and DB have ISO-8052
encoding. Our DB tables contains some of characters which are out of
ASCII range. So when we are running query on
Thanks Michael,
I need a cross-dialect solution so if that's the best we have, I think I'll
go with the inefficient load-everything-and-sort-in-python method :)
On Tuesday, June 9, 2015 at 9:32:03 PM UTC+3, Michael Bayer wrote:
you'd need to look into recursive querying. PG can do this with
I have a self referencing table like the following:
class Employee(Base):
__tablename__ = t_employee
id = sa.Column(BigInteger, sa.Sequence('%s_id_seq' % __tablename__),
primary_key=True, nullable=False)
manager_id = sa.Column(BigInteger, sa.ForeignKey(t_employee.id,
ondelete='SET
you'd need to look into recursive querying. PG can do this with CTEs:
http://www.postgresql.org/docs/8.4/static/queries-with.html
here's a tutorial that IMO goes way too fast and complicated, but SQLA
can do the SELECT part of this at least:
I'm confused with how Query.with_lockmode('update') works in sqlalchemy.
My code is in attachment.
For testing, I start test.py in 1st terminal, then start test.py in 2nd
terminal.
so the 2nd termial will read the data modified by 1st test.py.
But the 2nd terminal's output contains L42 in
ying zhang zhy198...@gmail.com wrote:
I'm confused with how Query.with_lockmode('update') works in sqlalchemy.
My code is in attachment.
For testing, I start test.py in 1st terminal, then start test.py in 2nd
terminal.
so the 2nd termial will read the data modified by 1st test.py.
ok, I think I found the solution. Thought I'd post what I did and what I
tried; partly because it might help someone else and partly because someone
may have suggestions for a better way.
This is what worked:
my_function = func.my_function(...args...)
func_subquery = select(['id'],
I've googled around can't seem to find an answer to this, so hopefully
someone knows how to do it here.
I'm using PostgreSQL and I have a PL/PGSQL function that filters and
modifies a particular table based on a number of conditions and then
returns a set of rows as the result. This pattern
On Sep 20, 2012, at 11:49 AM, David McKeone wrote:
I've googled around can't seem to find an answer to this, so hopefully
someone knows how to do it here.
I'm using PostgreSQL and I have a PL/PGSQL function that filters and modifies
a particular table based on a number of conditions and
you can make a function act like a table by creating a select() out of it,
check out the fifth example at
http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html#functions
On Sep 20, 2012, at 1:09 PM, David McKeone wrote:
Hi M,
Is creating something in the database the only way to do it?
I need to select for update but locking the row only in one table in a
query with outer joins. I'm using PostgreSQL backend. It has to be outer
join, so I need FOR UPDATE OF [tablename]. How do I do that with SQLA?
So far I was using Query.with_lockmode(update), but I can't specify
the table
I'm trying to do a like statement in a query filter. I'm fine doing it one way
for instance
session.query(Table).filter(Table.path.like(C:\Test\%))
which would hopefully return all folders and files in the folder Test
but what if I want to do it the other way around and pass
sure it does, if you convert it to a SQL token first:
literal(C:\test\testfile.txt).like(Table.path + %)
or even
literal(C:\test\testfile.txt).startswith(Table.path)
On Nov 7, 2011, at 8:40 AM, Paul wrote:
I'm trying to do a like statement in a query filter. I'm fine doing it one way
for
On 11/04/2011 03:11 AM, Mark Erbaugh wrote:
On Nov 3, 2011, at 3:31 PM, werner wrote:
Mark,
On 11/03/2011 07:18 PM, Mark Erbaugh wrote:
Using the query object count() method generates a sub-query (as per the docs).
The docs say to use func.count to avoid the subquery. func.count seems to
Il 04/11/11 03.08, Mark Erbaugh ha scritto:
On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the docs).
The docs say to use func.count to avoid the subquery. func.count seems
On Nov 4, 2011, at 4:54 AM, Stefano Fontanelli wrote:
Il 04/11/11 03.08, Mark Erbaugh ha scritto:
On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the
docs). The docs
Using the query object count() method generates a sub-query (as per the docs).
The docs say to use func.count to avoid the subquery. func.count seems to
require a field object. Is there a way to generate a query that essentially
becomes 'select count(*) from table' using the ORM, not the SQL
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the docs).
The docs say to use func.count to avoid the subquery. func.count seems to
require a field object. Is there a way to generate a query that essentially
becomes 'select
Mark,
On 11/03/2011 07:18 PM, Mark Erbaugh wrote:
Using the query object count() method generates a sub-query (as per the docs).
The docs say to use func.count to avoid the subquery. func.count seems to
require a field object. Is there a way to generate a query that essentially
becomes
On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the
docs). The docs say to use func.count to avoid the subquery. func.count
seems to require a field object. Is there a way
On Nov 3, 2011, at 3:31 PM, werner wrote:
Mark,
On 11/03/2011 07:18 PM, Mark Erbaugh wrote:
Using the query object count() method generates a sub-query (as per the
docs). The docs say to use func.count to avoid the subquery. func.count
seems to require a field object. Is there a way to
to allow proper quoting of names the schema portion of the name is separate
from the name of the table:
class AttributeConversion(Base):
__tablename__ = 'attribute_conversion'
__table_args__ = {'schema':'test'}
On May 16, 2011, at 9:02 AM, Ivan wrote:
I have table from schema test
I have table from schema test
class AttributeConversion(Base):
__tablename__ = 'test.attribute_conversion'
How to select records from this table ?
sqlalchemy make sql:
:select * from test.attribute_conversion
but it's work.
right question must be :select * from test.attribute_conversion (
This is a duplicate of my stackoverflow question --
http://stackoverflow.com/questions/5602918/postgresql-select-null-values-in-sqlalchemy
Basically I have a (PostgreSQL) table --
test= create table people (name varchar primary key, marriage_status
varchar) ;
test= insert into people values
Use
people.married_status == None
to check for NULL
--
Mike Conley
--
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
hi list,
I have the following (elixir) definitions
class Invoice(Entity):
user_name = Field(Unicode(255))
item = ManyToOne(Item)
class Item(Entity):
item_id = Field(Integer, primary_key=True)
service_id = Field(Unicode(255), primary_key=True)
item_class =
its a little unfortunate that Invoice.item.distinct() is recursion overflowing
like that, but in general if you want to select distinct Item objects you'd be
saying query(Item).distinct().
not sure I understand what invoices that have distinct items means. I
understand invoices that have
Can someone show me the gist of how to construct an SA query that
produces SQL* of the form
SELECT ARRAY(SELECT ... FROM ... WHERE ...) as array_col
Thanks,
Michael
*
http://www.postgresql.org/docs/8.4/interactive/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
--
You received this
Hi all,
In version 0.6 seems the group_by property does nothing...
(Pdb) sql =
select([Verifica.c.codice,func.sum(Prestazione.c.importo).label('importo')])
(Pdb) print sql
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione
(Pdb)
On 4/27/2010 5:22 AM, jo wrote:
Hi all,
In version 0.6 seems the group_by property does nothing...
(Pdb) sql =
select([Verifica.c.codice,func.sum(Prestazione.c.importo).label('importo')])
(Pdb) print sql
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM verifica, prestazione
Lance Edgar wrote:
On 4/27/2010 5:22 AM, jo wrote:
Hi all,
In version 0.6 seems the group_by property does nothing...
(Pdb) sql =
select([Verifica.c.codice,func.sum(Prestazione.c.importo).label('importo')])
(Pdb) print sql
SELECT verifica.codice, sum(prestazione.importo) AS importo
FROM
Dear list,
I have been upgrading an application I wrote with SQLAlchemy 0.4 to
0.5.8. Currently I'm fighting a strange effect. In some places of my
application I can't insert data into the database any more. I get error
messages like:
IntegrityError: (IntegrityError) null value in column
Christoph Haas wrote:
So apparently SQLAlchemy tries to insert a new row with the id field
being None. This is obviously wrong because the field is supposed to
contain the next serial value. I enabled debugging and saw that before
the insert SQLAlchemy runs:
___SQL___
Hi. I am sort of a newbie on SQLAlchemy. Is there a way to do a query
with the ORM, and doing an as on the select.
For example:
class Example(Base):
name = Column(Unicode(512) )
query = session.query(Example.name AS foo)
query.all()
Thanks in advance..
', types.Integer(), ForeignKey('deals.id')),
Column('tagid', types.Integer(), ForeignKey
('tags.id')),
)
I want to select tag id, tag name and deal count (number of deals per
tag).
I tried to create the query in sqlalchemy select function
select([tags_table.c.name,tags_table.c.id, func.count
Hi.
Im still trying to use the select object from sqlalchemy, but i found a
strange (bug or not) behavior:
sql = select(columns=[self.fields], from_obj=self.tables,
whereclause=self.where, bind=self.req.cfg.engine, order_by= ' 1 ')
1) without order by, i get an error: AttributeError:
Recently I had a fairly complex SQL query I wanted to write out using
SQLAlchemy.
I ended up implementing it using select() so what I end up doing looks
something like:
parent=my_table.alias('parent')
child=my_table.alias('child')
j=parent.outerjoin(child, condition)
my_qry=select([my_table],
I am trying to get all of the rows in table A that do not have a match
in table B. I believe the problem is that I am using a text foreign key,
and for the rows I am looking for the field will still have a value, it
just won't match anything in table B.
To make things confusing the table
I am having trouble writing a sqlalchemy query which selects all rows
where a field equals the max for that field, eg
q =
session.query(Snapshot).filter(Snapshot.totalqty==func.max(Snapshot.totalqty))
When I try and get the results of the query, I get the error below.
How should I use
Hi
What is the most elegant way of performing a SELECT DISTINCT on a
single column at the ORM level on SQLAlchemy 0.4.6?
It looks like you can do
q=session.query(Order.support_manager).distinct() in the beta
version. This is not supported in 0.4.6 though.
Thanks in advance
Best regards
Jon
this should be straightforward, I think...
I'm trying to do
select email_address from user where is_verified is not true
( or is not null or is not false )
This part is simple :
Session.execute( sqlalchemy.select( [ User.c.email ] ) )
This part is not:
Session.execute(
Howdy,
I'm a newbie to sqlalchemy and am having trouble understanding how to
turn selects into objects.
I've got two tables mapped into objects like this:
nis_accounts_table = Table( ... )
nis_users_table = Table( ... )
class NisAccount:
pass
class NisUser:
pass
mapper(NisUser,
Hi all,
What's the simplest way for do a simple:
select * from myTable where id in (1,2,3);
I've solved this by using Subquery but final qry isn't pretty as this one.
Thank you
Glauco
--
++
Glauco Uri
I am looking to filter specific columns in a table, but I cannot find
how to do this. I can filter by setting these columns to a value, but
not just grabbing the entire column. In SQL, I want to do this:
SELECT artist FROM artist_table, where the only column kept is
artist. The reason I want to
Is there some other way I can execute a query using a list as a bound parameter?
res = db.db_con.text('SELECT module_extra_key_name FROM module_extra_keys
WHERE module_id IN :module_ids').execute({'module_ids': [1,2]})
The above is giving me an You cannot execute SELECT statements in
Hi all,
I'm trying to create the following query using SA:
SELECT DISTINCT operatore.id, anagrafica.nome, acl.cod_ruolo
FROM operatore JOIN anagrafica
ON operatore.id_anagrafica = anagrafica.id
LEFT OUTER JOIN acl ON acl.id_operatore = operatore.id
LEFT OUTER JOIN ruolo_permesso ON
hi,
I am currently using sqlalchemy to build SQL queries and it's a fantastic
tool! By now, I am looking for a way to build:
SELECT 'a_fixed_string', atable.col1, atable.col2
FROM atable
using the syntax:
select([XXX, atable.c.col1, atable.c.col2])
but I don't know what to put at XXX.
This should be easy, but I'm having real problems with it. I'd like
to do the following SQL (this is from MySQL) as a sqa query:
select count(distinct flow.node_id) as nodes, ip.ip_addr,
ip.country, ip.domain, ip.isp, ip.city from flow, ip where flow.src_id =
ip.id group by flow.src_id having
I'm getting this error trying to map Select objects. Here's a test case:
from sqlalchemy import *
e = create_engine('sqlite:///:memory:', echo=True)
md = BoundMetaData(e)
sql =
CREATE TABLE books (
id integer PRIMARY KEY, -- auto-SERIAL in sqlite
title
90 matches
Mail list logo