Re: [sqlalchemy] Error: ORA-01036: illegal variable name/number
Il 03/04/2014 16:27, Simon King ha scritto:
On Thu, Apr 3, 2014 at 2:54 PM, Matteo Boscolo
matteo.bosc...@boscolini.eu wrote:
Il 03/04/2014 15:26, Matteo Boscolo ha scritto:
Hi All,
i got this function to make a query:
def getTypeFromId(self,tmm_id):
get the tipe from a given id
flt=TMM_ID='%s'%str(tmm_id)
for ent in self.query(filterSql=flt):
return ent.getValue(TMM_TYPE)
the value of the tmm_id is 'T]:5\
unfortunately the orm think that the :5 is a parameter and i get the
illegal variable name/number ..
this is the select of the query ..
'SELECT TMM_REVENTITY_revprog, TMM_REVENTITY_lockuser,
TMM_REVENTITY_tlockcounter, TMM_REVENTITY_revdate,
TMM_REVENTITY_revdes, TMM_REVENTITY_revstate, TMM_REVENTITY_plockdate,
TMM_REVENTITY_plockcounter, TMM_REVENTITY_revname,
TMM_REVENTITY_revlabel, TMM_REVENTITY_TMM_ID, TMM_REVENTITY_TMM_TYPE,
TMM_REVENTITY_TMM_LASTUPDATE \nFROM (SELECT TMM_REVENTITY.revprog AS
TMM_REVENTITY_revprog, TMM_REVENTITY.lockuser AS
TMM_REVENTITY_lockuser, TMM_REVENTITY.tlockcounter AS
TMM_REVENTITY_tlockcounter, TMM_REVENTITY.revdate AS
TMM_REVENTITY_revdate, TMM_REVENTITY.revdes AS TMM_REVENTITY_revdes,
TMM_REVENTITY.revstate AS TMM_REVENTITY_revstate,
TMM_REVENTITY.plockdate AS TMM_REVENTITY_plockdate,
TMM_REVENTITY.plockcounter AS TMM_REVENTITY_plockcounter,
TMM_REVENTITY.revname AS TMM_REVENTITY_revname, TMM_REVENTITY.revlabel
AS TMM_REVENTITY_revlabel, TMM_REVENTITY.TMM_ID AS
TMM_REVENTITY_TMM_ID, TMM_REVENTITY.TMM_TYPE AS
TMM_REVENTITY_TMM_TYPE, TMM_REVENTITY.TMM_LASTUPDATE AS
TMM_REVENTITY_TMM_LASTUPDATE \nFROM TMM_REVENTITY \nWHERE
TMM_ID=\'T]:5\') \nWHERE ROWNUM = :ROWNUM_1' {'ROWNUM_1': 5000, '5':
None}
any help is appreciated ..
regards,
Matteo
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
hi solve the issue by putting the escape parameter so my tmm_id become
'T]\:5\
thanks anyhow
regards,
Matteo
Out of interest, do you really need to be passing a raw sql string
into your query here? It opens you up to bugs and possibly sql
injection attacks if you don't quote your strings properly. For
example, what happens if tmm_id contains a single quote?
Cheers,
Simon
this is a good point of view ...
Thanks,
Regards,
Matteo
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Modifying a cascaded object directly and then saving its parent
Thanks for the very detailed explanation :) I think I'll tweak the application flow to avoid this kind of thing altogether... Many 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] SQL Server 2005 + pyodbc + Stored Procedure
Hi,
I am trying to execute stored procedure from python / pyramid code. I am
passing 3 input 1 output parameter. But I am not able to receive output
parameter back.
Here is Stored Procedure
CREATE PROCEDURE MY_PROC
@empID char(10),
@oldEmpList XML,
@newEmpList XML,
@Status INT OUTPUT
AS
-- sp body
SET @Status = 1
RETURN
GO
Here is Python code that calling stored procedure
t = text('EXEC MY_PROC :empID, :oldEmpList, :newEmpList, :Status',
bindparams=[bindparam('empID', type_=String, value='1234'),
bindparam('oldEmpList', type_=TEXT,
value='empid1/idid2/id'),
bindparam('newEmpList', type_=TEXT,
value='empide01/idide02/id'),
bindparam('Status', type_=Integer, value=0,
isoutparam=True)])
result = CMS_DBSession.execute(t)print result.out_parameters
Thanks
Aniruddha
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] database design question
hi all! i have a question about sqlalchemy and database design. i'm developing an app, where each client may receive an alert about the total space usage of their data (files and database), so, using postgresql, i can get them (data usage size) using a different tablespace, database or schema, if i'm not wrong. given these premises, which is the best way to design my database using sqlalchemy, having in mind that I need also to integrate the client databases to the core database? my best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] SQL Server 2005 + pyodbc + Stored Procedure
as far as I know, pyodbc does not support OUT parameters. The info they have
on stored procs is here:
http://code.google.com/p/pyodbc/wiki/StoredProcedures
if you want to work with the Pyodbc cursor directly (or any other cursor, like
that of pymssql perhaps):
conn = my_session.connection()
dbapi_conn = conn.connection
cursor = dbapi_conn.cursor()
the isoutparam flag is currently only understood by the cx_oracle dialect.
On Apr 4, 2014, at 11:53 AM, anierud...@gmail.com wrote:
Hi,
I am trying to execute stored procedure from python / pyramid code. I am
passing 3 input 1 output parameter. But I am not able to receive output
parameter back.
Here is Stored Procedure
CREATE PROCEDURE MY_PROC
@empID char(10),
@oldEmpList XML,
@newEmpList XML,
@Status INT OUTPUT
AS
-- sp body
SET @Status = 1
RETURN
GO
Here is Python code that calling stored procedure
t = text('EXEC MY_PROC :empID, :oldEmpList, :newEmpList, :Status',
bindparams=[bindparam('empID', type_=String, value='1234'),
bindparam('oldEmpList', type_=TEXT,
value='empid1/idid2/id'),
bindparam('newEmpList', type_=TEXT,
value='empide01/idide02/id'),
bindparam('Status', type_=Integer, value=0,
isoutparam=True)])
result = CMS_DBSession.execute(t)
print result.out_parameters
Thanks
Aniruddha
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] is it possible to joinedload/subqueryload a single column ?
I have these 2 tables in the database: class TableA(base): id field_a field_b items = sa.orm.relationship(TableA_Items, primaryjoin=TableA.id== TableA_Items.table_a_id, backref=table_a) class TableA_Items(base): id table_a_id huge_blob_1 is it possible to set up or query a relationship that will only query the ids from TableA_Items ? Just to be clear, on the ORM, i want to have a collection that is only the IDs from TableA_Items, not the full records themselves. They're pretty big, and I'd like to avoid them. i basically want to be able to use the ORM and have this data session.query( TableA , TableA_Items.id ) this doesn't look possible based on the docs, but i'm hoping someone here may have run into a similar situation and figured out a workaround. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] is it possible to joinedload/subqueryload a single column ?
On Apr 4, 2014, at 5:16 PM, Jonathan Vanasco jonat...@findmeon.com wrote:
I have these 2 tables in the database:
class TableA(base):
id
field_a
field_b
items = sa.orm.relationship(TableA_Items, primaryjoin=TableA.id==
TableA_Items.table_a_id, backref=table_a)
class TableA_Items(base):
id
table_a_id
huge_blob_1
is it possible to set up or query a relationship that will only query the ids
from TableA_Items ?
Just to be clear, on the ORM, i want to have a collection that is only the
IDs from TableA_Items, not the full records themselves. They're pretty big,
and I'd like to avoid them.
i basically want to be able to use the ORM and have this data
session.query( TableA , TableA_Items.id )
this doesn't look possible based on the docs, but i'm hoping someone here may
have run into a similar situation and figured out a workaround.
this is what load_only() was added for:
options(joinedload(TableA.items).load_only('id'))
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] is it possible to joinedload/subqueryload a single column ?
thank you !! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] is it possible to load two relationships during a subquery or similar ?
sorry for overload the list with random questions. i'm trying to get a
release out the door and dealing with performance bottlenecks...
given this setup:
class Items2Attributes(base):
id
item_id
condition_id
attribute_id
class Items(base):
id
to_attributes_a =
sa.orm.relationship(items.id==Items2Attributes.item_id,
Items2Attributes.condition_id==a)
to_attributes_b =
sa.orm.relationship(items.id==Items2Attributes.item_id,
Items2Attributes.condition_id==b)
I achieved a greater performance boost moving 'to_attributes_X' into a
subueryload from a joinedload.
I'm getting a bit of a ding from having 2 subqueryloads though.
Are there any ways to load both `to_attributes_a` and `to_attributes_b`
with a single request ?
i thought the syntax `subqueryload('to_attributes_a','to_attributes_b')`
might work, but that is for loading paths [e.g. to_attributes_a and
to_attributes_a.to_attributes_b ] makes perfect sense.
i totally understand this is a bit of an edge case. I doubt many people
encounter multiple relationships to the same tables. i'm just hitting this
table 2x for a nearly identical query. if anyone knows of a workaround, I
'd be grateful.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] is it possible to load two relationships during a subquery or similar ?
On Apr 4, 2014, at 8:08 PM, Jonathan Vanasco jonat...@findmeon.com wrote: sorry for overload the list with random questions. i'm trying to get a release out the door and dealing with performance bottlenecks... given this setup: class Items2Attributes(base): id item_id condition_id attribute_id class Items(base): id to_attributes_a = sa.orm.relationship(items.id==Items2Attributes.item_id, Items2Attributes.condition_id==a) to_attributes_b = sa.orm.relationship(items.id==Items2Attributes.item_id, Items2Attributes.condition_id==b) I achieved a greater performance boost moving 'to_attributes_X' into a subueryload from a joinedload. I'm getting a bit of a ding from having 2 subqueryloads though. Are there any ways to load both `to_attributes_a` and `to_attributes_b` with a single request ? yeah, make one relationship and just filter them with a @property for each of a and b. if the object is usually used such that both collections are needed, that's the approach. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] is it possible to load two relationships during a subquery or similar ?
On Friday, April 4, 2014 8:11:11 PM UTC-4, Michael Bayer wrote: yeah, make one relationship and just filter them with a @property for each of “a” and “b”. if the object is usually used such that both collections are needed, that’s the approach. brilliant. thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
