import sqlite3, csv
from pprint import pprint


sqlite_file = 'mydb_NEW.db' # name of db file

# connect to database:
conn = sqlite3.connect(sqlite_file)
conn.text_factory = str

# Get a cursor object
cur = conn.cursor() # Return a cursor for the connection.

'''nodes_tags section'''

cur.execute('''DROP TABLE IF EXISTS nodes_tags''') # exucutes an sql statement
conn.commit()


# Create the table, specifying the column names and data types:
cur.execute('''
    CREATE TABLE nodes_tags(id INTEGER, key TEXT, value TEXT,type TEXT)
''')
# commit the changes
conn.commit()


# open nodes_tags.csv
with open('nodes_tags.csv', 'rb') as fin:
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['id'], i['key'], i['value'], i['type']) for i in dr]





# inseart the formatted data
cur.executemany(
    'INSERT INTO nodes_tags(id, key, value,type) VALUES (?, ?, ?, ?);', to_db)

conn.commit()







''' nodes section'''

cur.execute('''DROP TABLE IF EXISTS nodes''') # exucutes an sql statement
conn.commit()


cur.execute('''
    CREATE TABLE nodes(id INTEGER, lat DECIMAL, lon DECIMAL, user TEXT, uid INTEGER, version INTEGER , changeset INTEGER, timestamp TIMESTAMP)
''')
# commit the changes
conn.commit()
# Read the csv file as a dictionary,
# then format as a list of tuples

# open nodes.csv
# id    lat lon user    uid version changeset   timestamp
with open('nodes.csv', 'rb') as fin:
    dr = csv.DictReader(fin)
    to_db = [(i['id'], i['lat'], i['lon'], i['user'], i['uid'], i['version'], i['changeset'], i['timestamp']) for i in dr]

cur.executemany('INSERT INTO nodes(id, lat, lon, user, uid, version, changeset, timestamp) VALUES (?,?,?,?,?,?,?,?);', to_db)





''' ways section'''

cur.execute('''DROP TABLE IF EXISTS ways''') # exucutes an sql statement
conn.commit()

#id user    uid version changeset   timestamp


cur.execute('''
    CREATE TABLE ways(id INTEGER, user TEXT, uid INTEGER, version INTEGER , changeset INTEGER, timestamp TIMESTAMP)
''')
# commit the changes
conn.commit()
# Read the csv file as a dictionary,
# then format as a list of tuples

# open ways.csv
with open('ways.csv', 'rb') as fin:
    dr = csv.DictReader(fin)
    to_db = [(i['id'], i['user'], i['uid'], i['version'], i['changeset'], i['timestamp']) for i in dr]

cur.executemany('INSERT INTO ways(id, user, uid, version, changeset, timestamp) VALUES (?,?,?,?,?,?);', to_db)




''' ways nodes section'''

cur.execute('''DROP TABLE IF EXISTS ways_nodes''') # exucutes an sql statement
conn.commit()

#id node_id position


cur.execute('''
    CREATE TABLE ways_nodes(id INTEGER, node_id INTEGER, position INTEGER)
''')
# commit the changes
conn.commit()
# Read the csv file as a dictionary,
# then format as a list of tuples

# open ways_nodes.csv
with open('ways_nodes.csv', 'rb') as fin:
    dr = csv.DictReader(fin)
    to_db = [(i['id'], i['node_id'], i['position']) for i in dr]

cur.executemany('INSERT INTO ways_nodes(id, node_id, position) VALUES (?,?,?);', to_db)



''' ways tags section'''

cur.execute('''DROP TABLE IF EXISTS ways_tags''') # exucutes an sql statement
conn.commit()


#id key value   type

cur.execute('''
    CREATE TABLE ways_tags(id INTEGER, key TEXT, value TEXT, type TEXT)
''')
# commit the changes
conn.commit()
# Read the csv file as a dictionary,
# then format as a list of tuples

# open ways_nodes.csv
with open('ways_tags.csv', 'rb') as fin:
    dr = csv.DictReader(fin)
    to_db = [(i['id'], i['key'], i['value'], i['type']) for i in dr]

cur.executemany('INSERT INTO ways_tags(id, key, value, type) VALUES (?,?,?,?);', to_db)