Re: formating query with empty parameter

2009-05-25 Thread Pet 
On May 25, 2:15 pm, "Diez B. Roggisch"  wrote:
> someone wrote:
> > Hello!
>
> > if one of parameter in values is empty, I'm getting
> > TypeError: not enough arguments for format string
>
> > But how to handle such situation? It is ok for DB, that some of values
> > are empty.
>
> > def __insert(self, data):
> >         query = """
> >             BEGIN;
> >                 INSERT INTO table
> >                     (a,  b,  c,  d,  e,  f,  g)
> >                     VALUES
> >                     (%s, %s, %s, %s, %s, %s, %s);
> >             COMMIT;
> >             """
> >         values = [
> >             data['a'],
> >             data['b'],
> >             data['c'],
> >             data['d'],
> >             data['e'],
> >             data['f'],
> >             data['g']
> >             ]
> >         self.db.execute(query, *values)
>
> You need to pass
>
> None

Hi,

thanks for reply.
Unfortunately, it doesn't work. Still getting TypeError: not enough
arguments for format string


>
> then as that parameter.
>
> Diez

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: formating query with empty parameter

2009-05-25 Thread Pet 
On May 25, 2:25 pm, Pet  wrote:
> On May 25, 2:15 pm, "Diez B. Roggisch"  wrote:
>
>
>
>
>
> > someone wrote:
> > > Hello!
>
> > > if one of parameter in values is empty, I'm getting
> > > TypeError: not enough arguments for format string
>
> > > But how to handle such situation? It is ok for DB, that some of values
> > > are empty.
>
> > > def __insert(self, data):
> > >         query = """
> > >             BEGIN;
> > >                 INSERT INTO table
> > >                     (a,  b,  c,  d,  e,  f,  g)
> > >                     VALUES
> > >                     (%s, %s, %s, %s, %s, %s, %s);
> > >             COMMIT;
> > >             """
> > >         values = [
> > >             data['a'],
> > >             data['b'],
> > >             data['c'],
> > >             data['d'],
> > >             data['e'],
> > >             data['f'],
> > >             data['g']
> > >             ]
> > >         self.db.execute(query, *values)
>
> > You need to pass
>
> > None
>
> Hi,
>
> thanks for reply.
> Unfortunately, it doesn't work. Still getting TypeError: not enough
> arguments for format string
>
>
>
>
>
> > then as that parameter.
>
> > Diez

Sorry, for previous quick post. Actually it works now, I've missed
some other parameter in list

Thanks again!
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: formating query with empty parameter

2009-05-25 Thread Pet 
On May 25, 2:50 pm, Peter Otten <[email protected]> wrote:
> Pet wrote:
> > > someone wrote:
> > > > Hello!
>
> > > > if one of parameter in values is empty, I'm getting
> > > > TypeError: not enough arguments for format string
>
> > > > But how to handle such situation? It is ok for DB, that some of values
> > > > are empty.
>
> > > > def __insert(self, data):
> > > >         query = """
> > > >             BEGIN;
> > > >                 INSERT INTO table
> > > >                     (a,  b,  c,  d,  e,  f,  g)
> > > >                     VALUES
> > > >                     (%s, %s, %s, %s, %s, %s, %s);
> > > >             COMMIT;
> > > >             """
> > > >         values = [
> > > >             data['a'],
> > > >             data['b'],
> > > >             data['c'],
> > > >             data['d'],
> > > >             data['e'],
> > > >             data['f'],
> > > >             data['g']
> > > >             ]
> > > >         self.db.execute(query, *values)
>
> > > You need to pass
>
> > > None
>
> > Hi,
>
> > thanks for reply.
> > Unfortunately, it doesn't work. Still getting TypeError: not enough
> > arguments for format string
>
> The code you posted doesn't match that error message. You have to invoke
> cursor.execute() as
>
> cursor.execute(query, values) # correct
>
> , not
>
> cursor.execute(query, *values) # wrong

