Python and Sqlite

I am trying to create a program using Python 3.1 and Sqlite3. The program will open a text file and read parameters to pass for the select query and output a text file with the result. I am getting stuck on the cursor.execute(query) statement. I may be doing everything incorrect. Any help would be appreciated.

import sqlite3
# Connect to database and test
#Make sure the database is in the same folder as the python script folder
conn = sqlite3.connect("nnhs.sqlite3")
if (conn):
  print ("Connection successful")
else:
  print ("Connection not successful")

# Create a cursor to execute SQL queries
cursor = conn.cursor()  
# Read data from a file
data = []
infile = open ("patient_in.txt", "r")

for line in infile:
  line = line.rstrip("\n")
  line = line.strip()
  seq = line.split(' ')
  seq[5] = int(seq[5])
  seq = tuple (seq)
  data.append(seq)
  infile.close()

# Check that the data has been read correctly
print
print ("Check that the data was read from file")
print (data)

# output file
outfile = open("patient_out.txt", "w")

# select statement
query = "SELECT DISTINCT patie开发者_如何学运维nts.resnum, patients.facnum, patients.sex, patients.age, patients.rxmed, icd9_1.resnum, icd9_1.code  "
query += "from patients "
query += "INNER JOIN icd9 as icd9_1 on (icd9_1.resnum = patients.resnum) AND (icd9_1.code LIKE ':6%') "
query += "INNER JOIN icd9 as icd9_2 on (icd9_2.resnum = patients.resnum) AND (icd9_2.code LIKE ':6%') "
query += "(where patients.age >= :2) AND (patients.age <= :3) "
query += "AND patients.sex = :1 "
query += "AND (patients.rxmed >= :4) AND (patients.rxmed <= :5) "
query += "ORDER BY patients.resnum;"
result = cursor.execute(query)
for row in result:
    ResultNumber = row[0]
    FacNumber = row[1]
    Sex = row[2]
    Age = row[3]
    RxMed = row[4]
    ICDResNum = row[5]
    ICDCode = row[6]
    outfile.write("Patient Id Number: " + str(ResultNumber) + "\t" + " ICD Res Num: " + str(ICDResNum) + "\t" + " Condition: " + str(ICDCode) + "\t" + " Fac ID Num: " + str(FacNumber) + "\t" + " Patient Sex: " + str(Sex) + "\t" + " Patient Age: " + str(Age) + "\t" +" Number of Medications: " + str(RxMed) + "\t" + "\n")



# Close the cursor
cursor.close()

# Close the connection
con.close()

You have read multiple rows of query parameters and stored them in data and then ... nothing. data is a misleading name. Let's call it queries instead.

You presumably want to iterate over queries and perform one query for each row in queries. So do that: for query_row in queries: .....

Also let's rename query to sql.

You'll need result = cursor.execute(sql, query_row)

You'll also need to decide whether you want to have a different output file for each query_row, or have only one file with a field (or sub-heading) to distinguish what info comes from what query_row.

Update about parameter passing with sqlite3

It appears not to be documented, but if you use numbered place holders, you can supply a tuple of arguments -- you don't need to supply a dict. The following example presumes a database blah.db with an empty table created by

create table foo (id int, name text, amt int);

>>> import sqlite3
>>> conn = sqlite3.connect('blah.db')
>>> curs = conn.cursor()
>>> sql = 'insert into foo values(:1,:2,:1);'

>>> curs.execute(sql, (42, 'bar'))
<sqlite3.Cursor object at 0x01E3D520>
>>> result = curs.execute('select * from foo;')
>>> print list(result)
[(42, u'bar', 42)]

>>> curs.execute(sql, {'1':42, '2':'bar'})
<sqlite3.Cursor object at 0x01E3D520>
>>> result = curs.execute('select * from foo;')
>>> print list(result)
[(42, u'bar', 42), (42, u'bar', 42)]

>>> curs.execute(sql, {1:42, 2:'bar'})
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: You did not supply a value for binding 1.
>>>

Update 2a You have a bug in this line of your SQL (and the following one):

INNER JOIN icd9 as icd9_1 on (icd9_1.resnum = patients.resnum) AND (icd9_1.code LIKE ':6%')

If your parameter is the Python string "XYZ", the resultant SQL will be ... LIKE ''XYZ'%') which is not what you want. The db interface will always quote your supplied string. You want ... LIKE 'XYZ%'). What you should do is have ... LIKE :6) in your SQL, and pass e.g. user_input[5].rstrip("%") + "%" (ensures exactly 1 %) as the parameter.

Update 2b You can of course use a dictionary for the parameters, as documented, but it would improve the legibility considerably if you used meaningful names instead of digits.

For example, ... LIKE :code) instead of the above, and pass e.g. {'code': user_input[5].rstrip("%"), .....} as the second arg of execute()

:2? These are placeholders for parameters, which you're not giving it. Take a look at the module docs for how to call execute with a tuple of parameters:

http://docs.python.org/library/sqlite3.html#sqlite3.Cursor.execute

Yeah, it looks like you need to add the parameters. Also, changing the line query += "(where patients.age >= :2) AND (patients.age <= :3) " to query += "where (patients.age >= :2) AND (patients.age <= :3) " might help.

Here is a slightly more python-ish way of writing your code... Although PEP8 might say otherwise...

import sqlite3

with open sqlite3.connect("nnhs.sqlite3") as f:
    cursor = f.cursor()
    data = []
    with open("patient_in.txt", "r") as infile:
        for line in infile:
            line = line.rstrip("\n")
            line = line.strip()
            seq = line.split(' ')
            seq[5] = int(seq[5])
            seq = tuple (seq)
            data.append(seq)

    print(data)

    with open("patient_out.txt", "w") as outfile:
        query = """SELECT DISTINCT patients.resnum, patients.facnum,patients.sex, patients.age, patients.rxmed, icd9_1.resnum, icd9_1.code
                from patients
                INNER JOIN icd9 as icd9_1 on (icd9_1.resnum = patients.resnum) AND (icd9_1.code LIKE ':6%')
                INNER JOIN icd9 as icd9_2 on (icd9_2.resnum = patients.resnum) AND (icd9_2.code LIKE ':6%')
                (where patients.age >= :2) AND (patients.age <= :3)
                AND patients.sex = :1
                AND (patients.rxmed >= :4) AND (patients.rxmed <= :5)
                ORDER BY patients.resnum"""
        variables = {'1':sex, '2':age_lowerbound, '3':age_upperbound, '4':rxmed_lowerbound, '5':rxmed_upperbound, '6':icd9_1}
        cursor.execute(query, variables)
        for row in cursor.fetchall():
            outfile.write("Patient Id Number: {0}\t ICD Res Num: {1}\t Condition: {2}\t Fac ID Num: {3}\t Patient Sex: {4}\t Patient Age: {5}\t Number of Medications: {6}\n".format(*row))