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>