-
Notifications
You must be signed in to change notification settings - Fork 0
/
csv2sql.py
executable file
·93 lines (76 loc) · 2.63 KB
/
csv2sql.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
#!/usr/bin/env python
"""
Take an input file in csv format and
create an SQL database table.
To run from the command line:
csv2sql.py "csv_file_name" [options]
"""
# import needed modules
import sqlite3
from matplotlib.mlab import csv2rec
# get csv file from command line
from sys import argv
csv_file = argv[1]
# Get the arguments from the command
# line for the csv2sql command.
from optparse import OptionParser
# set the options
parser = OptionParser()
parsser.add_option("-h", "--help", action="help")
parser.add_option("--database", dest="database", default=csv_file[:-4],
help="Specify output database.", metavar="DATABASE")
parser.add_option("-t", "--table", dest="table", default=csv_file[:-4]
help="Specify output table.", metavar="TABLE")
parser.add_option("-c", "--comments", dest="comments", default="#",
help="Specify symbol which signifies comment lines.",
metavar="COMMENT")
parser.add_option("-d", "--delimiter", dest="delimiter", default=",",
help="Specify delimiter of csv file data.",
metavar="DELIMITER")
# put optional args in a dict
opts = {'database':database,
'table':table,
'comment':comment,
'delimiter':delimiter}
def get_data_type(data):
"""
Get the data type of
the input and return
the SQL data type name.
"""
if "string" in str(type(data)): return "TEXT"
if "float" in str(type(data)): return "REAL"
if "int" in str(type(data)): return "INTEGER"
def csv2sql(database, table, comments="#", delimiter=","):
"""
The main method that will
take in the csv file and
create a database and
a table.
"""
global csv_file
# create the database and cursor
con = sqlite3.connect(database)
cur = con.cursor()
# load in the data
data = csv2rec(csv_file, comments=comments, delimiter=delimiter)
# make the table
create_table_command = "CREATE TABLE {0} (".format(table)
for n, t in zip(data.dtype.names, data[0]):
create_table_command += "{0} {1}, ".format(n, get_data_type(t))
create_table_command += ")"
cur.execute(create_table_command)
# insert the rows of data into the table.
for row in data:
cur.execute(
"INSERT INTO {0} VALUES {1}".format(table, str(row)))
# Save (commit) the database
con.commit()
# close the connection to the database
con.close()
# To run it from command line
if __name__=="__main__":
csv2sql(opts['database'],
opts['table'],
comments=opts['comments'],
delimiter=opts['delimiter'])