Re: [sqlalchemy] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE
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
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
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
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
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
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
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.
You could also say conn = engine.connect().execution_options(autocommit=True).
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.
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
Re: [sqlalchemy] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE
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.
To
Re: [sqlalchemy] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE
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] HOW TO HANDLE SPECIAL CHARACTERS WITH ENGINE.EXECUTE
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.
