Python and Sqlite3, transforming data text file to sql database

Question

So I've been given an assignment/Challenge to complete but I just don't know whee to start with it I've got experience with Python but not with using databases and data transformation onto the description.

So here is a snippet of my text file I've been given:

   Grid-ref=   1, 148
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630
 3020 2820 3040 2880 1740 1360  980  990 1410 1770 2580 2630

Grid-ref=   1, 311
  490  290  280  230  200  250  440  530  460  420  530  450
  490  290  280  230  200  250  440  530  460  420  530  450
  490  290  280  230  200  250  440  530  460  420  530  450
  490  290  280  230  200  250  440  530  460  420  530  450
  490  290  280  230  200  250  440  530  460  420  530  450
  490  290  280  230  200  250  440  530  460  420  530  450
  490  290  280  230  200  250  440  530  460  420  530  450
  490  290  280  230  200  250  440  530  460  420  530  450
  490  290  280  230  200  250  440  530  460  420  530  450
  490  290  280  230  200  250  440  530  460  420  530  450
Grid-ref=   1, 312
  460  280  260  220  190  240  430  520  450  400  520  410
  460  280  260  220  190  240  430  520  450  400  520  410
  460  280  260  220  190  240  430  520  450  400  520  410
  460  280  260  220  190  240  430  520  450  400  520  410
  460  280  260  220  190  240  430  520  450  400  520  410
  460  280  260  220  190  240  430  520  450  400  520  410
  460  280  260  220  190  240  430  520  450  400  520  410
  460  280  260  220  190  240  430  520  450  400  520  410
  460  280  260  220  190  240  430  520  450  400  520  410
  460  280  260  220  190  240  430  520  450  400  520  410

So from this i must then create a database containing 4 columns like so:

Xref    Yref    Date        Value
1       148     1,1,2000    3020
1       148     1,2,2000    2820

I hope you can see the pattern so grid-ref= 1, 148 are my X & Y co-ords then each value is obviously the value but i need to iterate through and for each value it then gives it the new date which is just the 1st of each month for 10 years.

So far I have this code which i know isn't much it's a start.

    import os
    import csv
    import sqlite3

    f_path = os.path.dirname(os.path.abspath(__file__)) + "/data/"

    db = sqlite3.connect('output.db')
    cursor = db.cursor()
    cursor.execute('CREATE TABLE Data (Xref, Yref, Date, Value)')

    date = 2000 - 2010
    grid = 'Xref, Yref'

with open(f_path + "data.to.use.txt") as file_read:
    for row in csv.DictReader(file_read):
        cursor.execute('''INSERT INTO Data
                              VALUES (:Xref, :Yref, :Date, :Value)''', row)


db.commit()
db.close()

Thank you for all feedback and guidance, I'm in unfamiliar territory with this type of task and hope you can help.

Shijo · Accepted Answer · 2017-01-11 15:59:03Z

you could try the below code. I am not quite not clear with date requirement . So I just added a month for each entry

from datetime import date,datetime
from dateutil.relativedelta import relativedelta
Xref=''
Yref=''
date =datetime.strptime('2000-01-01', '%Y-%m-%d')
with open('C:\Users\shmathew\Desktop\Sample\sample.txt') as file_read:
    for row in file_read:
        print row
        if 'Grid-ref' in row:
            Xref = row.split(',')[0].split('=   ')[1]
            Yref = row.split(',')[1]
        else:
            for Value in row.split('  '):
                date = date + relativedelta(months=+1)
                print Xref.strip(),Yref.strip(),date,Value.strip()

Sample output

490  290  280  230  200  250  440  530  460  420  530  450

1 311 2009-08-01 00:00:00 490
1 311 2009-09-01 00:00:00 290
1 311 2009-10-01 00:00:00 280
1 311 2009-11-01 00:00:00 230
1 311 2009-12-01 00:00:00 200
1 311 2010-01-01 00:00:00 250
1 311 2010-02-01 00:00:00 440
1 311 2010-03-01 00:00:00 530
1 311 2010-04-01 00:00:00 460
1 311 2010-05-01 00:00:00 420
1 311 2010-06-01 00:00:00 530
1 311 2010-07-01 00:00:00 450
490  290  280  230  200  250  440  530  460  420  530  450

1 311 2010-08-01 00:00:00 490
1 311 2010-09-01 00:00:00 290
1 311 2010-10-01 00:00:00 280
1 311 2010-11-01 00:00:00 230
1 311 2010-12-01 00:00:00 200
1 311 2011-01-01 00:00:00 250
1 311 2011-02-01 00:00:00 440
1 311 2011-03-01 00:00:00 530
1 311 2011-04-01 00:00:00 460
1 311 2011-05-01 00:00:00 420
1 311 2011-06-01 00:00:00 530
1 311 2011-07-01 00:00:00 450
490  290  280  230  200  250  440  530  460  420  530  450
1 311 2011-08-01 00:00:00 490
1 311 2011-09-01 00:00:00 290
1 311 2011-10-01 00:00:00 280
1 311 2011-11-01 00:00:00 230
1 311 2011-12-01 00:00:00 200
1 311 2012-01-01 00:00:00 250
1 311 2012-02-01 00:00:00 440
1 311 2012-03-01 00:00:00 530
1 311 2012-04-01 00:00:00 460
1 311 2012-05-01 00:00:00 420
1 311 2012-06-01 00:00:00 530
1 311 2012-07-01 00:00:00 450

Collectives™ on Stack Overflow

Python and Sqlite3, transforming data text file to sql database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related