as far as I know it is not wrong, at least for pyPgSQL it takes values
and escapes properly preventing sql injections

>
> or
>
> cursor.execute(query % values) # wrong
>
> The length of values must match the number of "%s" occurences in the sql
> query, but as Diez indicated you may pass None for every field that allows a
> NULL value in the table.
>
> Peter

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: formating query with empty parameter

2009-05-25 Thread Pet 
On May 25, 3:26 pm, Tim Chase  wrote:
>  if one of parameter in values is empty, I'm getting
>  TypeError: not enough arguments for format string
>  But how to handle such situation? It is ok for DB, that some of values
>  are empty.
>  def __insert(self, data):
>          query = """
>              BEGIN;
>                  INSERT INTO table
>                      (a,  b,  c,  d,  e,  f,  g)
>                      VALUES
>                      (%s, %s, %s, %s, %s, %s, %s);
>              COMMIT;
>              """
>          values = [
>              data['a'],
>              data['b'],
>              data['c'],
>              data['d'],
>              data['e'],
>              data['f'],
>              data['g']
>              ]
>          self.db.execute(query, *values)
>
> > Sorry, for previous quick post. Actually it works now, I've missed
> > some other parameter in list
>
> To stave off this problem, I often use:
>
>    values = [
>     data['a'],
>     data['b'],
>     data['c'],
>     data['d'],
>     data['e'],
>     data['f'],
>     data['g'],
>     ]
>    params = ', '.join('%s' for _ in values)
>    query = """
>      BEGIN;
>        INSERT INTO table
>          (a,b,c,d,e,f,g)
>        VALUES (%s);
>      COMMIT;
>      """ % params
>    self.db.execute(query, values)
>

Why do you pass values to execute() if you already have your query
formatted?

> If the indexes are named the same as the fieldnames, or you have
> a mapping of them, I tend to use something like
>
>    field_map = {
>      # dictionary_index: database_fieldname
>      # data['a'] -> table.f1
>      'a': 'f1',
>      'b': 'f2',
>      'c': 'f3',
>      # ...
>      }
>    name_value_pairs = (
>      (data[k], v)
>      for k,v
>      in fieldmap.iteritems())
>    values, fieldnames = zip(*name_value_pairs)
>    # may want to do fieldname escaping here:
>    fieldname_string = ', '.join(fieldnames)
>    params = ', '.join('%s' for _ in ordering)
>
>    query = """
>      BEGIN;
>        INSERT INTO table (%s) VALUES (%s);
>      COMMIT;
>      """ % (fieldname_string, params)
>    self.db.execute(query, values)
>
> -tkc

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: formating query with empty parameter

2009-05-25 Thread Pet 
On 25 Mai, 18:16, Tim Chase  wrote:
> >> To stave off this problem, I often use:
>
> >>    values = [
> >>     data['a'],
> >>     data['b'],
> >>     data['c'],
> >>     data['d'],
> >>     data['e'],
> >>     data['f'],
> >>     data['g'],
> >>     ]
> >>    params = ', '.join('%s' for _ in values)
> >>    query = """
> >>      BEGIN;
> >>        INSERT INTO table
> >>          (a,b,c,d,e,f,g)
> >>        VALUES (%s);
> >>      COMMIT;
> >>      """ % params
> >>    self.db.execute(query, values)
>
> > Why do you pass values to execute() if you already have your query
> > formatted?
>
> The "params" might be better named "placeholders".  So after the

O, thanks for clarification, I've completely missed the point of
params = ', '.join
>
>     query = "..." % params
>
> the query looks like your original (go ahead and print "query" to
> see), only the number of placeholders ("%s") is guaranteed to
> match the number of values you pass in during the execute() call.
>   The second iteration I gave goes one step further to ensure
> that the "(a,b,c,d,e,f,g)" portion also matches in count to the
> number of values and place-holders to be used.
>
> Once you have a SQL query that matches what you plan to pass
> (based on your initial data-structure:  a list/tuple or a
> dictionary), then you call execute(query, values) to have the
> database then associate the parameter-placeholders ("%s") with
> the corresponding value from "values".
>
> -tkc

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: unicode confusing

