[sqlalchemy] Re: row level locking question with expression language

[Krishnakant]

On Saturday 24 June 2017 08:17 PM, Jonathan Vanasco wrote:
http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=with_for_update#sqlalchemy.sql.expression.Select.with_for_update 



E.g.:
stmt  =  select([table]).with_for_update(nowait=True)


Thank you, will try this out for sure.
I was using raw sql till now but thought that using expression language 
may give me some more pythonic way of doing such things.

Happy hacking.
Krishnakant.

--
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] row level locking question with expression language

[Krishnakant]

Dear all,

I wish to know how can I achieve row level locking (select for update ) 
while using sql expression language?


I have been using select([table]).where() very comfortable but did not 
file a select for lock example in the tutorial.


Any suggestion?

Happy hacking.

Krishnakant.

--
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] Re: confused on optimal use of engine vs connection

[Krishnakant]

On Friday 03 June 2016 09:07 PM, Jonathan Vanasco wrote:



On Friday, June 3, 2016 at 7:49:23 AM UTC-4, Krishnakant wrote:

So it will have no performance difference is it?


If both do same thing then how and why will explicit connection
help me
better?


the `engine.execute()` will be slower, because you will be 
creating/checking-out a different connection for each operation in the 
for-loop and (needlessly) leveraging the sqlalchemy connection pool. 
 if you use `connection.execute()` with a single connection, then you 
don't have as much overhead involved with the connection pool management.


Your errors are probably from improperly implementing the connection 
pool.  I suggest re-reading the connection pool docs and faq to get a 
better understanding of what it does and why it does that.



Thank you Jonathan,
I had guessed about your reply, and it came out correct.
So essentially opening a connection at the start of a class method/ 
function, doing number of queries and then closing it just before return 
would be the right strategy  I think?

And is there some kind of cashing available for sql expression as well?
I see a lot of articles on things like memcash etc but they all talk 
about ORM.

Happy hacking.
Krishnakant.

--
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: confused on optimal use of engine vs connection

[Krishnakant]

On Friday 03 June 2016 04:44 AM, Jonathan Vanasco wrote:
as the docs state, `engine.execute(foo)` is shorthand for "connection 
= engine.connect()" + "connection.execute(foo)".  you can verify this 
in the source.



So it will have no performance difference is it?
for what you describe, it's usually best to grab an explicit 
connection and re-use it.


If both do same thing then how and why will explicit connection help me 
better?

Happy hacking.
Krishnakant.

--
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] confused on optimal use of engine vs connection

[Krishnakant]

Dear all,
I am currently reading
<http://docs.sqlalchemy.org/en/latest/core/connections.html>
and one thing is confusing, rather not clearly mentioned, unless I 
missed it.

Which is a better thing to do, specially when executing raw sql queries?
engine.execute or con = engine.connect() and then con.execute()?
Where there is a chance of multiple executes with a set of different 
queries to be run one after the other or in a loop, should 
engine.execute be used and should I disable pooling for such heavy 
select queries being fired one after the other?

I have encountered pool overflow problems in such situations.
Changing max limit solved it for now, but I guess this is not a good 
idea in the long run at production level.

Kindly guide me on this as I am totally new in this context.
happy hacking.
Krishnakant.

--
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] alchemy expressionconfusion in insert statement having jsonb field

[Krishnakant]

On Wednesday 16 March 2016 02:24 PM, Simon King wrote:

On 16 Mar 2016, at 06:45, Krishnakant <krm...@openmailbox.org> wrote:

Dear all,
I have a challenge which is confusing me.
I have a table called voucher with the following field.
(vid, vdate,Cr) where vid is integer, vdate  is date and Cr is jsonb in
postgresql.
Can some one tell me how do I write an sql expression insert query
involving all the 3 fields?
some thing like con.execute(voucher.insert(),...) I don't know how I do
this.
In the documentation there are 2 fields id and data and it seems id is
auto_increment so inserting with only one field which is only json is
easy, but here I have 3 fields involved in the insert.
So how do I do this?
Happy hacking.
Krishnakant.Krishnakant.

I’m not sure I understand the question. Here are the docs for INSERT 
expressions:

http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#executing-multiple-statements

you should be able to write something like this:

con.execute(voucher.insert(), vid=1, vdate=somedate, Cr=somedict)


Thanks Simon,
It works and I also got another problem worked out.

Happy hacking.
Krishnakant.

--
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] how can I search rows containing jsonb data on the basis of it's key>

[Krishnakant]

Hello,
I wish to search rows in my table on the basis of text of json keys.
My table has vouchercode, voucherdate, dramt, cramt.
Here dramt and cramt are both jsonb fields (postgresql 9.4).
dramt containes account and amount, same with cramt.
sample date.
vouchercode:1 ... dramt{"1":25,"2":25}
"1" and "2" are account codes.
there will be several such ros and I want to get only those rows where 
either dramt or cramt contains accountcode as 1.

Note that accountcode is a key not the value of jsonb data.
so my sudo code for where is where dramt.key = '1'.
How can I achieve this?
I can loop through all vouchers and do a comparison but that is not 
efficient and entirely defeating purpose of a select query.

Happy hacking.
Krishnakant.

--
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] I wish to know how I use table ALIAS in alchemy core

[Krishnakant]

On Thursday 25 February 2016 03:50 PM, Simon King wrote:
On Thu, Feb 25, 2016 at 9:43 AM, Krishnakant <krm...@openmailbox.org 
<mailto:krm...@openmailbox.org>> wrote:


Hello,
I have a query where there are 2 alias for a single table.
This is because the table contains a self referencing foreign key.
the table is (groupcode integer primary key, groupname text,
subgroupof integer foreign key references groupcode).
Now let's say I wish to have a 2 column query with groups and
their respective subgroups, I need to join the table to itself
making 2 aliases.
I know the raw query but need to do it through sqlalchemy core.
I don't use ORM for my project.and need this in the expression
language.


Something like this perhaps:

import sqlalchemy as sa
md = sa.MetaData()
t = sa.Table(
't', md,
sa.Column('groupcode', sa.Integer, primary_key=True),
sa.Column('groupname', sa.Text()),
sa.Column('subgroupof', sa.ForeignKey('t.groupcode')),
)

subgroup = t.alias('subgroup')
j = t.join(subgroup, subgroup.c.subgroupof == t.c.groupcode)
print sa.select([t.c.groupcode, subgroup.c.groupcode]).select_from(j)


Output:

SELECT t.groupcode, subgroup.groupcode
FROM t JOIN t AS subgroup ON subgroup.subgroupof = t.groupcode


Hope that helps,


Thanks a lot for the help.
I have one query.
do I not need to import alias?  some thing like,
from sqlalchemy import alias
I tryed this and I get import error for the above mentioned line.
and the query you provided did not work without alias.
Can you help?
Happy hacking.
Krishnakant.

--
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] I wish to know how I use table ALIAS in alchemy core

[Krishnakant]

Hello,
I have a query where there are 2 alias for a single table.
This is because the table contains a self referencing foreign key.
the table is (groupcode integer primary key, groupname text, subgroupof 
integer foreign key references groupcode).
Now let's say I wish to have a 2 column query with groups and their 
respective subgroups, I need to join the table to itself making 2 aliases.

I know the raw query but need to do it through sqlalchemy core.
I don't use ORM for my project.and need this in the expression language.
Happy hacking.
Krishnakant.

--
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] Where to use and vs and_()

[Krishnakant]

