Mailing List Archive: Python: Python

variable expansion with sqlite

Index | Next | Previous | View Threaded

marc.wyburn at googlemail

Jul 30, 2008, 4:45 AM

Post #1 of 6 (293 views)
Permalink

variable expansion with sqlite

Hi I'm using SQlite and the CSV module and trying to create a class
that converts data from CSV file into a SQLite table.

My script curently uses functions for everything and I'm trying to
improve my class programming. The problem I'm having is with variable
expansion.

self.cursor.executemany('INSERT INTO test VALUES (?)', CSVinput)

If CSVinput is a tuple with only 1 value, everything is fine. If I
want to use a tuple with more than 1 value, I need to add more
question marks. As I'm writing a class I don't want to hard code a
specific number of ?s into the INSERT statement.

The two solutions I can think of are;
using python subsitution to create a number of question marks, but
this seems very dirty
or
finding someway to substitue tuples or lists into the statement - I'm
not sure if this should be done using Python or SQLite substitution
though.

Any tips on where to start looking?

Thanks, Marc.
--
http://mail.python.org/mailman/listinfo/python-list

mail at timgolden

Jul 30, 2008, 5:09 AM

Post #2 of 6 (281 views)
Permalink

Re: variable expansion with sqlite [In reply to]

marc wyburn wrote:
> Hi I'm using SQlite and the CSV module and trying to create a class
> that converts data from CSV file into a SQLite table.
>
> My script curently uses functions for everything and I'm trying to
> improve my class programming. The problem I'm having is with variable
> expansion.
>
> self.cursor.executemany('INSERT INTO test VALUES (?)', CSVinput)
>
> If CSVinput is a tuple with only 1 value, everything is fine. If I
> want to use a tuple with more than 1 value, I need to add more
> question marks. As I'm writing a class I don't want to hard code a
> specific number of ?s into the INSERT statement.
>
> The two solutions I can think of are;
> using python subsitution to create a number of question marks, but
> this seems very dirty
> or
> finding someway to substitue tuples or lists into the statement - I'm
> not sure if this should be done using Python or SQLite substitution
> though.

I do this kind of thing sometimes:

<test.csv>
a,b,c
1,2,3
4,5,6
</test.csv>

<code>
import csv
import sqlite3

reader = csv.reader (open ("test.csv", "rb"))
csv_colnames = reader.next ()

db = sqlite3.connect (":memory:")
coldefs = ", ".join ("%s VARCHAR (200)" % c for c in csv_colnames)
db.execute ("CREATE TABLE test (%s)" % coldefs)

insert_cols = ", ".join (csv_colnames)
insert_qmarks = ", ".join ("?" for _ in csv_colnames)
insert_sql = "INSERT INTO test (%s) VALUES (%s)" % (insert_cols, insert_qmarks)

db.executemany (insert_sql, list (reader))
for row in db.execute ("SELECT * FROM test"):
print row

</code>

Obviously, this is a proof-of-concept code. I'm (ab)using
the convenience functions at database level, I'm hardcoding
the column definitions, and I'm making a few other assumptions,
but I think it serves as an illustration.

Of course, you're only a few steps away from something
like sqlalchemy, but sometimes rolling your own is good.

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

gh at ghaering

Jul 30, 2008, 6:15 AM

Post #3 of 6 (279 views)
Permalink

Re: variable expansion with sqlite [In reply to]