2009-05-26 Thread Pet 
On May 25, 6:07 pm, Paul Boddie  wrote:
> On 25 Mai, 17:39, someone  wrote:
>
> > Hi,
>
> > reading content of webpage (encoded in utf-8) with urllib2, I can't
> > get parsed data into DB
>
> > Exception:
>
> >   File "/usr/lib/python2.5/site-packages/pyPgSQL/PgSQL.py", line 3111,
> > in execute
> >     raise OperationalError, msg
> > libpq.OperationalError: ERROR:  invalid UTF-8 byte sequence detected
> > near byte 0xe4
>
> > I've already checked several python unicode tutorials, but I have no
> > idea how to solve my problem.
>
> With pyPgSQL, there are a few tricks that you have to take into
> account:
>
> 1. With PostgreSQL, it would appear advantageous to create databases
> using the "-E unicode" option.

Hi,

DB is in UTF8


>
> 2. When connecting, use the client_encoding and unicode_results
> arguments for the connect function call:
>
>   connection = PgSQL.connect(client_encoding="utf-8",
> unicode_results=1)

If I do unicode_results=1, then there are exceptions in other places,
e.g. urllib.urlencode(values)
cant encode values

>
> 3. After connecting, it appears necessary to set the client encoding
> explicitly:
>
>   connection.cursor().execute("set client_encoding to unicode")

I've tried this as well, but still have exceptions

>
> I'd appreciate any suggestions which improve on the above, but what
> this should allow you to do is to present Unicode objects to the
> database and to receive such objects from queries. Whether you can
> relax this and pass UTF-8-encoded strings instead of Unicode objects
> is not something I can guarantee, but it's usually recommended that
> you manipulate Unicode objects in your program where possible, and
> here you should be able to let pyPgSQL deal with the encodings
> preferred by the database.
>

Thanks for your suggestions! Sadly, I can't solve my problem...

Pet

> Paul

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: unicode confusing

2009-05-26 Thread Pet 
On May 26, 9:29 am, Pet  wrote:
> On May 25, 6:07 pm, Paul Boddie  wrote:
>
>
>
>
>
> > On 25 Mai, 17:39, someone  wrote:
>
> > > Hi,
>
> > > reading content of webpage (encoded in utf-8) with urllib2, I can't
> > > get parsed data into DB
>
> > > Exception:
>
> > >   File "/usr/lib/python2.5/site-packages/pyPgSQL/PgSQL.py", line 3111,
> > > in execute
> > >     raise OperationalError, msg
> > > libpq.OperationalError: ERROR:  invalid UTF-8 byte sequence detected
> > > near byte 0xe4
>
> > > I've already checked several python unicode tutorials, but I have no
> > > idea how to solve my problem.
>
> > With pyPgSQL, there are a few tricks that you have to take into
> > account:
>
> > 1. With PostgreSQL, it would appear advantageous to create databases
> > using the "-E unicode" option.
>
> Hi,
>
> DB is in UTF8
>
>
>
> > 2. When connecting, use the client_encoding and unicode_results
> > arguments for the connect function call:
>
> >   connection = PgSQL.connect(client_encoding="utf-8",
> > unicode_results=1)
>
> If I do unicode_results=1, then there are exceptions in other places,
> e.g. urllib.urlencode(values)
> cant encode values
>
>
>
> > 3. After connecting, it appears necessary to set the client encoding
> > explicitly:
>
> >   connection.cursor().execute("set client_encoding to unicode")
>
> I've tried this as well, but still have exceptions
>
>
>
> > I'd appreciate any suggestions which improve on the above, but what
> > this should allow you to do is to present Unicode objects to the
> > database and to receive such objects from queries. Whether you can
> > relax this and pass UTF-8-encoded strings instead of Unicode objects
> > is not something I can guarantee, but it's usually recommended that
> > you manipulate Unicode objects in your program where possible, and
> > here you should be able to let pyPgSQL deal with the encodings
> > preferred by the database.
>
> Thanks for your suggestions! Sadly, I can't solve my problem...
>
> Pet
>
>
>
> > Paul