Hello all,
The subject might have made my problem already clear.
So I am unclear about when I should use the normal Python "and " vs the 
sqlalchemy "and_" while writing where, having or similar queries 
including joins.

I have tryed understanding this but may be I have overlooked some thing.
Kindly give some pointers.
Happy hacking.
Krishnakant.

--
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] totally shifted to sql expression language, any performance tips

[Krishnakant]

Dear all,
I have totally shifted to using sql expression in alchemy.
I am going to retrieve huge sets of data, about 5 + records with 7 
columns each.

The result might contain jsonb data (I use postgresql 9.4 with Psycopg2 ).
Insertions are not a problem because they are not so heavy and not in 
concurrence.
Daily there will be around 200 entries on an average, not more than 5 
people doing them at a time.

So what performance tips could you all suggest?
Should I use pg8000 or any other driver instead of Psycopg2?
Any other cashing tips?
Happy hacking.
Krishnakant.

--
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] how to pick up constraint violation errors in Alchemy

[Krishnakant]

hello all,
I wish to know how I can pick up the constraint failure errors in my code?
I think I would probably have to pick up the errors in a try: except: 
system?

But What is the exact way of picking up the message?
Do we have a ready made exception for each constraint?  such as Unique 
or Check?

Or is there a common one and message is to be picked up?
i am using Alchemy with Psycopg2 for postgresql 9.4
Happy hacking.
Krishnakant.

--
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] how to pick up constraint violation errors in Alchemy

[Krishnakant]

On Sunday 03 January 2016 04:20 AM, Mike Bayer wrote:


On 01/02/2016 01:02 PM, Krishnakant wrote:

hello all,
I wish to know how I can pick up the constraint failure errors in my code?
I think I would probably have to pick up the errors in a try: except:
system?

that is correct.


But What is the exact way of picking up the message?

You'd need to catch the type of exception, such as IntegrityError which
is usually what you will get for a constraint violation.  Beyond that,
if you need to programmatically have more information you'd need to
parse the text of the exception with a regular expression.


So If the unique constraint is violated, then will I still have to see 
them using integrity exception?


As I said I am using psycopg2 for postgresql.
Thanks for the tips.
happy hacking.
Krishnakant.

--
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] auto completion for connection and engine objects in eclipse not working

[Krishnakant]

Hello all,
I need some help/ tips.
This is may be perhaps partially off tipic but still it is related to 
Alchemy.

The issue is in the subject line

when I do eng = create_engine(connection_statement) I do get the engine 
in eng
but when I do con = eng.connect I don't get any selection list when i 
just do eng. and even after pressing tab nothing comes so I have to 
write it by hand.

Same is for the connection object con.
if I wish the execute method I have to completely write 
con.execute(querystring)

meaning I don't get auto completion here as well.
same is with the fetchall() with results.
Can some one give a tip as to why this might be the case with few 
modules in sqlalchemy and not all of them?

Happy hacking.
Krishnakant.

--
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] can I use tables generated by ORM via sql expression language for select queries

[Krishnakant]

Hi,
The subject says it all.
I have classes inheriting the base and thus my tables are created using ORM.
But I wish to use sql expression language for queries, particularly bulk 
selects for faster performance.

So is this possible and how?
Happy hacking.
Krishnakant.

--
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] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE

[Krishnakant Mane]

On 11/02/12 21:10, Michael Bayer wrote:

def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return 
engine.execute(function_with_params.execution_options(autocommit=True))




Hi Michael,
I tryed this code with postgresql (psycopg2 ).

I have a group table with the fields.
groupcode integer, groupname text and groupdesc text.

When I pass in a procedure name called get groups with all the above 
listed columns as output parameters (actually the procedure returns 
setof rows), I get the could not locate column on the groupcode field.
I had previously mailed you with a longish function called execProc 
which you corrected and maild back.

With that long function without func, I never had this problem.
could you please point out what could have gone wrong?
Note that I am not paisting the function here as it is exactly the same 
as you had provided.

I will however paist the line which gave me the error.
I   will infact give you the code concerning the function where your 
version of execProc was called.
#note that we have a dbconnect module containing an array of engines and 
also the execProc method.


res = dbconnect.execproc(getAllGroups,dbconnect.engines[client_id])
#since we know that the function getAllGroups exists and that it returns 
the exact 3 fields from the groups table,

#we are shure we have some records to process.
#We will loop through the generated resultset in res
#We wish to make a 2 dymentional list of rows and columns to be 
transfered over an xmlrpc connection.

result = []
for row in res:
   
 result.append([row[groupcode],row[groupname],row[groupdesc]])

return result




I know for sure that the names of the fields used inside row[] are 
absolutely correct, and the code actually works when I use the old 
execProc which did not have all the sqlalchemy's func trickery.

Could you please explain?
happy hacking.
Krishnakant.


--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE

[Krishnakant Mane]

On 12/02/12 22:01, Michael Bayer wrote:

On Feb 12, 2012, at 5:22 AM, Krishnakant Mane wrote:


On 11/02/12 21:10, Michael Bayer wrote:

def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return 
engine.execute(function_with_params.execution_options(autocommit=True))



Hi Michael,
I tryed this code with postgresql (psycopg2 ).

I have a group table with the fields.
groupcode integer, groupname text and groupdesc text.

When I pass in a procedure name called get groups with all the above listed 
columns as output parameters (actually the procedure returns setof rows), I get 
the could not locate column on the groupcode field.


OK sorry, you want individual columns from the function which  means select * 
from it.  Here is that, using a function from postgresql's website:

from sqlalchemy import create_engine, func, select, literal_column
engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

engine.execute(
CREATE OR REPLACE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
 LANGUAGE SQL;
)
def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return engine.execute(
 select([literal_column('*')]).\
 select_from(function_with_params).\
 execution_options(autocommit=True)
 )

for row in execproc(dup, engine, [42]):
 print row.f1, row.f2





Hi Michael,
Firstly at the outset, let me say that SQL Alchemy is one of the best 
and most impressive library I ever saw in my career.
I lead a project called GNUKhata, www.gnukhata.org which is a free and 
open source software aimed at chartered accountants, small to medium 
business enterprises and retail shops.
The project is government funded and we have enough desire and funds to 
make it the most popular and ubiquitous software for accounting as 
firefox is to internet.
I wish to tell you that SA is the power house behind our core engine 
that uses postgresql as the database server.

SA has made our work so easy and sql has become bliss with it.
I infact wish to add this projject as a testimony for your great work.
We are soon to launch the software and it is going to be a great success 
given the marketing we are doing and the social projects we wish to 
undertake with it.

Now coming back to your function.e
You see, I wish to use this execProc function for executing all the 
stored procedureshen  including inserts, updates, deletes and selects.

With regard to the latest revision you sent for the function,
I wish to know if I can use it for all the mentioned purposes?
For example if I have a table already in the database called groups with 
groupcode, groupname and groupdesc as my fields, can I use this version 
of execProc to access a stored procedure related to the table?

Suppose the stored procedure has 2 out parameters, groupname and groupdesc.
can I execute this procedure, say getGroups() through this  execProc 
function.

Then, can I do a fetchall and then,
for row in rows:
print row[groupname] ...
and so on?
I think this might be pritty obvious, but the example you posted talked 
about creating the table etc.  So just wished to get it cleared.

Happy hacking.
Krishnakant.




--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE

[Krishnakant Mane]

Hi again,
Mikeal, I am sorry, I was half asleep when I went through the code,
This week long sprint on our project has tired me out.
I think its pritty clear now.
(unless you would love to explain the last 2 llines for clearity sake ).
Happy hacking.
Krishnakant.

