Extracting values from SQLite database in Python

Question

I was trying to figure out how to work with SQLite database using python, but seem to be stuck. I think I am missing something basic. I was following this tutorial: http://docs.python.org/library/sqlite3.html

I set up a database such that it contains the following information:

import sqlite3
conn = sqlite3.connect('SQL_test_3') #this creates a seperate file
c = conn.cursor()
c.execute('''create table stocks
(date text, trans text, symbol text,
 qty real, price real)''')
data = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]

for t in data:
    c.execute('insert into stocks values (?,?,?,?,?)', t)

conn.commit()

c.close()

My problems arise when I try to extract the data; the tutorial explains how to extract the data if one of the characteristics are satisfied, for example:

(When I open the database in another file)

import sqlite3

conn = sqlite3.connect('SQL_test_3')

c = conn.cursor()

price = 53
t = (price,)
c.execute('select * from stocks where price=?', t)
print c.fetchall()

The above works perfect, but what if I want extract information for all the assets who's price is greater that 50, I can't do it. Doing price > 50 and price>? doesn't work...

So my questions are:

1) How do I extract information for an asset when the key criteria is fits in a given range, for example price > 50 or 40 < price < 70.

2)And what if I wanted to have two criteria, for example information for IBM stocks, and if the stock was traded at, say, price greater than 50.

I feel that my questions are very beginner/basic, but I couldn't find the answers to them in the tutorial.

Any help is appreciated.

Thanks in Advance.

I tried running your code and it works fine for me. Please post the exact code you tried using > and the output you got when you ran it. — Duncan
– Duncan, Commented Feb 6, 2012 at 15:49
Duncan, I got that line to work with: price = 50 t = (price,) c.execute('select * from stocks where price>?', t) z4 = c.fetchall() print z4 Thanks — Akavall
– Akavall, Commented Feb 6, 2012 at 16:23

Mike Graham · Accepted Answer · 2012-02-06 16:20:07Z

3

c.execute('select * from stocks where price > ?', (50,))

c.execute('select * from stocks where price between ? and ?', (40, 70))

c.execute('select * from stocks where price > ? and symbol = ?', (50, 'IBM'))

Is this ok or do you need a universal solution?

edited Feb 6, 2012 at 16:20

Mike Graham

77.2k16 gold badges105 silver badges131 bronze badges

answered Feb 6, 2012 at 15:51

eumiro

214k36 gold badges307 silver badges264 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Duncan Over a year ago

That first one won't work: you need a tuple not a single value.

Akavall Over a year ago

Your first and second lines still don't work for me. Your 3rd line does work fine. Thanks.

Akavall Over a year ago

OK. The problem was on my end. I got it. Thanks a lot!

Collectives™ on Stack Overflow

Extracting values from SQLite database in Python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related