After some time, I've tried, to convert result with unicode(result,
'ISO-8859-15') and that was it :)
I've thought it was already utf-8, because of charset defining in
 of webpage I'm fetching
Pet
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: better way?

2009-08-11 Thread Pet 
On 11 Aug., 20:39, Kushal Kumaran 
wrote:
> On Tue, Aug 11, 2009 at 10:03 PM, someone wrote:
> > Hello,
>
> > I'd like to make insert into db if record not exist otherwise update.
> > to save typing list of columns in both statements I do following
>
> > 
>
> > is there better or more readable way to do it?
>
> Well, mysql, in particular, offers an "on duplicate key update" clause
> that you can take a look at.  Don't know about about other databases.
>
> --
> kushal

Oh, forgotten to mention. It's PostGres
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: better way?

2009-08-11 Thread Pet 
On 11 Aug., 21:23, "Rami Chowdhury"  wrote:
> IIRC Postgres has had ON DUPLICATE KEY UPDATE functionality longer than  
> MySQL...
>

Ok, I've completely failed here :)

Thanks, man

>
>
>
>
> On Tue, 11 Aug 2009 11:45:50 -0700, Pet  wrote:
> > On 11 Aug., 20:39, Kushal Kumaran 
> > wrote:
> >> On Tue, Aug 11, 2009 at 10:03 PM, someone  
> >> wrote:
> >> > Hello,
>
> >> > I'd like to make insert into db if record not exist otherwise update.
> >> > to save typing list of columns in both statements I do following
>
> >> > 
>
> >> > is there better or more readable way to do it?
>
> >> Well, mysql, in particular, offers an "on duplicate key update" clause
> >> that you can take a look at.  Don't know about about other databases.
>
> >> --
> >> kushal
>
> > Oh, forgotten to mention. It's PostGres
>
> --
> Rami Chowdhury
> "Never attribute to malice that which can be attributed to stupidity" --  
> Hanlon's Razor
> 408-597-7068 (US) / 07875-841-046 (UK) / 0189-245544 (BD)

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: better way?

2009-08-11 Thread Pet 
On 11 Aug., 22:19, "Rami Chowdhury"  wrote:
> Ah, my apologies, I must have been getting it confused with ON UPDATE  
> [things]. Thanks for correcting me.
>
> On Tue, 11 Aug 2009 13:10:03 -0700, Matthew Woodcraft  
>
>  wrote:
> > "Rami Chowdhury"  writes:
>
> >> IIRC Postgres has had ON DUPLICATE KEY UPDATE functionality longer than
> >> MySQL...
>
> > PostgreSQL does not have ON DUPLICATE KEY UPDATE.
>
> > The SQL standard way to do what the OP wants is MERGE. PostgreSQL
> > doesn't have that either.
>
> > -M-
>
> --
> Rami Chowdhury
> "Never attribute to malice that which can be attributed to stupidity" --  
> Hanlon's Razor
> 408-597-7068 (US) / 07875-841-046 (UK) / 0189-245544 (BD)

So, I'm doing it in right way?
What about building columns? map(lambda s: s + ' = %s', fields)
Is that o.k.?

thanks
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: better way?