On 12/02/12 22:01, Michael Bayer wrote:

On Feb 12, 2012, at 5:22 AM, Krishnakant Mane wrote:


On 11/02/12 21:10, Michael Bayer wrote:

def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return 
engine.execute(function_with_params.execution_options(autocommit=True))



Hi Michael,
I tryed this code with postgresql (psycopg2 ).

I have a group table with the fields.
groupcode integer, groupname text and groupdesc text.

When I pass in a procedure name called get groups with all the above listed 
columns as output parameters (actually the procedure returns setof rows), I get 
the could not locate column on the groupcode field.


OK sorry, you want individual columns from the function which  means select * 
from it.  Here is that, using a function from postgresql's website:

from sqlalchemy import create_engine, func, select, literal_column
engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

engine.execute(
CREATE OR REPLACE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
 LANGUAGE SQL;
)
def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return engine.execute(
 select([literal_column('*')]).\
 select_from(function_with_params).\
 execution_options(autocommit=True)
 )

for row in execproc(dup, engine, [42]):
 print row.f1, row.f2






--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE

[Krishnakant Mane]

Hi Michael,
Excelent work, you are a real artist.
Just wished to know what is the trans object, and do i need to use a 
connection object for the thing to work?

I mean, do I need the conn = engine.connect() line?
I already have a set of live engines so I never make an extra connection 
in my execProc method, as you must have seen in my very first email with 
explanations.


Thanks a million for the splendid work and your most valued help.
happy hacking.
Krishnakant.

On 13/02/12 00:21, Michael Bayer wrote:

Here's another one that is without the SQL expression stuff, perhaps it is 
easier to understand:

def execproc(procname, engine, queryParams=[]):
 conn = engine.connect()
 try:
 trans = conn.begin()
 result = conn.execute(
 SELECT * FROM %s(%s) % (
 procname,
 , .join(%s for arg in queryParams),
 ), queryParams
 )
 trans.commit()
 return list(result)
 finally:
 conn.close()


still another, using the DBAPI directly.  This uses only psycopg2 and the 
Python standard library:

import psycopg2
import collections
def execproc(procname, queryParams=[]):
 conn = psycopg2.connect(user=scott, password=tiger,
 host=localhost, database=test)
 cursor = conn.cursor()
 cursor.execute(
 SELECT * FROM %s(%s) % (
 procname,
 , .join(%s for arg in queryParams),
 ), queryParams
 )
 conn.commit()
 result = list(cursor)
 conn.close()
 tup = collections.namedtuple(row, [d[0] for d in cursor.description])
 return [tup(*row) for row in result]


All three versions of this will execute any psycopg2 stored procedure, including ones that INSERT, 
UPDATE, or DELETE and return the results as a named result set.The result rows here are not 
quite the same thing as out parameters which are an Oracle concept but fortunately this 
is much easier to do than Oracle out parameters.





On Feb 12, 2012, at 12:31 PM, Krishnakant Mane wrote:


Hi again,
Mikeal, I am sorry, I was half asleep when I went through the code,
This week long sprint on our project has tired me out.
I think its pritty clear now.
(unless you would love to explain the last 2 llines for clearity sake ).
Happy hacking.
Krishnakant.

On 12/02/12 22:01, Michael Bayer wrote:

On Feb 12, 2012, at 5:22 AM, Krishnakant Mane wrote:


On 11/02/12 21:10, Michael Bayer wrote:

def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return 
engine.execute(function_with_params.execution_options(autocommit=True))


Hi Michael,
I tryed this code with postgresql (psycopg2 ).

I have a group table with the fields.
groupcode integer, groupname text and groupdesc text.

When I pass in a procedure name called get groups with all the above listed 
columns as output parameters (actually the procedure returns setof rows), I get 
the could not locate column on the groupcode field.

OK sorry, you want individual columns from the function which  means select * 
from it.  Here is that, using a function from postgresql's website:

from sqlalchemy import create_engine, func, select, literal_column
engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

engine.execute(
CREATE OR REPLACE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
 LANGUAGE SQL;
)
def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return engine.execute(
 select([literal_column('*')]).\
 select_from(function_with_params).\
 execution_options(autocommit=True)
 )

for row in execproc(dup, engine, [42]):
 print row.f1, row.f2





--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.



--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE

[Krishnakant Mane]

On 13/02/12 02:19, Michael Bayer wrote:

Transaction is a SQLAlchemy object that represents the scope of the 
transaction within the DBAPI.  The DBAPI always presents a transaction, 
that's why the pure DBAPI version calls conn.commit() at the end.



So does that mean I will have to use a connection object and not do the 
execution with just engine?
Note that the engine parameter I pass to the execProc is already 
connected to a database.

So do I need to create an additional connection object?
I thought that the engine had its own implesit connection which it uses 
for executing the functions or any sql for that matter.




You could also say conn = engine.connect().execution_options(autocommit=True).


Ah, meaning the connection object is just a formal requirement is it?
Happy hacking.
Krishnakant.



On Feb 12, 2012, at 2:18 PM, Krishnakant Mane wrote:


Hi Michael,
Excelent work, you are a real artist.
Just wished to know what is the trans object, and do i need to use a connection 
object for the thing to work?
I mean, do I need the conn = engine.connect() line?
I already have a set of live engines so I never make an extra connection in my 
execProc method, as you must have seen in my very first email with explanations.

Thanks a million for the splendid work and your most valued help.
happy hacking.
Krishnakant.

On 13/02/12 00:21, Michael Bayer wrote:

Here's another one that is without the SQL expression stuff, perhaps it is 
easier to understand:

def execproc(procname, engine, queryParams=[]):
 conn = engine.connect()
 try:
 trans = conn.begin()
 result = conn.execute(
 SELECT * FROM %s(%s) % (
 procname,
 , .join(%s for arg in queryParams),
 ), queryParams
 )
 trans.commit()
 return list(result)
 finally:
 conn.close()


still another, using the DBAPI directly.  This uses only psycopg2 and the 
Python standard library:

import psycopg2
import collections
def execproc(procname, queryParams=[]):
 conn = psycopg2.connect(user=scott, password=tiger,
 host=localhost, database=test)
 cursor = conn.cursor()
 cursor.execute(
 SELECT * FROM %s(%s) % (
 procname,
 , .join(%s for arg in queryParams),
 ), queryParams
 )
 conn.commit()
 result = list(cursor)
 conn.close()
 tup = collections.namedtuple(row, [d[0] for d in cursor.description])
 return [tup(*row) for row in result]


All three versions of this will execute any psycopg2 stored procedure, including ones that INSERT, 
UPDATE, or DELETE and return the results as a named result set.The result rows here are not 
quite the same thing as out parameters which are an Oracle concept but fortunately this 
is much easier to do than Oracle out parameters.





On Feb 12, 2012, at 12:31 PM, Krishnakant Mane wrote:


Hi again,
Mikeal, I am sorry, I was half asleep when I went through the code,
This week long sprint on our project has tired me out.
I think its pritty clear now.
(unless you would love to explain the last 2 llines for clearity sake ).
Happy hacking.
Krishnakant.

On 12/02/12 22:01, Michael Bayer wrote:

On Feb 12, 2012, at 5:22 AM, Krishnakant Mane wrote:


On 11/02/12 21:10, Michael Bayer wrote:

def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return 
engine.execute(function_with_params.execution_options(autocommit=True))


Hi Michael,
I tryed this code with postgresql (psycopg2 ).

