Input/output examples: csv files and database

Read from csv file, write to database

First, we read the data from the following csv file.

Date	Open	High	Low	Close	Volume	Adj Close
2/8/2016	185.770004	186.119995	182.800003	185.419998	185762900	185.419998
2/5/2016	190.990005	191.669998	187.199997	187.949997	172668500	187.949997
2/4/2016	190.710007	192.75	189.960007	191.600006	136318100	191.600006
2/3/2016	191.410004	191.779999	187.100006	191.300003	203820400	191.300003
2/2/2016	191.960007	191.970001	189.539993	190.160004	172941200	190.160004
2/1/2016	192.529999	194.580002	191.839996	193.649994	130514800	193.649994
1/29/2016	190.020004	193.880005	189.880005	193.720001	195455400	193.720001
1/28/2016	189.960007	190.199997	187.160004	189.110001	139970600	189.110001
1/27/2016	189.580002	191.559998	187.059998	188.130005	181677100	188.130005
1/26/2016	188.419998	190.529999	188.020004	190.199997	137269900	190.199997
1/25/2016	189.919998	190.149994	187.410004	187.639999	122676200	187.639999
1/22/2016	189.779999	190.759995	188.880005	190.520004	163849600	190.520004
1/21/2016	186.210007	188.869995	184.639999	186.690002	189174000	186.690002
1/20/2016	185.029999	187.5	181.020004	185.649994	280016900	185.649994
1/19/2016	189.960007	190.110001	186.199997	188.059998	190196000	188.059998
1/15/2016	186.770004	188.759995	185.520004	187.809998	324846400	187.809998
1/14/2016	189.550003	193.259995	187.660004	191.929993	240795600	191.929993
1/13/2016	194.449997	194.860001	188.380005	188.830002	221168900	188.830002
1/12/2016	193.820007	194.550003	191.139999	193.660004	172330500	193.660004
1/11/2016	193.009995	193.410004	189.820007	192.110001	187941300	192.110001
1/8/2016	195.190002	195.850006	191.580002	191.919998	209817200	191.919998
1/7/2016	195.330002	197.440002	193.589996	194.050003	213436100	194.050003
1/6/2016	198.339996	200.059998	197.600006	198.820007	152112600	198.820007
1/5/2016	201.399994	201.899994	200.050003	201.360001	110845800	201.360001
1/4/2016	200.490005	201.029999	198.589996	201.020004	222353500	201.020004

Then we insert the table in a database, following https://docs.python.org/3.3/library/sqlite3.html

import csv

### Read csv file
fid = open('historicalPrices.csv')
import csv
fid = open('historicalPrices.csv')
reader = csv.reader(fid)
headers = next(reader) # not used
rows = []
for row in reader:
    print(row)
    rows.append((row[0], float(row[1]), float(row[2]), float(row[3]), float(row[4]), float(row[5]), float(row[6])))
fid.close()

### Database
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Delete table from database, to start from scratch
try:
    c.execute('DROP TABLE history')
except:
    pass

c.execute('''CREATE TABLE history (Date text, Open real, High real, Low real, Close real,  Volume real, Adj Close real)''')
c.executemany('INSERT INTO history VALUES (?,?,?,?,?,?,?)', rows)

conn.commit()
conn.close()

(Source code)

Read from database, save to a new csv file

Finally, we read the table from the database, in a new Python session and print it to a csv file, after we swap two columns and format the data.

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

fid = open('historicalPrices2.csv', 'w')
c.execute('SELECT * FROM history')
headers = [description[0] for description in c.description]
for h in headers[0:5]:
    fid.write(h + ',  ')
fid.write(headers[6] + ',  ')
fid.write(headers[5])
fid.write('\n')

for row in c.execute('SELECT * FROM history'):
    fid.write(row[0] + ',  ')
    for r in row[1:5]:
        fid.write(str(round(r,2)) + ',  ')
    fid.write(str(round(row[6],2)) + ',  ')
    fid.write(str(int(row[5])))
    fid.write('\n')
fid.close()

conn.close()

(Source code)

Date	Open	High	Low	Close	Adj	Volume
2/8/2016	185.77	186.12	182.8	185.42	185.42	185762900
2/5/2016	190.99	191.67	187.2	187.95	187.95	172668500
2/4/2016	190.71	192.75	189.96	191.6	191.6	136318100
2/3/2016	191.41	191.78	187.1	191.3	191.3	203820400
2/2/2016	191.96	191.97	189.54	190.16	190.16	172941200
2/1/2016	192.53	194.58	191.84	193.65	193.65	130514800
1/29/2016	190.02	193.88	189.88	193.72	193.72	195455400
1/28/2016	189.96	190.2	187.16	189.11	189.11	139970600
1/27/2016	189.58	191.56	187.06	188.13	188.13	181677100
1/26/2016	188.42	190.53	188.02	190.2	190.2	137269900
1/25/2016	189.92	190.15	187.41	187.64	187.64	122676200
1/22/2016	189.78	190.76	188.88	190.52	190.52	163849600
1/21/2016	186.21	188.87	184.64	186.69	186.69	189174000
1/20/2016	185.03	187.5	181.02	185.65	185.65	280016900
1/19/2016	189.96	190.11	186.2	188.06	188.06	190196000
1/15/2016	186.77	188.76	185.52	187.81	187.81	324846400
1/14/2016	189.55	193.26	187.66	191.93	191.93	240795600
1/13/2016	194.45	194.86	188.38	188.83	188.83	221168900
1/12/2016	193.82	194.55	191.14	193.66	193.66	172330500
1/11/2016	193.01	193.41	189.82	192.11	192.11	187941300
1/8/2016	195.19	195.85	191.58	191.92	191.92	209817200
1/7/2016	195.33	197.44	193.59	194.05	194.05	213436100
1/6/2016	198.34	200.06	197.6	198.82	198.82	152112600
1/5/2016	201.4	201.9	200.05	201.36	201.36	110845800
1/4/2016	200.49	201.03	198.59	201.02	201.02	222353500