Hi folks,

I've got a little problem with my queries in sqlite3.
I need to select 'brands' (a, b, c, ...) for a 'date' YYYY-MM-DD from my table.
As you can see in my script, I'm able to build a query by passing 'selectedDate'
or 'selection', but not both values. Is there a solution for this ?

There is a test.txt in attachment. Just rename it test.csv

Any help or advice will be apreciated.

Regards,

JMD

"""

"""

test.csv

   * ---> The fields that I need.
   
Ord Col.  Field description
--- ----  -----------------
00   A
01   B
02   C
03   D
04   E
05   F
06   G
07   H
08   I
09   J
10   K
11   L
12   M
13 * N    Item
14   O
15 * P    Original partNo.
16   Q
17 * R    Brand/Set
18 * S    Art.Nr.
19 * T    Brand
20 * U    Internal part No.
21 * V    Loc.
22 * W    Alt. part No.
23   X
24 * Y    Description
25 * Z    Date IN file
26 * AA   Quantity
27 * AB   Price
28   AC
29   AD
30   E
31   AF
32   AG
33   AH
34 * AI   Comments
35   AJ
36   AK
37   AL


"""



# Imports.
# --------

import csv
import os
from os.path import getsize, getmtime
import sys
import time
import sqlite3 as sqlite

from mx.DateTime import *                       # See www.egenix.com/ 


# Constants & variables.
# ----------------------

iso_date = now().iso_week
time_stamp = ARPA.str(now())
iso_stamp = str(iso_date[0])+'W'+\
           str(iso_date[1]).rjust(2,'0')+\
           str(iso_date[2]).replace("0","")
iso_stamp_h = (str(hex(iso_date[0]))+\
             str(hex(iso_date[1]))+\
             str(hex(iso_date[2])))
warning_1 = " Hit RETURN or enter a valid item. See list below.\n"
sql_columns = [13,15,17,18,19,20,21,22,24,25,26,27,34]
columns = [26,20,22,13,15,17,18,19,21,24,25,27] # The columns I need in custom order.
counter = 0
length = 0
InputFile = 'test.csv'                          # UTF-8
DatabaseFile = 'test_.sql3'
Brands = ("DAEWOO","LG","PHILIPS","PIONEER","SANYO","SONY")


# Dictionaries.
# -------------


# Functions & Procedures.
# -----------------------

def add_unique_postfix(fn):
    '''    Function 'add_unique_postfix'.

    Function for making unique non-exixtent file name
    with saving source file extension.

    __author__ = 'Denis Barmenkov <denis.barmenkov@gmail.com>'
    __source__ = 'http://code.activestate.com/recipes/577200-make-unique-file-name/'


    '''
    
    if not os.path.exists(fn):
        return fn
    print "Warning! File",fn,"already exists!"
    path, name = os.path.split(fn)
    name, ext = os.path.splitext(name)
    make_fn = lambda i: os.path.join(path, '%s_(%d)%s' % (name, i, ext))
    for i in xrange(2, sys.maxint):
        uni_fn = make_fn(i)
        if not os.path.exists(uni_fn):
            print "File will be saved as :",uni_fn,"\n"
            return uni_fn
    return None


# Main.
# -----

fi = open(InputFile,'rb')                       # Open the .csv file.
date_ = getmtime(InputFile)                     # Get some file info.
size = getsize(InputFile)
fi_data = fi.readline()
fi_dialect = csv.Sniffer().sniff(fi_data)       # Sniff a line and display file info.
FieldDelimiter = fi_dialect.delimiter           
fi.seek(0)
rec=0
for line in fi.readlines():
    rec += 1
fi.seek(0)
print "ISO week stamp  :", iso_stamp, iso_stamp_h
print "File name       :", InputFile
print "Date modified   :", TimestampFromTicks(date_)
print "Size            :",
if len(str(size)) > 3 and size >= 1024:
    print str(int(round(size/1024))),"Kb."
else:
    print str(size).rjust(10,' '),"bytes."
print "Dialect         :", fi_dialect
print "Field delimiter :", FieldDelimiter
print "Records         :", str(rec).rjust(10,' ')

try:                                            # Header or not ?
    header = csv.Sniffer().has_header(fi.read(size/2))
    if header:
        print "\nHeader found."
        fi.seek(0)
        header = fi.readline()
        print "Length :",
        print len(header),
        print 'bytes.'
        offset = len(header) + 1
        header = header.split(FieldDelimiter)
        print "Fields :",
        print len(header)-1,'\n'
    else:
        print "No header.\n"
        offset = 0
finally:
    fi.seek(offset)

"""
brand_list = []                                 # Create lists.
date_list = []
reader = csv.reader(fi, delimiter = FieldDelimiter)
for row in reader:
    if row[19] not in brand_list:
        brand_list.append(row[19])
    if row[25] not in date_list:
        date_list.append(row[25])

        

# Get brand.
while True:
    selectedBrand = raw_input("Enter BRAND                 : ").upper()
    if selectedBrand in brand_list:
        break
    else:
        counter = 1
        print "BRAND unknow ..." + warning_1
        for entry in sorted (brand_list):
            if counter < 6 and entry != "":
                print entry.ljust(15,' ')+'\t',
                counter +=1
            else:
                print entry
                counter = 1
        print'\n'
        continue