2009-08-12 Thread Pet 
On 12 Aug., 09:14, Dennis Lee Bieber  wrote:
> On Tue, 11 Aug 2009 11:45:50 -0700 (PDT), Pet 
> declaimed the following in gmane.comp.python.general:
>
> > Oh, forgotten to mention. It's PostGres
>
>         Really? There are still installations of an RDBMS that predates the
> commonalization of SQL?
>
>         I suspect you mean PostgreSQL -- it IS a different beast from the
> older Postgres.

O, yeah. Of course it is PostgreSQL

>
>         In either event -- my old books don't show an "all in one" solution.
>
>         Best answer is probably to create some stored procedures which you
> call instead of plain INSERT; the stored procedure could then do
> whatever is needed to check for a duplicate (actually the easiest, I'd
> think, would be: if primary key is supplied, it is an UPDATE; if primary
> key is NULL or not supplied, it is an insert and the primary key will be
> auto-generated).

I don't really like the idea of stored procedure, because query would
depend on existence of it then. On the other side, it looks like best
option.

>
> --
>         Wulfraed         Dennis Lee Bieber               KD6MOG
>         [email protected]     HTTP://wlfraed.home.netcom.com/

-- 
http://mail.python.org/mailman/listinfo/python-list

Re: better way?

2009-08-12 Thread Pet 
On Aug 12, 4:29 pm, Scott David Daniels  wrote:
> Pet wrote:
> > On 11 Aug., 22:19, "Rami Chowdhury"  wrote:
> >> Ah, my apologies, I must have been getting it confused with ON UPDATE  
> >> [things]. Thanks for correcting me.
>
> >> On Tue, 11 Aug 2009 13:10:03 -0700, Matthew Woodcraft  
>
> >>  wrote:
> >>> "Rami Chowdhury"  writes:
> >>>> IIRC Postgres has had ON DUPLICATE KEY UPDATE functionality longer than
> >>>> MySQL...
> >>> PostgreSQL does not have ON DUPLICATE KEY UPDATE.
> >>> The SQL standard way to do what the OP wants is MERGE. PostgreSQL
> >>> doesn't have that either.
>
> > So, I'm doing it in right way?
> > What about building columns? map(lambda s: s + ' = %s', fields)
> > Is that o.k.?
>
> Isn't
>      t = [field + ' = %s' for field in fields]
> clearer than
>      t = map(lambda s: s + ' = %s', fields)
> ? your call of course.

Yes, I think so

> I don't quite understand why you are building the SQL from data
> but constructing the arguments in source.  I'd actually set the
> SQL up directly as a string, making both the SQL and Python more

Sometimes, column list could be long, besides I keep column list in
sync for both update and insert query

> readable. To the original question, you could unconditionally
> perform a queries vaguely like:
>
> UPDATE_SQL = '''UPDATE table ...
>       WHERE id = %s AND location = %s;'''
> INSERT_SQL = '''INSERT INTO table(...
>       WHERE NOT EXISTS(SELECT * FROM table
>                        WHERE id = %s AND location = %s;);'''
> I'd put the NOW() and constant args (like the 1) in the SQL itself.

yes, but I'm building both UPDATE in INSERT from same list of columns,
so I didn't found better way as this one


> then your code might become:
>      row = (self.wl, name, location, id)
>      self._execQuery(db, UPDATE_SQL, [row])
>      self._execQuery(db, INSERT_SQL, [row + (location, id)])

I'm going to rebuild my queries like that.

Thank you very much!


> if _execQuery is like the standard Python DB interfaces.  Having
> the SQL do the checking means you allows the DB to check its
> index and use that result to control the operation, simplifying
> the Python code without significantly affecting the the DB work
> needed.  The "SELECT *" form in the EXIST test is something DB
> optimizers look for, so don't fret about wasted data movement.
>
> --Scott David Daniels
> [email protected]

-- 
http://mail.python.org/mailman/listinfo/python-list

Python developers

2013-04-27 Thread act pet 


http://tech.groups.yahoo.com/group/Python_Developers/


-- 
http://mail.python.org/mailman/listinfo/python-list