def setupFixtures(self):
self.really_basic_table = Table('really_basic_table')
self.really_basic_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT')
]
)
self.basic_table = Table('basic_table')
self.basic_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT'),
Column('date_field', 'DATE', 'csv_date_field'),
Column('integer_field', 'INTEGER', 'csv_integer_field'),
Column('text_field', 'TEXT', 'csv_text_field')
]
)
self.foreign_key_table = Table('foreign_key_table')
self.fk_col_a = Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT')
self.fk_col_b = Column('basic_id', 'INTEGER')
self.fk_col_c = Column('really_basic_id', 'INTEGER')
self.foreign_key_table.add_columns(
[self.fk_col_a, self.fk_col_b, self.fk_col_c])
self.foreign_key_a = ForeignKey('basic_id', self.basic_table, 'id')
self.foreign_key_b = ForeignKey('really_basic_id',
self.really_basic_table, 'id')
self.foreign_key_table.add_foreign_keys(
[self.foreign_key_a, self.foreign_key_b])
def setUp(self):
self.really_basic_table = Table('really_basic_table')
self.really_basic_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT')
]
)
self.basic_table = Table('basic_table')
self.basic_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT'),
Column('date_field', 'DATE', 'csv_date_field'),
Column('integer_field', 'INTEGER', 'csv_integer_field'),
Column('text_field', 'TEXT', 'csv_text_field')
]
)
self.foreign_key_table = Table('foreign_key_table')
self.foreign_key_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT'),
Column('basic_id', 'INTEGER'),
Column('really_basic_id', 'INTEGER')
]
)
self.foreign_key_table.add_foreign_keys(
[
ForeignKey('basic_id', self.basic_table, 'id'),
ForeignKey('really_basic_id',
self.really_basic_table,
'id')
]
)
def guess_schema(table_name, path):
"""Create a best-effort table schema from a CSV"""
datatypes = tally_datatypes(enumerate(read_csv(path)))
table = Table(table_name)
for col_name, datatypes_tally in datatypes.items():
most_freq_datatype = max(datatypes_tally, key=datatypes_tally.count)
table.add_column(Column(col_name, most_freq_datatype))
# for col, val in table.columns.items(): print(col)
return [table]
class TestDBMethods(unittest.TestCase):
def setUp(self):
self.setupFixtures()
self.setupDB()
def setupFixtures(self):
self.really_basic_table = Table('really_basic_table')
self.really_basic_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT')
]
)
self.basic_table = Table('basic_table')
self.basic_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT'),
Column('date_field', 'DATE', 'csv_date_field'),
Column('integer_field', 'INTEGER', 'csv_integer_field'),
Column('text_field', 'TEXT', 'csv_text_field')
]
)
self.foreign_key_table = Table('foreign_key_table')
self.fk_col_a = Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT')
self.fk_col_b = Column('basic_id', 'INTEGER')
self.fk_col_c = Column('really_basic_id', 'INTEGER')
self.foreign_key_table.add_columns(
[self.fk_col_a, self.fk_col_b, self.fk_col_c])
self.foreign_key_a = ForeignKey('basic_id', self.basic_table, 'id')
self.foreign_key_b = ForeignKey('really_basic_id',
self.really_basic_table, 'id')
self.foreign_key_table.add_foreign_keys(
[self.foreign_key_a, self.foreign_key_b])
def setupDB(self):
conn = sqlite3.connect(TEST_DB, 5.0, 0, None)
conn.text_factory = str
conn.execute('CREATE TABLE basic_table ' \
'(id INTEGER PRIMARY KEY AUTOINCREMENT, ' \
'date_field DATE, ' \
'integer_field INTEGER, ' \
'text_field TEXT);')
conn.execute("INSERT INTO basic_table (text_field) " \
"VALUES ('foo');")
conn.execute("INSERT INTO basic_table (text_field) " \
"VALUES ('bar');")
conn.execute("INSERT INTO basic_table (text_field) " \
"VALUES ('bar');")
conn.execute('CREATE TABLE foreign_key_table ' \
'(id INTEGER PRIMARY KEY AUTOINCREMENT, ' \
'basic_id INTEGER, ' \
'really_basic_id INTEGER);')
self.c = conn.cursor()
def tearDown(self):
os.remove(TEST_DB)
def test_lookup_foreign_key(self):
expected_fk = 1
actual_fk = dbwrappers.lookup_foreign_key(self.c,
self.foreign_key_table,
self.fk_col_b,
'csv_text_field',
'foo')
self.assertEqual(expected_fk, actual_fk)
def test_lookup_foreign_key_missing(self):
"""No row with text_field == 'baz'"""
with self.assertRaises(dbwrappers.ForeignKeyException):
dbwrappers.lookup_foreign_key(self.c, self.foreign_key_table,
self.fk_col_b,
'csv_text_field','baz')
def test_lookup_foreign_key_duplicate(self):
"""Table has 2 rows with text_field == 'bar'"""
with self.assertRaises(dbwrappers.ForeignKeyException):
dbwrappers.lookup_foreign_key(self.c, self.foreign_key_table,
self.fk_col_b,
'csv_text_field', 'bar')
def test_lookup_foreign_key_wrong_field(self):
"""'foo' is in text_field, not in date_field"""
with self.assertRaises(dbwrappers.ForeignKeyException):
dbwrappers.lookup_foreign_key(self.c, self.foreign_key_table,
self.fk_col_b,
'csv_date_field', 'foo')
class TestDBMethods(unittest.TestCase):
def setUp(self):
self.really_basic_table = Table('really_basic_table')
self.really_basic_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT')
]
)
self.basic_table = Table('basic_table')
self.basic_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT'),
Column('date_field', 'DATE', 'csv_date_field'),
Column('integer_field', 'INTEGER', 'csv_integer_field'),
Column('text_field', 'TEXT', 'csv_text_field')
]
)
self.foreign_key_table = Table('foreign_key_table')
self.foreign_key_table.add_columns(
[
Column('id', 'INTEGER PRIMARY KEY AUTOINCREMENT'),
Column('basic_id', 'INTEGER'),
Column('really_basic_id', 'INTEGER')
]
)
self.foreign_key_table.add_foreign_keys(
[
ForeignKey('basic_id', self.basic_table, 'id'),
ForeignKey('really_basic_id',
self.really_basic_table,
'id')
]
)
def test_basic_create_table_sql(self):
expected_sql = "CREATE TABLE basic_table (" \
"date_field DATE, " \
"id INTEGER PRIMARY KEY AUTOINCREMENT, " \
"integer_field INTEGER, " \
"text_field TEXT);"
actual_sql = sqlgen.basic_create_table_sql(self.basic_table)
self.assertEqual(expected_sql, actual_sql)
def test_foreign_key_constraint_sql(self):
expected_sql = "FOREIGN KEY(basic_id) " \
"REFERENCES basic_table(id), " \
"FOREIGN KEY(really_basic_id) " \
"REFERENCES really_basic_table(id), "
actual_sql = sqlgen.foreign_key_constraint_sql(
self.foreign_key_table)
self.assertEqual(expected_sql, actual_sql)
def test_create_table_sql(self):
expected_sql = "CREATE TABLE foreign_key_table (" \
"basic_id INTEGER, " \
"id INTEGER PRIMARY KEY AUTOINCREMENT, " \
"really_basic_id INTEGER, " \
"FOREIGN KEY(basic_id) " \
"REFERENCES basic_table(id), " \
"FOREIGN KEY(really_basic_id) " \
"REFERENCES really_basic_table(id));"
actual_sql = sqlgen.create_table_sql(self.foreign_key_table)
self.assertEqual(expected_sql, actual_sql)
def test_insert_sql(self):
expected_sql = "INSERT INTO basic_table (id, date_field) " \
"VALUES ('12345', '2010-01-01');"
row_data = {'id' : '12345', 'date_field' : '2010-01-01'}
# Force dict field order so actual SQL is consistent.
row_data = collections.OrderedDict(
sorted(row_data.items(), reverse=True))
actual_sql = sqlgen.insert_sql(self.basic_table, row_data)
self.assertEqual(expected_sql, actual_sql)
def test_query_sql(self):
expected_sql = "SELECT * FROM basic_table WHERE 1 " \
"AND date_field = '2010-01-01';"
actual_sql = sqlgen.query_sql(self.basic_table,
{'date_field': '2010-01-01'})
self.assertEqual(expected_sql, actual_sql)
def test_query_sql_specified_cols(self):
expected_sql = "SELECT id, integer_field FROM basic_table WHERE 1 " \
"AND date_field = '2010-01-01';"
actual_sql = sqlgen.query_sql(self.basic_table,
{'date_field': '2010-01-01'},
['id', 'integer_field'])
self.assertEqual(expected_sql, actual_sql)
def test_query_sql_multiple_parameters(self):
expected_sql = "SELECT * FROM basic_table WHERE 1 " \
"AND date_field = '2010-01-01' " \
"AND text_field = 'foo';"
actual_sql = sqlgen.query_sql(self.basic_table,
{'date_field': '2010-01-01',
'text_field': 'foo'})
self.assertEqual(expected_sql, actual_sql)
def test_pre_process_value(self):
def test_callback(test_col_str):
return 'callback succeeded'
col = Column('test_col', 'TEXT', None, test_callback)
processed_val = sqlgen.pre_process_value(col, 'test text')
self.assertEqual(processed_val, 'callback succeeded')
def test_quote_apostrophes(self):
text = "Hello there, I've been waiting for you."
expected_val = "Hello there, I''ve been waiting for you."
actual_val = csv_util.quote_apostrophes(text)
self.assertEqual(expected_val, actual_val)