tty Python Sqlite app: Noob NameError, Unexpected EOF, and sqlite error

Question

I am porting a tcl script to python, because I can't seem to get the tclSqlite thing going on my Nokia N810. The script prompts for inputs, passes them to a sqlite db with 3 tables: Notes, Tags, and a NotesXTags many-to-many tbl. There are triggers that keep me from storing any tags more than once. Being a noob/hobbyist I went line by line through the tcl script replacing each with a Python line. Not very Pythonic, but I'm a hobbyist with no intention of using the language after I get this one script to work on N810. I did look at every Q&A S.O. suggested and I've been working on this for hours. I've got at least 3 bugs-of-ignorance. A chunk of the script in a module called 'pythonmakenote.py':

the crunch-bang ... and some comments ....

import sys, tty
import sqlite3

def mn():
    conn = sqlite3.connect('/home/j...notes.sqlite')
    db = conn.cursor()
tagsofar =db.execute('select tag_text from tag')
print tagsofar

print "Enter note text, remember to let console wrap long lines"
notetxt = input("note: ") 

print "Enter 1 or more tags separated by spaces"
taglist = input("tags: ")
taglist = taglist.split(" ")

db.execute('INSERT INTO note (note_txt) VALUES (?)', notetxt)
db.commit
fknote = db.execute('select last_insert_rowid()')

#records new tags since db trigger stops dups, updates many-many tbl

for tagtxt in taglist: 
    db.execute('INSERT INTO tag VALUES (?)',tagtxt)
    db.commit
    fktag = db.execute('select rowid from tag where tag_text = (?)',tagtxt)
    db.execute('INSERT INTO fkeys VALUES (?,?)',fknote,fktag)
    db.commit

So I do 'import pythonmakenote'. So far so good. I type 'mn' and get an error:

>>> mn
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'mn' is not defined

Then I try this:

>>> from pythonmakenote import mn
>>> mn
<function mn at 0xb76b2a74>

But 'mn' still doesn't work. So I remove the Def altogether and copy the file and name it 'mn.py' and it sort-of works...

>>> import mn
<sqlite3.Cursor object at 0xb75fb740>
Enter note text, remember to let console wrap long lines
note: 'this is a note'<--------------------------Quotes are a MUST (but not in tcl version)
Enter 1 or more tags separated by spaces
tags: 'dev'
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "mn.py", line 19, in <module>
    db.execute('INSERT INTO note (note_txt) VALUES (?)', notetxt)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 14 supplied.<-----------------------------Huh?

Where in the world are the S.O. instructions on code block tags and other markdown?

Why can't I Def mn in a module and use it? Is Python: NameError: global name 'foobar' is not defined pertinent? (to my problem)

I've got several other Defs to do for getting notes by tag, getting the tag list, etc.. and I thought they could all go in one module.

I don't want to put quotes around my inputs (the notes or the space-delimited tag list). Is that doable? I have the import tty thing in there but I'm not using it (don't know how but I'm beginning to suspect I'll have to learn)

If I put 3 tags in when prompted, without quotes, I get the 'unexpected EOF' error. Why?

I see strings are immutable so maybe assigning the list/split to a var that was a string before could be a problem?

Where does sqlite get '14' bindings supplied? I'm splitting on the space char but it's being ignored (because I'm doing it wrong)?

Would it be easier to do my little project in Bash?

Thanks to anyone who takes the time to get this far. I have a bad habit of needing help in areas off-topic in S.U. and too noob-RTFM here. I struggled a little with the tcl version but it now works like a champ. I expected Python to be somewhat straightforward. Still think it might be.

edit: WOW. A bunch of newlines got stripped out. Sorry I have no idea how to fix. I'm off to see if "raw_input" works better.

Answer 1

You'll want raw_input instead of input in your script. input evaluates what you type, which is why you have to enter quotes.

You can markdown code using the {} buttons above the input window. That actual markdown for code is a preceding 4 spaces.

db.commit needs to be db.commit().

If you do this:

>>> import pythonmakenote

To run mn do this:

>>> pythonmakenote.mn()

You can also do:

>>> from pythonmakenote import mn
>>> mn()

For lines like:

db.execute('INSERT INTO note (note_txt) VALUES (?)', notetxt)

You need:

db.execute('INSERT INTO note (note_txt) VALUES (?)', (notetxt,))

execute expects a sequence, so if you pass a string, it acts as a sequence of single characters, hence your 14 bindings error (it was a string of 14 characters). (xxx,) is the syntax for a 1-element tuple. Making it a list [xxx] would work too.

Here's my best guess at something that works. I don't have your database:

import sys
import sqlite3

def mn():
    conn = sqlite3.connect('data.db')
    db = conn.cursor()
    db.execute('select tag_text from tag')
    tagssofar = db.fetchall()
    print tagssofar

    print "Enter note text, remember to let console wrap long lines"
    notetxt = raw_input("note: ") 

    print "Enter 1 or more tags separated by spaces"
    taglist = raw_input("tags: ")
    taglist = taglist.split()

    db.execute('INSERT INTO note (note_txt) VALUES (?)', [notetxt])
    conn.commit()
    db.execute('select last_insert_rowid()')
    fknote = db.fetchone()[0]
    print fknote

    #records new tags since db trigger stops dups, updates many-many tbl

    for tagtxt in taglist: 
        db.execute('INSERT INTO tag VALUES (?)',[tagtxt])
        conn.commit()
        db.execute('select rowid from tag where tag_text = (?)',[tagtxt])
        fktag = db.fetchone()[0]
        print fktag
        db.execute('INSERT INTO fkeys VALUES (?,?)',[fknote,fktag])
        conn.commit()

Answer 2

There are a couple of things going on here. First, mn does not return anything, you would want something like:

>>> def mn():
...     conn = sqlite3.connect('tester.db')
...     cur = conn.cursor()
...     return cur
... 
>>> c = mn()

This leaves an open connection, so when you are done with c you would call:

>>> c.connection.close()

Also, the executing on the cursor does not return anything, you need to call some fetch method, ie fetchone or fetchall. Putting a few of these things together I would start to modify as follows:

import sys, tty
import sqlite3

def mn():
    conn = sqlite3.connect('/home/j...notes.sqlite')
    cur = conn.cursor()
    return cur

db = mn()

tags_so_far = db.execute('select tag_text from tag').fetchall()

print tags_so_far

print "Enter note text, remember to let console wrap long lines \n"
notetxt = raw_input("note: ")

print "Enter 1 or more tags separated by spaces \n"
taglist = raw_input("tags: ").split()

db.execute('INSERT INTO note (note_txt) VALUES (?)', notetxt)
db.commit()

fknote = db.execute('select last_insert_rowid()').fetchone()[0]

#records new tags since db trigger stops dups, updates many-many tbl

for tagtxt in taglist:
    db.execute('INSERT INTO tag VALUES (?)', (tagtxt,))
    db.commit()
    fktag = db.execute('select rowid from tag where tag_text = (?)',tagtxt)
    db.execute('INSERT INTO fkeys VALUES (?,?)',fknote,fktag)