Date: Thu, 19 Feb 2015 08:17:58 -0600
From: Tim Chase <python.list@tim.thechases.com>
To: Mark Lawrence <breamoreboy@yahoo.co.uk>
Subject: Re: 'Lite' Databases (Re: sqlite3 and dates)
In-Reply-To: <mc3shp$215$1@ger.gmane.org>
References: <mc1atd$cq0$1@ger.gmane.org> <mailman.18805.1424243139.18130.python-list@python.org> <mc1s1e$feh$1@news.albasani.net> <mailman.18815.1424258499.18130.python-list@python.org> <4154cc37-0bb0-4bf2-a52c-b728c737357c@googlegroups.com> <CAPTjJmogRj_==1wUcLooFwDhUK+saSaAkQEuUo4o30n8aRdB=w@mail.gmail.com> <54E517B4.4000409@stoneleaf.us> <mailman.18843.1424302365.18130.python-list@python.org> <qt8aeaprecp11btjfiaht8q8d7mh6osqpv@4ax.com> <mc3shp$215$1@ger.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Cc: python-list@python.org
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.18882.1424355395.18130.python-list@python.org>
Lines: 33
NNTP-Posting-Host: 2001:888:2000:d::a6
Path: csiph.com!usenet.pasdenom.info!bete-des-vosges.org!feed.ac-versailles.fr!nerim.net!novso.com!newsfeed.xs4all.nl!newsfeed1.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
Xref: csiph.com comp.lang.python:85903

On 2015-02-19 05:32, Mark Lawrence wrote:
> On 19/02/2015 00:08, Mario Figueiredo wrote:
> > Parameterized queries is just a pet peeve of mine that I wish to
> > include here. SQLite misses it and I miss the fact SQLite misses
> > it. The less SQL one needs to write in their code, the happier
> > one should be.
>=20
> Instead, use the DB-API=E2=80=99s parameter substitution. Put ? as a
> placeholder wherever you want to use a value, and then provide a
> tuple of values as the second argument to the cursor=E2=80=99s execute()
> method. (Other database modules may use a different placeholder,
> such as %s or :1.) For example:..."

I think Mario was referring to what other back ends call prepared
statements. So you do something like

 =20
  sql =3D "..." # parameters are referenced here
  conn =3D sqlite3.connect(...)
  stmt =3D conn.prepare(sql)
  for parameters in list_of_parameters:
    stmt.execute(*parameters)

This saves the SQL processor from recompiling the SQL into internal
byte-code every time.  It's handy if you know a given query will run
multiple times with the same "shape" parameters.  It's not essential,
and some optimize away the need, but many back-end interfaces support
it.

-tkc