Re: variable expansion with sqlite
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
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
Re: variable expansion with sqlite
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
Re: variable expansion with sqlite
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
Re: variable expansion with sqlite
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
Re: variable expansion with sqlite
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 [EMAIL PROTECTED] 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