I have a group table with the fields.
groupcode integer, groupname text and groupdesc text.

When I pass in a procedure name called get groups with all the above listed 
columns as output parameters (actually the procedure returns setof rows), I get 
the could not locate column on the groupcode field.

OK sorry, you want individual columns from the function which  means select * 
from it.  Here is that, using a function from postgresql's website:

from sqlalchemy import create_engine, func, select, literal_column
engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

engine.execute(
CREATE OR REPLACE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
 LANGUAGE SQL;
)
def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return engine.execute(
 select([literal_column('*')]).\
 select_from(function_with_params).\
 execution_options(autocommit=True)
 )

for row in execproc(dup, engine, [42]):
 print row.f1, row.f2





--
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

[sqlalchemy] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE

[Krishnakant Mane]

Hello all,
I have an interesting problem for which I am sure some simple solution 
must be existing.

I have made a Python function which I will paist below.
Basically what the function does is that it takes 3 parameters, namely 
the name of a stored procedure, engine instance and a set of parameters.
This function is used to make calls to postgresql based stored 
procedures in a modular way.
The function is kept central and all my modules just pass the necessary 
parameters and leave it to the function to do the rest.
I got it working perfectly, except that I don't know how to handle 
special characters when constructing the query that makes a call to a 
stored procedure.  So if I have an insert query which has a value with a 
single quote ('), it crashes.
As you will observe in the function, it takes the arguements from a list 
called queryParams and constructs that part of the query that takes 
input arguements for inserts or for the where clause during select etc.  
So in those input parameters if any special character appears, the said 
stored procedure naturally fails.

following is the exact function.
Some one please point me out what the fundamental mistake is and 
probably send in a corrected version.


def execproc(procname, engine, queryParams=[]):
 Purpose:
executes a named stored procedure and returns the result.
Function takes 3 parameters, procname is a string containing the 
name of the stored procedure.
engine is the sqlalchemy engine instance through which the query 
will be executed.

queryParams contains the input parameters in a list form (if any).
description:
First it starts building a query string that commonly begins with 
the common select * from syntax that is needed for calling a stored 
procedure.
The code then goes to check if one or more parameters are 
supplied.  If yes then

a for loops runs that concatinate the parameters inside ()
During this process it checks the datatype of each supplied 
parameter to stringify any parameter or keep it as integer.

This is done using the %s, %d and %f place holders.
After the query is built using the user input that consisted of the 
proc name and parames, it executes the same using the supplied engine 
instance.
The result of the execution contains the rows returned by the 
stored procedure that was called.


listCounter = 0
if len(queryParams) == 0:
queryString = select * from %s()  % (procname)
else:
queryString = select * from %s( % (procname)
for param in queryParams:
if type(param) == str:
queryString = queryString + '%s' % (param)
if type(param) == int:
queryString = queryString + %d % (param)
if type(param) == float:
queryString = queryString + %.2f % (param)
if type(param) == NoneType:
queryString = queryString + None
if listCounter  (len(queryParams) - 1):
queryString = queryString + ,
listCounter = listCounter + 1
queryString = queryString + )
print queryString
res = 
engine.execute(text(queryString).execution_options(autocommit=True))

return res


Thanks for help in advance.
Happy hacking.
Krishnakant.



--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE

[Krishnakant Mane]

Thanks Michael,
I will see if this works perfectly with postgresql.
I had tryed func before but did not get any success.
May be this time it will work.
Happy hacking.
Krishnakant.

On 11/02/12 21:10, Michael Bayer wrote:

On Feb 11, 2012, at 8:02 AM, Krishnakant Mane wrote:


Hello all,
I have an interesting problem for which I am sure some simple solution must be 
existing.
I have made a Python function which I will paist below.
Basically what the function does is that it takes 3 parameters, namely the name 
of a stored procedure, engine instance and a set of parameters.
This function is used to make calls to postgresql based stored procedures in a 
modular way.
The function is kept central and all my modules just pass the necessary 
parameters and leave it to the function to do the rest.
I got it working perfectly, except that I don't know how to handle special 
characters when constructing the query that makes a call to a stored procedure. 
 So if I have an insert query which has a value with a single quote ('), it 
crashes.

This is because the function is not using bound parameters.  Dealing with 
individual datatypes and how they are formatted to the database is something 
you should let the DBAPI handle.

SQLAlchemy includes the capability to call functions built in via the func 
parameter.   Your execproc could be written as:

from sqlalchemy import create_engine, func
engine = create_engine('mysql://root@localhost/test', echo=True)

def execproc(procname, engine, queryParams=[]):
 function = getattr(func, procname)
 function_with_params = function(*queryParams)
 return 
engine.execute(function_with_params.execution_options(autocommit=True))

print execproc(concat, engine, [dog,  , cat]).scalar()




--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Re: How long should it take to load a data base into memory?

[Krishnakant Mane]

May be I am wrong,
But I guess you have not used stored procedures?
If speed and performance is important along with scalability, then 
forget database independence and use stored procedurs.
I have built a small function called execProc() which I can send you off 
the list.

It just makes use of the sqlalchemy's api and does not involve sessions etc.
Just a connectionless engine (which directly connects to the database ) 
and executes the said stored procedure.
I use one instance of the engine and entire thing happens at the 
database side.

I just get the result proxy object with wich you can obviusly work.
I have seen a big performance bennifit and got control over memory usage 
at the client side because now the ready made queries are executed at 
the database side (postgresql) in my case and just the rows returned.

You, see the overhead of session is removed here.
Happy hacking.
Krishnakant.

On 19/12/11 04:40, Michael Bayer wrote:

On Dec 18, 2011, at 5:53 PM, rivka wrote:


So - actually investigated it thoroughly - and here are the results:

My database size on disk is 362MB and includes the main table and
multiple one to many associated tables. I am querying the main table
(which has little info in itself - mainly id, an integer value and a
string value (mostly less than 100 characters) jointly with a one to
many relationship from the main table.
I run it through ipython and the program starts from ~23MB, and toward
the very end of the query - it soars to 582MB ! And that is when the
query involves only 10 rows out of the total of 1.2M rows in the
data base. So - I am very confused about why the memory explosion.
What occupies those 550MB of memory? surely not the data from the DB
which is probably less than 50MB total...
That explains the behavior that I have observed when attempting larger
queries - the memory explodes and the system shifts to using VM -
which is basically - working with the HD and churning memory and
caches non-stop.

I need to get control over the memory size so that I can hold a larger
query in the memory and work directly with memory.

You'd definitely need to forego using the ORM and build a very memory efficient 
datastructure that suits your needs.

it sounds like you might benefit by reorganizing your data into simple 
dictionaries and lists and just using Redis:  http://redis.io/

Since you're looking for all the data to be in memory, you should just get it 
out of the relational database as the first step, then work with an optimized structure 
that suits your needs.  redis is designed for this use case.




--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] is there a problem in $ and % in a query using engine.execute?

[Krishnakant Mane]

Hello all.
I am trying to write some code which after creating tables and related 
views trys to create the stored procedures (plpgsql).

code goes some thing like this
engine.execute(create or replace function addRecord(f1 text, f2 
t1.fieldname%type ) returns bit as $$

...
begin
...
end;
$$ language plpgsql)
When this gets executed I get the error that indicates dict object is 
not indexable.

Is this some thing to do wiht the use of % or $ sign in the query?
Note that although I wrote the code on more than one line, in my code 
its a single line statement.
All my views get created with same syntax of engine.execute() but not 
stored procedure creation code.

Any suggestion?
Happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] is None converted to null using engine.execute?

[Krishnakant Mane]

Hello all.
I have come across an interesting problem with sqlalchemy.
I am using 0.6.8 and plan to shift obviously to the .7 series.
But what ever the version is, I find this is really very interesting.
I have to execute stored procedures written  in plpgsql (for postgresql 
9.0).


The problem is very streight and precise.
I have a table called account.  in this table there are 2 columns 
groupcode and subgroupcode.
groupcode comes as a foreign key from the table group that contains 
groupname and groupcode.
However subgroupcode is optional as some accounts seldum get added to a 
group directly without a subgroup.

So in that condition I need to insert null for subgroupcode.
following is my query
engine.execute(select * from setAccount('grp001','account_name',None)
Ofcourse I have set the auto commit flag on but i did not include that 
here just to make things precise.

I get an error that says column None does not exist.

I wish to know if None in Python really gets converted to null in 
postgresql?

or is it my misinterpretation
that this can happen?
happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy

[Krishnakant Mane]

On 12/09/11 03:09, Michael Bayer wrote:

On Sep 11, 2011, at 3:43 PM, Krishnakant Mane wrote:


On 12/09/11 00:56, Michael Bayer wrote:

You use the func construct to invoke a function.  This can be passed to an 
execute() method directly where it should embed itself into a SELECT:

from sqlalchemy import func

result = engine.execute(func.name_of_my_pg_function(1, 2, 3))




Can you please give a complete example.
There are two balbonising problems here.
I use an ide called eclipse with pydev.
I don't get any code completion when I write the above code.
Secondly, do I use execute on engine or do i first do engine.connect() 
and then execute a func.name(param) through the connection?

When I try to use result as result[1] I get column not found.
Happy hacking.
Krishnakant.


--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy

[Krishnakant Mane]

Thanks michael,
But my problem here is different.

Let me give you the exact stored procedure that we have here for execution.



create or replace function getGroupByCode(group_code 
groups.groupcode%type) returns setof groups as $$

declare
res groups;
begin
for res in select * from groups where groupcode = group_code loop
return next res;
end loop;
return;
end;
$$ language plpgsql;


Now I will give you the code I am trying to run.


from sqlalchemy.engine import create_engine
from sqlalchemy import func

engine = 
create_engine(postgresql://gnukhata:gnukhata@localhost/K2011101512425529)


print type(engine)


res = engine.execute(func.getGroupByCode(1)).scalar()


print type(res)

for row in res:
   print row['groupname']

Note that the stored procedure refers to a group table that contains 
groupcode,groupname,groupdesc

So groupname is a valid column.
Yet I get the no such column error.
Can you please explain?

Happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy

[Krishnakant Mane]

On 15/10/11 22:17, Michael Bayer wrote:


that sounds like an issue in the procedure itself, such as running it on the wrong database, or the 
wrong groups table otherwise.   there could be many schemas/databases that contain a 
groups table. Get the procedure to work with psql first using the identical login 
information.





I got the result but without using func.
when I did res = engine.execute(select * from getGroupByCode(1)) I got 
the results.

But func.execute still seems to have a problem.
happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] can't find a way to index field with orm.

[Krishnakant Mane]

Hello all,
I feel I am seriously missing some point here.
suppose I am creating a class representing a table through orm.
I will asume that it is using declarative syntax.
I wish to know how do we index a field?
that is to say, I need to have indexes on a few fields so that they are 
applied to the tables in my postgresql database.
I know how to use primary key but wish to know if I can add indexes to 
the fields.

Happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy

[Krishnakant Mane]

I think the subject line makes it pritty clear.
I want to know how i can use the expression api to make calls to 
postgresql stored procedures written in plpgsql.
For example how to pass input parameters and how to manipulate cursor 
objects etc.

happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] still unclear how to invoke stored procedures with sqlalchemy

[Krishnakant Mane]

On 12/09/11 00:56, Michael Bayer wrote:

You use the func construct to invoke a function.  This can be passed to an 
execute() method directly where it should embed itself into a SELECT:

from sqlalchemy import func

result = engine.execute(func.name_of_my_pg_function(1, 2, 3))
So does it mean that name_of_my_pg_function is should be the name of the 
concerned stored procedure?
And let's say if I am using an ide for Python like pydev with eclipse, 
will func. give me list of those procedures which are available for calling?

happy hacking.
Krishnakant.



Manipulation of cursors is not supported by SQLAlchemy beyond calling the basic 
fetchone()/fetchmany()/fetchall() methods of DBAPI.   If you need non-standard cursor 
control methods like scroll(),  you can no longer use engine.execute() and 
need to use psycopg2 cursors directly:

http://initd.org/psycopg/docs/cursor.html

To get at a psycopg2 cursor from a SQLAlchemy engine:

connection = engine.raw_connection()
cursor = connection.cursor()

Usage is then that described at http://initd.org/psycopg/docs/cursor.html

cursor.execute(SELECT my_pg_function(%(param1)s, %(param2)s, 
%(param3)s), {'param1':1, 'param2':2, 'param3':3})

hope this helps !





On Sep 11, 2011, at 1:48 PM, Krishnakant Mane wrote:


I think the subject line makes it pritty clear.
I want to know how i can use the expression api to make calls to postgresql 
stored procedures written in plpgsql.
For example how to pass input parameters and how to manipulate cursor objects 
etc.
happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.



--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] any tips on balancing between performance and ease of use?

[Krishnakant Mane]

hello all,
I am thinking of programming a lot of stored procedures for my 
postgresql based application.
I would like to know if using the expression API is a way that can give 
me the power of sqlalchemy's eas and comfort, at the same time make use 
of the performance bennifits I will get from postgresql's stored 
procedure?  In short I would like to know if the approach is worthwile 
and how?

Happy hacking.
Krishnakant.


--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] any tips on balancing between performance and ease of use?

[Krishnakant Mane]

Thanks a lot.

On 01/09/11 19:16, Michael Bayer wrote:

I have some interest in working out ways to integrate stored procedures with SQLAlchemy 
though at the moment the points of integration are very rudimental.You can invoke a 
stored procedure, get results, and also create a selectable that would define 
the columns that come back from one (that's at 
http://www.sqlalchemy.org/docs/core/tutorial.html#functions).   Postgresql functions are 
also handy for various ad-hoc queries and I've used them for things like computing 
statistical values as columns.
And how can one do this?
I plan to stay with postgresql so database independence is not really my 
concern.

Happy hacking.
Krishnakant.

As far as a



--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Fwd: [Gnukhata-devel] not able to put in account name field

[Krishnakant Mane]

Hello all.
This might be interesting.
I don't know if we are doing some thing wrong.
We use session.query on a table and for adding records we use the orm.
But look at the forwarded email, I don't know what's wrong.
happy hacking.
Krishnakant.




 Original Message 
Subject:[Gnukhata-devel] not able to put  in account name field
Date:   Sun, 7 Aug 2011 17:35:51 +0530
From:   ankita shanbhag ankita.shanbhag...@gmail.com
To: gnukhata dev gnukhata-de...@cis-india.org



Hello All,

This may sound silly but entering  in account field eg A  B can
cause difficulty in retrival of that account from database.

I tried to query the Account table using postgres and its able to
fetch record properly.

I feel its a problem of SqlAlchemy.I am afraid if we want '' then we
have to look through rpc_account.py specially getAccount!!

Thanking you.


--
FOSS is not just about coding..its more of collaborative project management


ankita shanbhag
___
Gnukhata-devel mailing list
gnukhata-de...@cis-india.org
http://lists.cis-india.org/mailman/listinfo/gnukhata-devel


--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Fwd: [Gnukhata-devel] not able to put in account name field

[Krishnakant Mane]

I really don't think that's the issue.
no eval is used.
Secondly it did work through psycopg2 directly with postgresql
If some would have the kindness to look at the code I am paisting it here.
Sorry for making it long, it contains comments to explain the situation.

def xmlrpc_setAccount(self,queryParams,client_id):
'''
Purpose : Adds new account i.e row to the account table in the 
database
Parameters : It expects a list of queryParams which 
contains[suggestedcode(datatype:integer),groupcode(datatype:integer),subgroupcode(datatype:integer),accountname(datatype:text)openingbalance(datatype:numeric),openingdate(datatype:timestamp),balance(datatype:numeric)]

Returns : Boolean
Description : Querys the account table and add new row.
To add account first time check wether max of accountcode 
if there is nothing in that coloumn maxAccountcode will increament by 1 
and if there is max value then go to else condition and increament by 1
It takes  which is a foreign key from the group 
table,account name which is name of the account, and openingbalance 
which is previous financial year balance amount and opening date i.e 
when a new account is added (it always takes the todays date), balance 
is the balance amount remaining with that account.
When record entered successfully  it returns True else 
returns False.

'''
connection = dbconnect.engines[client_id].connect()
Session = dbconnect.session(bind=connection)
account_creation_date = str(strftime(%Y-%m-%d %H:%M:%S))
if queryParams[5] != 0:

Session.add(dbconnect.Account(queryParams[5],queryParams[0],queryParams[1],queryParams[2],queryParams[3],account_creation_date,queryParams[4]))

else:
maxAccountCode = 0
maxAccountCode = 
Session.query(func.count(dbconnect.Account.accountcode)).scalar()

if maxAccountCode == None:
maxAccountCode = 0
maxAccountCode = int(maxAccountCode) + 1
else:
maxAccountCode = int(maxAccountCode) + 1


Session.add(dbconnect.Account(maxAccountCode,queryParams[0],queryParams[1],queryParams[2],queryParams[3],account_creation_date,queryParams[4]))

Session.commit()
Session.close()


Mind you, it is an  xmlrpc call.
and the constructor of the table instance takes all the parameter hence 
the way in which session.add is coded.

Happy hacking.
Krishnakant.


On 07/08/11 21:11, Michael Bayer wrote:


On Aug 7, 2011, at 8:19 AM, Krishnakant Mane wrote:


Hello all.
This might be interesting.
I don't know if we are doing some thing wrong.
We use session.query on a table and for adding records we use the orm.
But look at the forwarded email, I don't know what's wrong.
happy hacking.


you'd need to know what happens when someone enters A  B.  If for 
example you're calling eval() on that or something,  is a Python 
operator.





Krishnakant.




 Original Message 
Subject:[Gnukhata-devel] not able to put  in account name field
Date:   Sun, 7 Aug 2011 17:35:51 +0530
From:   ankita shanbhag ankita.shanbhag...@gmail.com
To: gnukhata dev gnukhata-de...@cis-india.org



Hello All,

This may sound silly but entering  in account field eg A  B can
cause difficulty in retrival of that account from database.

I tried to query the Account table using postgres and its able to
fetch record properly.

I feel its a problem of SqlAlchemy.I am afraid if we want '' then we
have to look through rpc_account.py specially getAccount!!

Thanking you.


--
FOSS is not just about coding..its more of collaborative project management


ankita shanbhag
___
Gnukhata-devel mailing list
gnukhata-de...@cis-india.org
http://lists.cis-india.org/mailman/listinfo/gnukhata-devel


--
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 
mailto:sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.


--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.


--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group

[sqlalchemy] how to get last record from a resultset

[Krishnakant Mane]

Hello all,
Subject line says it all.
Basically what I want to do is to get last record from a result set.
I am dealing with a situation where given a date I need to know the last 
record pertaining to  transaction on a given account.

yes, it is an accounting/ book keeping software.
So  I thought there was some thing like .last() method for a resultset?
Or even better do we have some thing like session.query(table).last()
The problem is that my logic is in place but I know that performance 
wise it is very dirty to get the list of all records, just to loop till 
the end and throw away all the rest of the rows.

So plese suggest how can I only get just that one (last) record?
Happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] how to get last record from a resultset

[Krishnakant Mane]

Well, there won't be a consistent result using sort because there might 
be 10 rows with same voucher code and same account code.

That's exactly the challenge so I don't know how sort will help.
If we can invert the entire resultset having the last record become 
first,  then its worth while.

But again, I don't want the entire set of rows in the first place.
I just want that particular row.
Happy hacking.
Krishnakant.

On 20/07/11 19:20, Timuçin Kızılay wrote:

I think, reversing the sort and getting the first record will do.



20-07-2011 16:32, Krishnakant Mane yazmış:

Hello all,
Subject line says it all.
Basically what I want to do is to get last record from a result set.
I am dealing with a situation where given a date I need to know the last
record pertaining to transaction on a given account.
yes, it is an accounting/ book keeping software.
So I thought there was some thing like .last() method for a resultset?
Or even better do we have some thing like session.query(table).last()
The problem is that my logic is in place but I know that performance
wise it is very dirty to get the list of all records, just to loop till
the end and throw away all the rest of the rows.
So plese suggest how can I only get just that one (last) record?
Happy hacking.
Krishnakant.





--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] which documentation to read?

[Krishnakant Mane]

On 16/07/11 19:51, Michael Bayer wrote:

On Jul 16, 2011, at 6:08 AM, Krishnakant Mane wrote:


I am still not sure if all performance enhancements of 0.7 have been backported 
to 0.6.8.

No performance enhancements have been backported to 0.6.8.


Ok, So is the current 0.6 documentation uptodate with 0.6.8?
Secondly, I am about to release my free accounting software on 31st July.
So do you advice that I shift to 0.7.1?  Will I face some critical problems?
I use mostly ORM and in some cases (hardly 5%) the expression API.
happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] which is the current version for mission critical applications on production

[Krishnakant Mane]

On 12/07/11 20:34, Michael Bayer wrote:

Hi Krishnakant -

0.7.1 is the current stable production release which is where the focus of 
development also lies.   0.6 is in maintenance releases at this point.



In that case will I have to change my code if I want to shift from 0.6 
to 0.7?

I use Pylons as my web application framework.
In addition the major projecct I am working on is using sqlalchemy 
version 0.6.3 in its core engine.

The core engine sends and recieves xml rpc messages.
It then uses sqlalchemy  to talk with the database in postgresql.
I plan to use a lot of expression api so I will like to know overall 
what all changes will i have to make in my code which uses 0.6.

Happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] which is the current version for mission critical applications on production

[Krishnakant Mane]

On 12/07/11 23:36, Michael Bayer wrote:

On Jul 12, 2011, at 1:57 PM, Krishnakant Mane wrote:


On 12/07/11 20:34, Michael Bayer wrote:

Hi Krishnakant -

0.7.1 is the current stable production release which is where the focus of 
development also lies.   0.6 is in maintenance releases at this point.


In that case will I have to change my code if I want to shift from 0.6 to 0.7?
I use Pylons as my web application framework.
In addition the major projecct I am working on is using sqlalchemy version 
0.6.3 in its core engine.
The core engine sends and recieves xml rpc messages.
It then uses sqlalchemy  to talk with the database in postgresql.
I plan to use a lot of expression api so I will like to know overall what all 
changes will i have to make in my code which uses 0.6.
Happy hacking.
Krishnakant.

