reate the table and use python to automate loading of the following file into SQ
ID: 3829009 • Letter: R
Question
reate the table and use python to automate loading of the following file into SQLite: http://rasinsrv07.cstcis.cti.depaul.edu/CSC455/Public_Chauffeurs_Short_hw3.csv It contains comma-separated data, with two changes: NULL may now be represented by NULL string or an empty string (e.g., either ,NULL, or ,,) and some of the names have the following form “Last, First” instead of “First Last”, which is problematic because when you split the string on a comma, you end up with too many values to insert.
Explanation / Answer
import csv,sqlite3
dbName = 'clt.db'
conn = sqlite3.connect(dbName)
curs = conn.cursor()
def loadBloc(bloc):
global conn,curs
curs.execute('begin')
for obs in bloc:
urs.execute(insertStmt,obs)
conn.commit()
return None
createCode = '''
create table ff
(
a text not null,
b text not null,
c text not null,
d numeric not null,
e text not null,
f text not null,
g text not null,
h numeric not null,
i numeric not null
);
'''
curs.execute(createCode)
conn.commit()
nb = 10000; cnt = 0
bloc = ["" for i in range(nb)]
f = open(infile,'rt')
csv.register_dialect('pipes',delimiter=' ')
with open('ff.dat','r') as f:
reader = csv.reader(f,dialect='pipes')
for row in reader:
a = row[0]
b = row[1]
c = row[2]
u = c.split('/')
if len(u) == 3:
v = [int(u[i]) for i in range(len(u)) ]
c = "{0:4d}-{1:02d}-{2:02d}".format(v[2],v[1],v[0])
else:
c = "1212-12-12"
d = row[3]
e = row[4]
f = row[5]
g = row[6]
h = row[7]
i = row[8]
obs = (a,b,c,d,e,f,g,h,i)
insertStmt = 'insert into ff (a,b,c,d,e,f,g,h,i) values (' + ','.join('?'*9) + ');'
curs.execute(insertStmt,obs)
conn.commit()
print cnt
#bloc[cnt] = (a,b,c,d,e,f,g,h,i)
#if cnt == nb-1:
# loadBloc(bloc)
# print "=============================================="
# bloc = ["" for i in range(nb)]
# cnt = 0
#else:
# cnt = cnt + 1
f.close()
curs.close()
conn.close()
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.