Arch Linux

marxav

Member

From: Gatineau, PQ, Canada

Registered: 2006-09-24

Posts: 386

Python insert sqlite list

Been browsing and I cannot figure out how I would do something like in python using sqlite3 module:

mylist=['field1', 'field2', 'field3', 'field4']
insert into packages (pkgname, pkgver, pkgrel, pkgdesc) values (?, ?, ?, ?), mylist

If I use something like

insert into packages (pkgname, pkgver, pkgrel, pkgdesc) values (%s, %s, %s, %s), mylist

It won't handle the quotes if it exists inside my list item.

Dusty

Schwag Merchant

From: Medicine Hat, Alberta, Canada

Registered: 2004-01-18

Posts: 5,986

Website

Re: Python insert sqlite list

In [4]: import sqlite3

In [5]: conn = sqlite3.connect(":memory:")

In [6]: cur = conn.cursor()

In [7]: cur.execute('create table packages (pkgname varchar, pkgver varchar, pkgrel varchar, pkgdesc varchar)')
Out[7]: <sqlite3.Cursor object at 0x87515c0>

In [8]: mylist = ['field1', 'field2', 'field3', 'field4']

In [9]: cur.execute('insert into packages (pkgname, pkgver, pkgrel, pkgdesc) values(?,?,?,?)', mylist)
Out[9]: <sqlite3.Cursor object at 0x87515c0>

In [10]: cur.execute('select * from packages')
Out[10]: <sqlite3.Cursor object at 0x87515c0>

In [11]: print cur.fetchall()
-------> print(cur.fetchall())
[(u'field1', u'field2', u'field3', u'field4')]

Last edited by Dusty (2009-02-08 17:41:42)

---

** Arch Linux Apparel and more ** Arch Linux stickers, laptop bags, jewelery ** I wrote a book on Python 3 ** and one on mental health **

Husio

Member

From: Europe

Registered: 2005-12-04

Posts: 359

Website

Re: Python insert sqlite list

read the examples: http://docs.python.org/library/sqlite3. … le-sqlite3

# Never do this -- insecure!
symbol = 'IBM'
c.execute("... where symbol = '%s'" % symbol)

# Do this instead
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

marxav

Member

From: Gatineau, PQ, Canada

Registered: 2006-09-24

Posts: 386

Re: Python insert sqlite list

Dusty thanks for such an elaborate answer.  At least I am happy to see I am on the right track.  I tried on the python command line and it works with my codes.  But when I run my script, I get

ValueError: operation parameter must be str or unicode

And this is my insert command:

('insert into packages (pkgname, pkgver, pkgrel, pkgdesc) values (?,?,?,?)', ['3ddesktop', '0.2.9', '2', 'a 3d virtual desktop switcher (opengl/mesa)'])

Husio, I visited that page probably to many times as well as other prior to posting.  Everything was working fine with the %s parameter until I met " and ' in the string to insert and looks like they are not escaped.

EDIT
I found something.  It fails for these entries that have unicode:
Insert fail for  djvulibre    Suite to create, manipulate and view DjVu ('déjà vu') documents
Insert fail for  libdjvu    Library to process DjVu ('déjà vu') documents
Insert fail for  scim-hangul    SCIM íê¸ (hangul) input method engine for Korean language
Insert fail for  scim-pinyin    Chinese æ¼é³ (PinYin) input for SCIM

So it is a unicode thing...

Last edited by marxav (2009-02-09 01:32:41)

Radek

Member

From: Warsaw in Poland

Registered: 2006-08-20

Posts: 23

Website

Re: Python insert sqlite list

Try connecting with database this way:

connection = sqlite.connect('test.db')
connection.text_factory = str
cursor = connection.cursor()

---

"Physics is like sex: sure, it may give some practical results, but that's not why we do it."
Richard P. Feynman.