if you're on 0.6, you'd move up to 0.6.8 to get the latest fixes and such, and 
you can stay on 0.6 for the time being.

Moving to 0.7 requires little to no changes to calling code. But you would 
need to fully test your 0.6 application on 0.7 before moving into production. 
There are very few backwards incompatible changes overall, details at 
http://www.sqlalchemy.org/trac/wiki/07Migration


So  is 0.6.8 updated with the performance bennifits that we get in 0.7?
Happy ahcking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] which is the current version for mission critical applications on production

[Krishnakant Mane]

Hello all.
I have a very quick and short question.
which is the current production release of sqlalchemy.
I mean this in terms of performance and reliability.
let me narrow down  the choices as per my knowledge.
is it 0.6 or 0.7?
And if 0.6 then which minor version?
Happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] confused on avoiding sql injections using ORM

[Krishnakant Mane]

Hello all.
I use Pylons 0.9.7 and sqlalchemy.
I use the Object Relational Mapper with declarative syntax in a few of 
my modules.
I was reading chapter 7 of the Pylons book and I understood that sql 
injections can be avoided using the expression api.

But can this be also done using ORM?
I tryed on my software and sql injections do work.
Is it possible to avoide it with ORM or will i have to totally avoide 
using an ORM layer of sqlalchemy and only use the expression api?

Happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Re: confused on avoiding sql injections using ORM

[Krishnakant Mane]

On 05/07/11 03:03, Malthe Borch wrote:

Think about it this way:

There's two kinds of strings when you're dealing with SQL: 1) SQL
language, 2) your data input. Don't ever include (2) in (1) –– let the
API do it.


How does one do this with the orm?
I am talking about things like session.add etc, obviously for inserts.

Say I create an instance of a mapped class and then attach some values 
to it.

And want to do session.add.
Happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] how to have indexed columns in table definition?

[Krishnakant Mane]

Hello all.
The subject line says it all.
I use declarative syntax in my tables so that I can define and map 
tables in a single step.

Now I want to know how I can add index to a certain column.
I understand when we say prymary key it is already indexed or even 
foreign kay key for that matter.

But what if I want to index additional fields for performance reason?
I use postgresql 8.4 and will soon shift to 9.0 after testing.
Can some one tell me how to add index when a column is defined?
happy hacking.
Krishnakant.

--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] can some one give me sample on using max with session.query?

[Krishnakant Mane]

hello.
Can some one give me an example of the said query in the subject line?
I have a need to get the max on the id for a given table.
and I want to do it on a session.query(table_instance).max()
I know that is a wrong way, so what is the right syntax?
Happy hacking.
Krishnakant.



--
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Fwd: [Gnukhata-devel] Error installing gnukhata

[Krishnakant Mane]

Don't know why this might be happening on an Ubuntu 10.04 machine?
Can some one help solve this?

happy hacking.
Krishnakant.



 Original Message 
Subject:[Gnukhata-devel] Error installing gnukhata
Date:   Wed, 13 Oct 2010 11:06:00 +0400
From:   pooja bakshi pooja.dbak...@gmail.com
To: gnukhata-de...@cis-india.org



This is the error while installing GNUkhataserver/. Pls help.



po...@pooja-desktop:~$ cd GNUKhataServer/
po...@pooja-desktop:~/GNUKhataServer$ cd gnukhata-server/
po...@pooja-desktop:~/GNUKhataServer/gnukhata-server$ cd 
GNUKhata-ApplicationServer/
po...@pooja-desktop:~/GNUKhataServer/gnukhata-server/GNUKhata-ApplicationServer$ 
python rpc_main.py
/var/lib/python-support/python2.6/sqlalchemy/util.py:7: 
DeprecationWarning: the sets module is deprecated

  import inspect, itertools, new, operator, sets, sys, warnings, weakref
Traceback (most recent call last):
  File rpc_main.py, line 45, in module
import rpc_groups
  File 
/home/pooja/GNUKhataServer/gnukhata-server/GNUKhata-ApplicationServer/rpc_groups.py, 
line 31, in module

from sqlalchemy.orm import join
ImportError: cannot import name join
po...@pooja-desktop:~/GNUKhataServer/gnukhata-server/GNUKhata-ApplicationServer$ 
sudo su postgres

[sudo] password for pooja:
postg...@pooja-desktop:/home/pooja/GNUKhataServer/gnukhata-server/GNUKhata-ApplicationServer$ 
python rpc_main.py
/var/lib/python-support/python2.6/sqlalchemy/util.py:7: 
DeprecationWarning: the sets module is deprecated

  import inspect, itertools, new, operator, sets, sys, warnings, weakref
Traceback (most recent call last):
  File rpc_main.py, line 45, in module
import rpc_groups
  File 
/home/pooja/GNUKhataServer/gnukhata-server/GNUKhata-ApplicationServer/rpc_groups.py, 
line 31, in module

from sqlalchemy.orm import join
ImportError: cannot import name join
postg...@pooja-desktop:/home/pooja/GNUKhataServer/gnukhata-server/GNUKhata-ApplicationServer$ 






--

Regards,
Pooja Bakshi

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

___
Gnukhata-devel mailing list
gnukhata-de...@cis-india.org
http://lists.cis-india.org/mailman/listinfo/gnukhata-devel

[sqlalchemy] Is ORM the right choice for large scale data manipulation?

[Krishnakant Mane]

Hello all,
I will be interested to know if using ORM for a large scale data 
operation is the right approach when it comes to scalability.
Has SQLAlchemy been put to test ever for a select query which involves 
getting thousands of records and specially when joins are involved?

I have a financial software which needs such a system.
There will be lot of joins and will involve lot of complex queries.
If not then should I use the layer directly below the ORM?
Should I use some kind of query building tools which come with sqlalchemy?

Happy hacking.
Krishnakant.

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] SQLAlchemy gives QPool limit error, connection times out

[Krishnakant Mane]

Hello all,
I am using sqlalchemy in an xml rpc based application.
It is an API for accounting software.
The error given belo is encountered inconsistently on the application 
and I can't figure out why.


raise exc.TimeoutError(QueuePool limit of size %d overflow %d reached, 
connection timed out, timeout %d % (self.size(), self.overflow(), 
self._timeout))
sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 
reached, connection timed out, timeout 30


Happy hacking.
Krishnakant.

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] sqlalchemy 0.5.8 or 0.6.0?

[Krishnakant Mane]

Hello,
I am using pylons for my web application development.
Currently pylons is in version  1.0 and 0.9.7 is also going stable.
I want to know which is the correct version of sqlalchemy for both 
versions of Pylons.
I know it might not make that much of a difference but there are some 
changes in syntax, so I wonder if any of those changes affect the way 
sqlalchemy is used in Pylons.


Besides, I would like to know if the last release of version 0.6 
possesses any performance bennifits over 0.5.8 or is it just a release 
for cleanner syntax?


Happy hacking.
Krishnakant.

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] sqlalchemy 0.5.8 or 0.6.0?

[Krishnakant Mane]

So I asume that 0.6.0 is pritty stable.
Is all the documentation upto date?

Happy hacking.
Krishnakant.
On Sunday 30 May 2010 10:10 PM, Michael Bayer wrote:

Please see the list of enhancements in SQLAlchemy 0.6 at 
http://www.sqlalchemy.org/trac/wiki/06Migration .

