Re: Style formating of multiline query, advise
On Mar 19, 10:26 am, Marco Mariani wrote: > someone wrote: > >> Also, for SQL, (A) why are you using nested joins?, and > > > inner select produce smaller set which is then joined with other > > table, kind a optimization > > Did you time it? I've did explain on both kinds of query (with nested select and without) and query plan is identical to both. So, there is no difference, well in PostGres > I've done some "kind of a optimization" that slowed queries by tenfold, > because postgres didn't need my advice, and knew better. RDBMS > performance is non-intuitive, really. If you're using mysql, YMMV, > because its optimizer is not as good. > > > Yes, my original question was about formatting. It's not original > > query (only a part). > > Try this: > > http://www.dpriver.com/pp/sqlformat.htm > > My 2c: I use textwrap.dedent to strip leading spaces from every line. -- http://mail.python.org/mailman/listinfo/python-list
Re: Escaping optional parameter in WHERE clause
On Mar 23, 1:48 pm, MRAB wrote:
> someone wrote:
> > Hi,
>
> > as you can see below I have some optional parameter for my query (mf,
> > age). They are in WHERE clause only if not empty.
> > In this function they are not escaped as, for example, 'search'
> > parameter, cause I can't pass them to execute function, which does
> > escaping automatically.
>
> > I could write another if's block like that
>
> > if mf and not age:
> > db.execute(query, search, mf, limit)
> > if age and not mf:
> > db.execute(query, search, age, limit)
> > if age and mf:
> > db.execute(query, search, mf, age, limit)
>
> > Is there a better way to deal with optional WHERE clause?
>
> > Pet
>
> > def getData(self, db, params):
> > search = params.get('search','')
> > age = params.get('age','')
> > mf = params.get('mf','')
> > limit = params.get('limit',1)
>
> > if mf:
> > mf = " AND mf = %s " % mf
> > if age:
> > age = " AND age = %s " % age
>
> > query = """
> > SELECT * FROM mytable
> > WHERE class = 'P'
> > AND name = %s
> > """ + mf + """
> > """ + age + """
> > ORDER BY id DESC
> > LIMIT %s;
> > """
>
> > db.execute(query, search, limit)
> > result = db.fetchall()
> > return result
>
> How about:
>
> def getData(self, db, params):
> search = params.get('search', '')
> age = params.get('age', '')
> mf = params.get('mf', '')
> limit = params.get('limit', 1)
>
> query = """
> SELECT * FROM mytable
> WHERE class = 'P'
> AND name = %s
> """
> values = [search]
>
> if mf:
> query += " AND mf = %s"
> values.append(mf)
>
> if age:
> query += " AND age = %s"
> values.append(age)
>
> query += """
> ORDER BY id DESC
> LIMIT %s;
> """
> values.append(limit)
>
> db.execute(query, *values)
> result = db.fetchall()
> return result
Like it. Thanks, man!
--
http://mail.python.org/mailman/listinfo/python-list
Re: install pyPgSQL on Windows for python 2.5
On Mar 24, 4:57 pm, Dietmar Schwertberger wrote: > someone wrote: > > Hi, > > > does anyone know how to install pyPgSQL on Windows? There is no > > package for Python 2.5 on Homepage: > > I've installed newest Visual C++ Studio 2008 from Microsoft, but still > > no luck > > Hello Pet, > > you need Visual Studio 2003 to compile extensions for Python 2.5 > If you want, I can send you a binary. O, thank you very much! Where should I put hole thing? > > Regards, > > Dietmar -- http://mail.python.org/mailman/listinfo/python-list