# Get date.
while True:
    selectedDate = raw_input("Enter DATE (YYYY-MM-DD)    : ")
    if selectedDate in date_list and \
       selectedDate <>"":
        break
    else:
        counter = 1
        print "Wrong date !\n"+"Pick a valid date in the list below.\n"
        for entry in reversed (sorted(date_list)):
            if counter < 7:
                print entry+'\t',
                counter +=1
            else:
                print entry
                counter = 1
        print'\n'
        continue
"""

selectedDate = "2007-02-14"

"""
# Print results.
counter = 0
for brand in sorted (brand_list):
    flag = False
    fi.seek(offset)
    reader = csv.reader(fi, delimiter = FieldDelimiter)
    for row in reader:
        if row[25] == selectedDate and row[19]== brand:
            flag = True
    if flag:
        print "\n"+brand
        print "-"*len(brand)
    fi.seek(offset)
    for row in reader:
        if row[19] == brand and row[25] == selectedDate:
            counter +=1
            for col in columns:
                if row[20]== "":
                    row[20] ="NULL"
                if col ==26:
                    row[26] = row[26]+" x"
                if col == 27:
                    print (row[col])
                else:
                    print (row[col]),
                    
print "\n",counter,"items found for",selectedDate
"""

# Create database.
print '\nPlease wait. Building SQLite database file ...\n'
DatabaseFile = add_unique_postfix(DatabaseFile)
con = sqlite.connect(DatabaseFile)
cur = con.cursor()
con.row_factory = sqlite.Row

cur.execute('''CREATE TABLE IF NOT EXISTS myTable
            (
            Id INT PRIMARY KEY,
            item_ CHAR,
            orig_code CHAR,
            type_ CHAR,
            artnr_ CHAR,
            brand_ CHAR,
            code_ CHAR,
            loc_ CHAR,
            alt_code CHAR,
            alt_item CHAR,
            date_ CHAR,
            quant_ INTEGER,
            price_ REAL,
            comments_ CHAR
            )''')

fi.seek(offset)
records = csv.reader(fi, delimiter = FieldDelimiter)
try:
    Id = 0
    for row in records:
        recList=[]
        if row[20] == "":
            row[20] = "NULL"        
        recList.append(Id)
        for f in sql_columns:
            recList.append(row[f])
        cur.execute('INSERT INTO myTable VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)',recList)
        Id +=1
except:
    print 'Error in database creation.\n'
    pass
con.commit()
print Id, "Records transfered.\n"


# Write here your query.
# ----------------------

brands = ['"LG"', '"PHILIPS"','"SONY"']
selection = ','.join(name for name in brands)
print (selection)
print selectedDate, selection
print

# >>> How can I replace the tuple ("LG", "PHILIPS") by a placeholder (like I did for ':date' here below) ? <<<

print "\nMy 1st query\n"

cur.execute('SELECT * FROM myTable \
            WHERE date_ = :date \
            AND brand_ IN ("LG", "PIONEER", "SANYO", "DAEWOO") \
            OR date_ = :date \
            AND brand_ LIKE "WH%" \
            ORDER BY code_',
            {"date":selectedDate})

"""
# This script won't work.

cur.execute('SELECT * FROM myTable \
            WHERE date_ = :date \
            AND brand_ IN (:sel) \
            OR date_ = :date \
            AND brand_ LIKE "WH%" \
            ORDER BY code_',
            {"date":selectedDate, "sel":selection})
"""

#print (cur.fetchall())
for field in cur:
    print str(field[0]).rjust(4,'0'), \
          str(field[11]).rjust(3,' '), \
          (field[6])[0:25].rjust(25,' '), \
          (field[8]).rjust(25,' '), \
          (field[1])[0:20].ljust(21,' '), \
          (field[2]).rjust(15,' '), \
          (field[5]).ljust(10,' ')

print
print

print "\nMy 2nd query\n"

cur.execute('SELECT * FROM myTable \
            WHERE date_ = "2007-02-14" \
            AND brand_ IN (%s) \
            ORDER BY code_' %selection)

"""
# This script won't work.

cur.execute('SELECT * FROM myTable \
            WHERE date_ = %s \
            AND brand_ IN (%s) \
            ORDER BY code_' %(selectedDate,selection))
"""

#print (cur.fetchall())
for field in cur:
    print str(field[0]).rjust(4,'0'), \
          str(field[11]).rjust(3,' '), \
          (field[6])[0:25].rjust(25,' '), \
          (field[8]).rjust(25,' '), \
          (field[1])[0:20].ljust(21,' '), \
          (field[2]).rjust(15,' '), \
          (field[5]).ljust(10,' ')

           
# >>> How can I replace both 'selectedDate' & 'selection' by placeholders ? <<<


cur.execute('DROP TABLE myTable')
cur.close()
con.close()
fi.close()
print "\nDone."

# <EOF>

Your queries look fine, although I didn't go through all of the code. You may be looking for something that is not in the SQLite file. Design a smaller test program that looks for known items in the file so you can tell if it is the query language or if nothing is found.