Pylons itself does not make use of any deprecated features in SQLAlchemy.




On May 30, 2010, at 12:02 PM, Krishnakant Mane wrote:

   

Hello,
I am using pylons for my web application development.
Currently pylons is in version  1.0 and 0.9.7 is also going stable.
I want to know which is the correct version of sqlalchemy for both versions of 
Pylons.
I know it might not make that much of a difference but there are some changes 
in syntax, so I wonder if any of those changes affect the way sqlalchemy is 
used in Pylons.

Besides, I would like to know if the last release of version 0.6 possesses any 
performance bennifits over 0.5.8 or is it just a release for cleanner syntax?

Happy hacking.
Krishnakant.

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

 
   


--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] does indexing on database tables have any affect on sqlalchemy mapped classes

[Krishnakant Mane]

Hello,
This might be a very simple question to answer, but I am not finding any 
suitable benchmark tests so asking on the mailing list.
I want to know if indexing on certain columns impacts the performance of 
sqlalchemy?
for example I am using postgresql with python-psycopg2 and mapping all 
the tables to classes through alchemy.
Now if I follow the postgresql tuning tips and index the needed columns 
from the relevent tables, will sqlalchemy perform better?
My confusion is because I see that tables are mapped to classes and once 
that is done I don't directly interract with tables except through the 
engine.execute() where direct queries are passed.


So will tuning of database tables have direct impact on performance with 
sqlalchemy?


Happy hacking.
Krishnakant.

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] connection pooling question, is it possible

[Krishnakant Mane]

hello all,
I am working on a free software for accounting and rural banking in India.
We use Pylons for web application.
Now, my question is in reference to the recent threads on this mailing 
list regarding sqlalchemy connections.
I heard that after a certain amount of connections, the library does 
have some problems managing them.
I have looked at some emails but I would like if some one demystifies my 
understanding or misunderstanding.
My application is based on MVC Architecture and the core logic is coded 
as XMLRPC based server side APIs.
We create a connection for every new client which connects to the rpc 
server and maintain all the connections in a list.
now I forsee a situation where more than 500 connections might be alive 
at one time in the list.
I understand that sqlalchemy has some limitations on the number of 
connections (engines ) and their respective session objects which can be 
kept alive at the same time?

if this is true, can i create some kind of a connection pool for the server?
This way connections can be recycled and used for a lot of clients and 
new connections will only be created when needed.
I want to avoide this situation, so I really want to know if there is 
some kind of upper limit on the number of engines that can be active at 
one time.


Happy hacking.
Krishnakant.



--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Re: unable to understand this error

[Krishnakant Mane]

On Tuesday 16 February 2010 07:01 PM, avdd wrote:


Now the specific problem you see is that the ORM uses the declared
ForeignKeys to determine how to join tables for a relation, but on
your Detail table you have two foreign keys back to account and the
ORM doesn't know which one to use.

   

Even I have the same problem.
If one table has 2 columns as foreign keys which refer back to one 
column in the parent table, how do we sort this problem out?



Happy hacking.
Krishnakant.


On Feb 16, 9:49 pm, anusha kadambalaanusha.kadamb...@gmail.com
wrote:
   

hello all,

My tables are giving following error at the time of querying. The tables got
created properly but when i query on some table it is showing error on other
tables.I didnt understand whats the issue here. I am querying on login table
it is giving error on debitnote details tables.

Tables:
**
class Login(Base):
 __tablename__ = 'login'
 usercode = Column(Integer,primary_key=True)
 username = Column(Text)
 userpassword = Column(Text)
 userrole = Column(Text)

 def __init__(self,username,userpassword,userrole):
 self.username = username
 self.userpassword = userpassword
 self.userrole = userrole

login_table = Login.__table__

class Account(Base):
 __tablename__ = account
 accountcode =  Column(Integer, primary_key = True)
 groupcode = Column(Integer, ForeignKey(groups.groupcode), nullable =
False)
 groups = relation(Groups, backref = backref(Account, order_by =
accountcode))
 accountname = Column(Text, nullable = False)
 basedon = Column(Text)
 accountdesc  = Column(Text)
 openingbalance = Column(Numeric(13,2))
 openingdate = Column(TIMESTAMP)
 initialbalance = Column(Numeric(13,2))

 def
__init__(self,groupcode,groups,accountname,basedon,accountdesc,openingbalance,openingdate,initialbalance):
 self.groupcode = groupcode
 self.groups = groups
 self.accountname = accountname
 self.basedon = basedon
 self.accountdesc = accountdesc
 self.openingbalance = openingbalance
 self.openingdate = openingdate
 self.initialbalance = initialbalance

account_table = Account.__table__

class DebitnoteMaster(Base):
 __tablename__ = debitnotemaster
 vouchercode = Column(String(40), primary_key = True)
 sbillno = Column(String(40))
 voucherdate = Column(TIMESTAMP, nullable = False)
 reffdate = Column(TIMESTAMP)
 booktype = Column(Text)
 chequeno = Column(Text)
 bankname = Column(Text)
 debitnarration = Column(Text, nullable = False)

 def
__init__(self,vouchercode,sbillno,voucherdate,reffdate,booktype,chequeno,bankname,debitnarration):
 self.vouchercode = vouchercode
 self.sbillno = sbillno
 self.voucherdate = voucherdate
 self.reffdate = reffdate
 self.booktype = booktype
 self.chequeno = chequeno
 self.bankname = bankname
 self.debitnarration = debitnarration

debitnotemaster_table = DebitnoteMaster.__table__

class DebitnoteDetails(Base):
 __tablename__ = debitnotedetails
 dndtcode = Column(Integer, primary_key = True)
 vouchercode = Column(String(40),
ForeignKey(debitnotemaster.vouchercode))
 debitnotemaster = relation(DebitnoteMaster, backref =
backref(DebitnoteDetails, order_by = dndtcode))
 craccountcode = Column(Integer, ForeignKey(account.accountcode),
nullable = False)
 account = relation(Account, backref = backref(DebitnoteDetails,
order_by = dndtcode))
 draccountcode = Column(Integer, ForeignKey(account.accountcode),
nullable = False)
 account = relation(Account, backref = backref(DebitnoteDetails,
order_by = dndtcode))
 amount = Column(Numeric(13,2), nullable = False)

 def __init__(self,vouchercode,craccountcode,draccountcode,amount):
 self.vouchercode = vouchercode
 self.craccountcode = craccountcode
 self.draccountcode = draccountcode
 self.amount = amount

debitnotedetails_table = DebitnoteDetails.__table__



Error:
*

Traceback (most recent call last):
   File /usr/lib/python2.6/dist-packages/twisted/web/server.py, line 150,
in process
 self.render(resrc)
   File /usr/lib/python2.6/dist-packages/twisted/web/server.py, line 157,
in render
 body = resrc.render(self)
   File /usr/lib/python2.6/dist-packages/twisted/web/resource.py, line 190,
in render
 return m(request)
   File /usr/lib/python2.6/dist-packages/twisted/web/xmlrpc.py, line 118,
in render_POST
 defer.maybeDeferred(function, *args).addErrback(
---exception caught here  ---
   File /usr/lib/python2.6/dist-packages/twisted/internet/defer.py, line
106, in maybeDeferred
 result = f(*args, **kw)
   File
/home/sonal/Desktop/gnukhata_alpha/gnukhata-server/GNUKhata-ApplicationServer/rpc_user.py,
line 53, in xmlrpc_getUser
 res = Session.query(dbconnect.Login).filter