Tim Golden wrote:
> marc wyburn wrote:
>> Hi I'm using SQlite and the CSV module and trying to create a class
>> that converts data from CSV file into a SQLite table.
>>
>> My script curently uses functions for everything and I'm trying to
>> improve my class programming. The problem I'm having is with variable
>> expansion.
>>
>> self.cursor.executemany('INSERT INTO test VALUES (?)', CSVinput)
>>
>> If CSVinput is a tuple with only 1 value, everything is fine. If I
>> want to use a tuple with more than 1 value, I need to add more
>> question marks. As I'm writing a class I don't want to hard code a
>> specific number of ?s into the INSERT statement.
>>
>> The two solutions I can think of are;
>> using python subsitution to create a number of question marks, but
>> this seems very dirty
>> or
>> finding someway to substitue tuples or lists into the statement - I'm
>> not sure if this should be done using Python or SQLite substitution
>> though.
>
>
> I do this kind of thing sometimes:
>
> <test.csv>
> a,b,c
> 1,2,3
> 4,5,6
> </test.csv>
>
> <code>
> import csv
> import sqlite3
>
> reader = csv.reader (open ("test.csv", "rb"))
> csv_colnames = reader.next ()
>
> db = sqlite3.connect (":memory:")
> coldefs = ", ".join ("%s VARCHAR (200)" % c for c in csv_colnames)
> db.execute ("CREATE TABLE test (%s)" % coldefs)
>
> insert_cols = ", ".join (csv_colnames)
> insert_qmarks = ", ".join ("?" for _ in csv_colnames)
> insert_sql = "INSERT INTO test (%s) VALUES (%s)" % (insert_cols,
> insert_qmarks)
>
> db.executemany (insert_sql, list (reader))
> for row in db.execute ("SELECT * FROM test"):
> print row
>
> </code>
>
> Obviously, this is a proof-of-concept code. I'm (ab)using
> the convenience functions at database level, I'm hardcoding
> the column definitions, and I'm making a few other assumptions, but I
> think it serves as an illustration. [..]

My code would probably look very similar. Btw you don't need to use
list() on an iterable to pass to executemany(). pysqlite's executemany()
accepts anything iterable (so generators work fine, too).

Also, with SQLite you can just skip data type definitions like
VARCHAR(200). They're ignored anyway.

-- Gerhard

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

mail at timgolden

Jul 30, 2008, 6:24 AM

Post #4 of 6 (274 views)
Permalink

Re: variable expansion with sqlite [In reply to]

Gerhard H�ring wrote:
> My code would probably look very similar. Btw you don't need to use
> list() on an iterable to pass to executemany(). pysqlite's executemany()
> accepts anything iterable (so generators work fine, too).

Thanks for that. My finger-memory told me to do that, possibly
because some *other* dbapi interface only accepts lists. Can't
quite remember. I'm usually all in favour of non-crystallised
iterators.

> Also, with SQLite you can just skip data type definitions like
> VARCHAR(200). They're ignored anyway.

Heh. Once again, finger memory forced me to put *something*
in there. I've been developing Enterprise databases for too
long :)

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

marc.wyburn at googlemail

Jul 30, 2008, 8:39 AM

Post #5 of 6 (266 views)
Permalink

Re: variable expansion with sqlite [In reply to]

Hi and thanks,

I was hoping to avoid having to weld qmarks together but I guess
that's why people use things like SQL alchemy instead. It's a good
lesson anyway.

Thanks, Marc.

On Jul 30, 2:24�pm, Tim Golden <m...@timgolden.me.uk> wrote:
> Gerhard H�ring wrote:
> > My code would probably look very similar. Btw you don't need to use
> > list() on an iterable to pass to executemany(). pysqlite's executemany()
> > accepts anything iterable (so generators work fine, too).
>
> Thanks for that. My finger-memory told me to do that, possibly
> because some *other* dbapi interface only accepts lists. Can't
> quite remember. I'm usually all in favour of non-crystallised
> iterators.
>
> > Also, with SQLite you can just skip data type definitions like
> > VARCHAR(200). They're ignored anyway.
>
> Heh. Once again, finger memory forced me to put *something*
> in there. I've been developing Enterprise databases for too
> long :)
>
> TJG

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

kris at FreeBSD

Aug 8, 2008, 6:16 PM

Post #6 of 6 (260 views)
Permalink

Re: variable expansion with sqlite [In reply to]

marc wyburn wrote:
> Hi and thanks,
>
> I was hoping to avoid having to weld qmarks together but I guess
> that's why people use things like SQL alchemy instead. It's a good
> lesson anyway.

The '?' substitution is there to safely handle untrusted input. You
*don't* want to pass in arbitrary user data into random parts of an SQL
statement (or your database will get 0wned). I think of it as a
reminder that when you have to construct your own query template by
using "... %s ..." % (foo) to bypass this limitation, that you had
better be darn sure the parameters you are passing in are safe.

Kris

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

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads