pypgsql-users

[Pypgsql-users] problems with returned data sets

[Timothy S. <ti...@op...>]

Hello, when i use fetch all i have a problem with what s returned. 
everything is returned as this string ['data'] which makes is very 
inconvienent to work it. is there better way of fetching things then i 
am using? here is my code

cur = db.cursor()
cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName = UserName")
groups = cur.fetchone()

Re: [Pypgsql-users] problems with returned data sets

[Gerhard H. <gh...@gh...>]

On Thu, Nov 04, 2004 at 04:26:26PM +1000, Timothy Smith wrote:
> Hello, when i use fetch all i have a problem with what s returned.
> everything is returned as this string ['data'] which makes is very
> inconvienent to work it. is there better way of fetching things then
> i am using? here is my code
>=20
> cur =3D db.cursor()
> cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName =3D UserNa=
me")
> groups =3D cur.fetchone()

The DB-API requires that the return value of the fetchXX methods is a
sequence. So even if you only select one column, you still get a
sequence of length 1.

To access the columns in the sequence, you can use index-based access:
groups[0]. This will work with all database adapters.

Much more convenient, but not portable is pyPgSQL's extension to
access columns in the result row by name. Two ways to do this are
supported, dictionary-style and attribute-style:

groups["UserGroup"]
groups.UserGroup

HTH,

-- Gerhard
--=20
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

Re: [Pypgsql-users] problems with returned data sets

[Timothy S. <ti...@op...>]

ok i get that but it is still giving me problems. the error i get is
libpq.OperationalError: ERROR:  column "groupmembership" does not exist

but it DOES exisit? there must be some syntax error i'm making. pyPgSQL 
has bearly any real documentation so these errors are easy to make....

#Get groups which this user belongs to
    cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName = 
UserName")
    Groups = cur.fetchone()
    GroupMembership = Groups[0]

*snip*  
   
    #Get menu items which this user has access to
    cur.execute("SELECT MenuName FROM MenuItems WHERE UserGroup = 
GroupMembership")


Gerhard Haering wrote:

>On Thu, Nov 04, 2004 at 04:26:26PM +1000, Timothy Smith wrote:
>  
>
>>Hello, when i use fetch all i have a problem with what s returned.
>>everything is returned as this string ['data'] which makes is very
>>inconvienent to work it. is there better way of fetching things then
>>i am using? here is my code
>>
>>cur = db.cursor()
>>cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName = UserName")
>>groups = cur.fetchone()
>>    
>>
>
>The DB-API requires that the return value of the fetchXX methods is a
>sequence. So even if you only select one column, you still get a
>sequence of length 1.
>
>To access the columns in the sequence, you can use index-based access:
>groups[0]. This will work with all database adapters.
>
>Much more convenient, but not portable is pyPgSQL's extension to
>access columns in the result row by name. Two ways to do this are
>supported, dictionary-style and attribute-style:
>
>groups["UserGroup"]
>groups.UserGroup
>
>HTH,
>
>-- Gerhard
>  
>

Re: [Pypgsql-users] problems with returned data sets

[Gerhard H. <gh...@gh...>]

On Thu, Nov 04, 2004 at 05:55:11PM +1000, Timothy Smith wrote:
> ok i get that but it is still giving me problems. the error i get is
> libpq.OperationalError: ERROR:  column "groupmembership" does not exist
>=20
> but it DOES exisit? there must be some syntax error i'm making. pyPgSQL=
=20
> has bearly any real documentation so these errors are easy to make....
>=20
> #Get groups which this user belongs to
>    cur.execute("SELECT UserGroup FROM UserMenuInfo WHERE UserName =3D=20
> UserName")
>    Groups =3D cur.fetchone()
>    GroupMembership =3D Groups[0]
>=20
> *snip* =20
>  =20
>    #Get menu items which this user has access to
>    cur.execute("SELECT MenuName FROM MenuItems WHERE UserGroup =3D=20
> GroupMembership")

"WHERE UserGroup =3D GroupMembership" means you join two columns in SQL. To
compare a column with a string, you use something like "WHERE UserGroup =3D
'Group1'", to compare it with a number "WHERE UserGroup =3D 42'.

Now, it appears you want to parametrize your query. That's what the additio=
nal
parameter of .execute() is for: to give it a sequence of query parameters:

cur.execute("SELECT Menuname FROM MenuItems WHERE UserGroup =3D %s", (Group=
Membership,))

The placeholder in pyPgSQL is always %s.

-- Gerhard
--=20
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?