def csv_to_sqlite(csv_path, sqlite_path=None, table_name=None):
if sqlite_path is None:
sqlite_path = os.path.splitext(csv_path)[0] + '.db'
if table_name is None:
table_name = os.path.splitext(os.path.basename(csv_path))[0]
conn = sqlite3.connect(sqlite_path)
sql_drop = 'DROP TABLE IF exists "{}"'.format(table_name)
conn.execute(sql_drop)
sa = open_csv(csv_path)
col_names = sa.dtype.names
sqlite_types = [__sqlite_type(np_descr) for _, np_descr in sa.dtype.descr]
sql_create = 'CREATE TABLE "{}" ({})'.format(
table_name,
', '.join(['{} {}'.format(col_name, sqlite_type) for
col_name, sqlite_type
in zip(col_names, sqlite_types)]))
conn.execute(sql_create)
data = __make_digestible_list_of_list(sa)
sql_insert = 'INSERT INTO "{}" VALUES ({})'.format(
table_name,
', '.join('?' * len(col_names)))
conn.executemany(sql_insert, data)
conn.commit()
conn.close()
return (sqlite_path, table_name)
def csv_to_sqlite(csv_path, sqlite_path=None, table_name=None):
if sqlite_path is None:
sqlite_path = os.path.splitext(csv_path)[0] + '.db'
if table_name is None:
table_name = os.path.splitext(os.path.basename(csv_path))[0]
conn = sqlite3.connect(sqlite_path)
sql_drop = 'DROP TABLE IF exists "{}"'.format(table_name)
conn.execute(sql_drop)
sa = open_csv(csv_path)
col_names = sa.dtype.names
sqlite_types = [__sqlite_type(np_descr) for _, np_descr in sa.dtype.descr]
sql_create = 'CREATE TABLE "{}" ({})'.format(
table_name,
', '.join(['{} {}'.format(col_name, sqlite_type) for
col_name, sqlite_type
in zip(col_names, sqlite_types)]))
conn.execute(sql_create)
data = __make_digestible_list_of_list(sa)
sql_insert = 'INSERT INTO "{}" VALUES ({})'.format(
table_name,
', '.join('?' * len(col_names)))
conn.executemany(sql_insert, data)
conn.commit()
conn.close()
return (sqlite_path, table_name)
def csv_to_sql(conn, csv_path, table_name=None,
parse_datetimes=[]):
"""Converts a csv to a table in SQL
Parameters
----------
conn : sqlalchemy engine
Connection to database
csv_path : str
Path to csv
table_name : str or None
Name of table to add to db. if None, will use the
name of the csv with the .csv suffix stripped
Returns
-------
str
THe table name
"""
# avoiding circular dependency
from diogenes.read import open_csv
if table_name is None:
table_name = os.path.splitext(os.path.basename(csv_path))[0]
sql_drop = 'DROP TABLE IF exists "{}"'.format(table_name)
conn.execute(sql_drop)
sa = open_csv(csv_path, parse_datetimes=parse_datetimes)
col_names = sa.dtype.names
sqlite_types = [__sqlite_type(np_descr) for _, np_descr in sa.dtype.descr]
sql_create = 'CREATE TABLE "{}" ({})'.format(
table_name,
', '.join(['{} {}'.format(col_name, sqlite_type) for
col_name, sqlite_type
in zip(col_names, sqlite_types)]))
conn.execute(sql_create)
data = __make_digestible_list_of_list(sa)
sql_insert = 'INSERT INTO "{}" VALUES ({})'.format(
table_name,
', '.join('?' * len(col_names)))
for row in data:
conn.execute(sql_insert, row)
return table_name
def csv_to_sql(conn, csv_path, table_name=None, parse_datetimes=[]):
"""Converts a csv to a table in SQL
Parameters
----------
conn : sqlalchemy engine
Connection to database
csv_path : str
Path to csv
table_name : str or None
Name of table to add to db. if None, will use the
name of the csv with the .csv suffix stripped
Returns
-------
str
THe table name
"""
# avoiding circular dependency
from diogenes.read import open_csv
if table_name is None:
table_name = os.path.splitext(os.path.basename(csv_path))[0]
sql_drop = 'DROP TABLE IF exists "{}"'.format(table_name)
conn.execute(sql_drop)
sa = open_csv(csv_path, parse_datetimes=parse_datetimes)
col_names = sa.dtype.names
sqlite_types = [__sqlite_type(np_descr) for _, np_descr in sa.dtype.descr]
sql_create = 'CREATE TABLE "{}" ({})'.format(
table_name, ', '.join([
'{} {}'.format(col_name, sqlite_type)
for col_name, sqlite_type in zip(col_names, sqlite_types)
]))
conn.execute(sql_create)
data = __make_digestible_list_of_list(sa)
sql_insert = 'INSERT INTO "{}" VALUES ({})'.format(
table_name, ', '.join('?' * len(col_names)))
for row in data:
conn.execute(sql_insert, row)
return table_name
def test_open_csv(self):
csv_file = utils_for_tests.path_of_data("mixed.csv")
correct = np.array([(0, 'Jim', 5.6), (1, 'Jill', 5.5)],
dtype=[('id', '<i8'), ('name', 'O'),
('height', '<f8')])
self.assertTrue(np.array_equal(read.open_csv(csv_file), correct))
def test_open_csv(self):
csv_file = utils_for_tests.path_of_data("mixed.csv")
correct = np.array([(0, 'Jim', 5.6), (1, 'Jill', 5.5)],dtype=[('id', '<i8'), ('name', 'O'), ('height', '<f8')])
self.assertTrue(np.array_equal(read.open_csv(csv_file),correct))