csvimport.py 4.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130
  1. #!/usr/bin/env python3
  2. # -*- coding: utf-8 -*-
  3. """
  4. Created on Thu May 28 11:23:44 2020
  5. Used to import csv data into tables, written in python for ease of use.
  6. Data is obtained from:
  7. https://ourairports.com/data/
  8. https://openflights.org/data.html
  9. @author: Felix Turowsky
  10. /*
  11. *openPilot Log - A FOSS Pilot Logbook Application
  12. *Copyright (C) 2020 Felix Turowsky
  13. *
  14. *This program is free software: you can redistribute it and/or modify
  15. *it under the terms of the GNU General Public License as published by
  16. *the Free Software Foundation, either version 3 of the License, or
  17. *(at your option) any later version.
  18. *
  19. *This program is distributed in the hope that it will be useful,
  20. *but WITHOUT ANY WARRANTY; without even the implied warranty of
  21. *MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  22. *GNU General Public License for more details.
  23. *
  24. *You should have received a copy of the GNU General Public License
  25. *along with this program. If not, see <https://www.gnu.org/licenses/>.
  26. */
  27. """
  28. import csv
  29. import sqlite3
  30. import os
  31. conn = sqlite3.connect(os.path.join('./csv', 'logbook.db'))
  32. c = conn.cursor()
  33. with open(os.path.join('./csv', 'airports_edited.csv')) as fin:
  34. dr = csv.DictReader(fin) # comma is default delimiter
  35. to_dbap = [(i['icao'],
  36. i['iata'],
  37. i['name'],
  38. i['lat'],
  39. i['long'],
  40. i['country'],
  41. i['alt'],
  42. i['utcoffset'],
  43. i['tzolson'],
  44. ) for i in dr]
  45. def csvtodbap(to_db):
  46. c.executemany("INSERT INTO airports (icao, iata, name, lat, long, country, alt, utcoffset, tzolson) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);", to_db)
  47. conn.commit()
  48. print('Database entries have been added. New table:')
  49. for row in c.execute("select * from airports"):
  50. print(row)
  51. conn.close()
  52. with open(os.path.join('./csv', 'flights_test.csv')) as fin: # imports flights
  53. dr = csv.DictReader(fin) # comma is default delimiter
  54. to_dbfl = [(i['doft'],
  55. i['dept'],
  56. i['tofb'],
  57. i['dest'],
  58. i['tonb'],
  59. i['tblk'],
  60. i['pic'],
  61. i['acft']
  62. ) for i in dr]
  63. def csvtodbfl(to_db):
  64. c.executemany("INSERT INTO flights (doft, dept, tofb, dest, tonb, tblk, pic, acft) VALUES (?, ?, ?, ?, ?, ?, ?, ?);", to_db)
  65. conn.commit()
  66. print('Databse entries have been added. New table:')
  67. for row in c.execute("select * from flights"):
  68. print(row)
  69. conn.close()
  70. with open(os.path.join('./csv', 'pilots_test.csv')) as fin: # imports flights
  71. dr = csv.DictReader(fin) # comma is default delimiter
  72. to_dbpl = [(i['picfirstname'],
  73. i['piclastname'],
  74. i['alias']
  75. ) for i in dr]
  76. def csvtodbpl(to_db):
  77. c.executemany("INSERT INTO pilots (picfirstname, piclastname, alias) VALUES (?, ?, ?);", to_db)
  78. conn.commit()
  79. print('Database entries have been added. New table:')
  80. for row in c.execute("select * from pilots"):
  81. print(row)
  82. conn.close()
  83. with open(os.path.join('./csv', 'aircraft.csv')) as fin: # imports flights
  84. dr = csv.DictReader(fin) # comma is default delimiter
  85. to_dbac = [(i['Name'],
  86. i['iata'],
  87. i['icao'],
  88. ) for i in dr]
  89. def csvtodbac(to_db):
  90. c.executemany("INSERT INTO aircraft (aircraft_id,make,model,variant,name,iata,icao,singlepilot,multipilot,singleengine,multiengine,turboprop,jet,heavy) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", to_db)
  91. conn.commit()
  92. print('Databse entries have been added. New table:')
  93. for row in c.execute("select * from aircraft"):
  94. print(row)
  95. conn.close()
  96. with open(os.path.join('./csv', 'tails_test.csv')) as fin: # imports flights
  97. dr = csv.DictReader(fin) # comma is default delimiter
  98. to_dbtails = [(i['Registration'],
  99. i['aircraft_id'],
  100. i['Company']
  101. ) for i in dr]
  102. def csvtodbtails(to_db):
  103. c.executemany("INSERT INTO tails (Registration, aircraft_id, Company) VALUES (?, ?, ?);", to_db)
  104. conn.commit()
  105. print('Databse entries have been added. New table:')
  106. for row in c.execute("select * from tails"):
  107. print(row)
  108. conn.close()