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