Calling upon data in just 1 cell from sqlite in python
Question: My question is essentially twofold. Firstly, I have a database using SQlite,
which I'm running with Python.

My database is:

    
    
    CREATE TABLE cards (id integer primary key autoincrement not null, ref text unique
    check(ref!=''), name text, description text, quantity integer default 0, cat1 text,
    cat2 text);
    
    INSERT INTO "cards" VALUES(1,'lx247','green door',NULL,20,'abstract','');
    INSERT INTO "cards" VALUES(2,'lxx247','green door',NULL,20,'abstract','');
    INSERT INTO "cards" VALUES(3,'lxx2f47','green door',NULL,20,'abstract','');
    

And I'm running the following python code:

    
    
    import sqlite3 as lite
    import sys
    con = lite.connect('cards.db')
    idn = raw_input("Enter your ID number")
    with con:
        cur=con.cursor()
        cur.execute("SELECT quantity FROM cards WHERE ref=?", idn)  
        print "Quantity" + str(cur.fetchone())
    

Firstly, when I execute this script, and enter 'lx247' into the prompt, I get
the following error message:

    
    
    Enter your ID numberlx247
    Traceback (most recent call last):
      File "test1.py", line 7, in <module>
        cur.execute("SELECT quantity FROM cards WHERE ref=?", idn)  
    sqlite3.ProgrammingError: Incorrect number of bindings supplied. 
    The current statement uses 1, and there are 5 supplied.
    

I've identified that the 5 it refers to are the number of characters in what I
enter into the idn input (lx247). If I change the line:

    
    
    cur.execute("SELECT quantity FROM cards WHERE ref=?", idn)  
    

to

    
    
    cur.execute("SELECT quantity FROM cards WHERE id=?", idn)   
    

and enter '2' at the prompt, it returns:

    
    
    Enter your ID number2
    Quantity(20,)
    

So this has told me there is something wrong with the way it is handling the
idn parameter to look up the ref column. So my first question is **how do I
get it to look up the value I enter in idn in column ref**?

The second question is how do I then get it to output the value as a number,
rather than in the form (20,), because I then want to later direct python to
add or subtract another number, x, to it and update the entry in the database
to 20+x, so I'm looking to create a variable in python that is the value of
the quantity column for a certain ref entry.


Answer: You need to pass in a _sequence_ of parameters, make it a tuple with one
element:

    
    
    cur.execute("SELECT quantity FROM cards WHERE ref=?", (idn,))  
    

Python strings are sequences too, so if `idn` is a 5-character string, python
sees a sequence of length 5.

The database always returns _rows_ of columns, even for results with only one
column. A row is essentially a Python tuple, a sequence. It's the same problem
as your `idn` parameter, but in reverse. Take the one column out of the result
row by indexing:

    
    
    print "Quantity " + str(cur.fetchone()[0])
    

A quick demo with your sample database:

    
    
    >>> import sqlite3
    >>> con = sqlite3.connect(':memory:')
    >>> con.executescript('''\
    ... CREATE TABLE cards (id integer primary key autoincrement not null, ref text unique
    ... check(ref!=''), name text, description text, quantity integer default 0, cat1 text,
    ... cat2 text);
    ... 
    ... INSERT INTO "cards" VALUES(1,'lx247','green door',NULL,20,'abstract','');
    ... INSERT INTO "cards" VALUES(2,'lxx247','green door',NULL,20,'abstract','');
    ... INSERT INTO "cards" VALUES(3,'lxx2f47','green door',NULL,20,'abstract','');
    ... ''')
    <sqlite3.Cursor object at 0x10ad66ab0>
    >>> idn = 'lx247'
    >>> cur=con.cursor()
    >>> cur.execute("SELECT quantity FROM cards WHERE ref=?", (idn,))
    <sqlite3.Cursor object at 0x10ad66b20>
    >>> print "Quantity " + str(cur.fetchone()[0])
    Quantity 20
    

