2 import sqlite3
as sqlite
7 log = logging.getLogger(
"Database")
11 """ Class to manage a SQL database built with sqlite3 """
15 self.connection =
None
20 def create(self,filename,overwrite=False):
21 """ Creates a database by simply connecting to the file """
22 log.info(
"Creating database")
23 if overwrite
and os.path.exists(filename):
25 sqlite.connect(filename)
28 """ Connects to the database in filename """
29 if not os.path.isfile(filename):
30 raise IOError,
"Database file not found: %s" % filename
31 self.connection = sqlite.connect(filename)
32 self.cursor = self.connection.cursor()
35 """ Checks if the class is connected to the database filename """
36 if self.connection ==
None:
37 raise ValueError,
"The database has not been created " \
38 "or connection not established "
40 def create_table(self, table_name, column_names, column_types):
41 """ Creates a table. It expects a sorted dictionary
42 of (data_field,typename) entries """
43 log.info(
"Creating table %s",table_name)
45 sql_command =
"CREATE TABLE %s (" % (table_name)
46 for name, data_type
in zip(column_names, column_types):
47 sql_typename = get_sql_type_name(data_type)
48 sql_command +=
"%s %s," % (name, sql_typename)
51 sql_command = sql_command[0:n-1] +
")"
52 log.debug(sql_command)
53 self.cursor.execute(sql_command)
54 self.connection.commit()
58 Delete a table if it exists
60 log.info(
"Deleting table %s",table_name)
62 sql_command =
"DROP TABLE IF EXISTS %s" % (table_name)
63 log.debug(sql_command)
64 self.cursor.execute(sql_command)
65 self.connection.commit()
68 """ Inserts information in a given table of the database.
69 The info must be a list of tuples containing as many values
70 as columns in the table
71 Conversion of values is done AUTOMATICALLY after checking the
72 types stored in the table
76 tuple_format=
"("+
"?,"*(n-1)+
"?)"
77 sql_command=
"INSERT INTO %s VALUES %s " % (table_name, tuple_format)
84 y = [apply_type(i)
for i,apply_type
in zip(x, types)]
85 self.cursor.execute(sql_command, y)
86 self.connection.commit()
89 """ Inserts information in a given table of the database.
90 The info must be a list of tuples containing as many values
91 as columns in the table
92 Conversion of values is done AUTOMATICALLY after checking the
93 types stored in the table
96 log.warning(
"Inserting empty data")
100 tuple_format=
"("+
"?,"*(n-1)+
"?)"
101 sql_command=
"INSERT INTO %s VALUES %s " % (table_name, tuple_format)
105 for i
in xrange(len(data)):
106 data[i] = [apply_type(d)
for d,apply_type
in zip(data[i], types)]
107 self.cursor.executemany(sql_command, data)
108 self.connection.commit()
111 """ Retrieves data from the database using the sql_command
112 returns the records as a list of tuples"""
114 log.debug(
"Retrieving data: %s" % sql_command)
115 self.cursor.execute(sql_command)
116 return self.cursor.fetchall()
123 """ updates the register in the table identified by the condition
124 values for the condition fields
127 sql_command =
"UPDATE %s SET " % (table_name)
128 for field, value
in zip(updated_fields, updated_values):
129 sql_command +=
"%s=%s," % (field, value)
130 sql_command = sql_command.rstrip(
",") +
" WHERE "
132 sql_command = sql_command + s
134 log.debug(
"Updating %s: %s",table_name, sql_command)
135 self.cursor.execute(sql_command)
136 self.connection.commit()
139 condition_fields, condition_values):
140 """ creates a view of the given table where the values are selected
141 using the condition values. See the help for update_data()
147 sql_command =
'CREATE VIEW %s AS SELECT * FROM %s WHERE ' % (view_name, table_name)
149 sql_command += condition
150 log.info(
"Creating view %s", sql_command)
151 self.cursor.execute(sql_command)
153 def create_view_of_best_records(self, view_name, table_name, orderby, n_records):
158 sql_command =
"""CREATE VIEW %s AS SELECT * FROM %s
159 ORDER BY %s ASC LIMIT %d """ % (view_name, table_name, orderby, n_records)
160 log.info(
"Creating view %s", sql_command)
161 self.cursor.execute(sql_command)
164 """ Removes a view from the database """
165 self.cursor.execute(
'DROP VIEW %s' % view_name)
167 def get_table(self, table_name, fields=False, orderby=False):
168 """ Returns th fields requested from the table """
169 fields = self.get_fields_string(fields)
170 sql_command =
"SELECT %s FROM %s " % (fields, table_name)
172 sql_command +=
" ORDER BY %s ASC" % orderby
176 def get_fields_string(self, fields, field_delim=","):
178 return field_delim.join(fields)
182 """ Closes the database """
185 self.connection.close()
188 """ creates a condition applying each value to each field
191 for field,value
in zip(fields,values):
192 s +=
"%s=%s AND " % (field, value)
200 Gets info about a table and retuns all the types in it
203 sql_command =
"PRAGMA table_info(%s)" % name
204 self.cursor.execute(sql_command)
205 info = self.cursor.fetchall()
210 elif row[2] ==
"DOUBLE":
212 elif row[2][0:7] ==
"VARCHAR":
218 Get the names of the columns for a given table
221 sql_command =
"PRAGMA table_info(%s)" % name
222 self.cursor.execute(sql_command)
223 info = self.cursor.fetchall()
224 return [ row[1]
for row
in info]
226 def execute_sql_command(self, sql_command):
228 self.cursor.execute(sql_command)
229 self.connection.commit()
234 Add a column to a table
235 column - the name of the column.
236 data_type - the type: int, float, str
238 sql_typename = get_sql_type_name(data_type)
239 sql_command =
"ALTER TABLE %s ADD %s %s" % (table, column, sql_typename)
240 self.execute_sql_command(sql_command)
242 def add_columns(self, table, names, types, check=True):
244 Add columns to the database. If check=True, columns with names
245 already in the database are skipped. If check=False no check
246 is done and trying to add a column that already exists will
251 for name, dtype
in zip(names, types):
252 if name
not in col_names:
255 for name, dtype
in zip(names, types):
258 def get_tables_names(self):
259 sql_command =
""" SELECT tbl_name FROM sqlite_master """
261 names = [d[0]
for d
in data]
267 Prompt for tables so the user can choose one
271 tables = self.get_tables_names()
274 while say
not in (
'n',
'y'):
275 say = raw_input(
"Use table %s (y/n) " % t)
280 return table_name, columns
283 def drop_columns(self, table, columns):
288 names_txt =
", ".join(cnames)
290 "CREATE TEMPORARY TABLE backup(%s);" % names_txt,
291 "INSERT INTO backup SELECT %s FROM %s" % (names_txt, table),
292 "DROP TABLE %s;" % table,
293 "CREATE TABLE %s(%s);" % (table, names_txt),
294 "INSERT INTO %s SELECT * FROM backup;" % table,
295 "DROP TABLE backup;",
297 for command
in sql_command:
300 self.cursor.execute(command)
302 def print_data(data, delimiter=" "):
303 """ Prints the data recovered from a database """
305 line = delimiter.join([str(x)
for x
in row])
308 def write_data(data,output_file,delimiter=" "):
309 """writes data to a file. The output file is expected to be a python
311 w = csv.writer(output_file, delimiter=delimiter)
315 def get_sql_type_name(data_type):
316 if(data_type == int):
318 elif(data_type == float):
320 elif(data_type == str):
323 def open(fn_database):
325 db.connect(fn_database)
328 def read_data(fn_database, sql_command):
330 db.connect(fn_database)
331 data = db.retrieve_data(sql_command)
335 def get_sorting_indices(l):
336 """ Return indices that sort the list l """
337 pairs = [(element, i)
for i,element
in enumerate(l)]
339 indices = [p[1]
for p
in pairs]
342 def merge_databases(fns, fn_output, tbl):
344 Reads a table from a set of database files into a single file
345 Makes sure to reorder all column names if neccesary before merging
350 names = db.get_table_column_names(tbl)
351 types = db.get_table_types(tbl)
352 indices = get_sorting_indices(names)
353 sorted_names = [ names[i]
for i
in indices]
354 sorted_types = [ types[i]
for i
in indices]
355 log.info(
"Merging databases. Saving to %s", fn_output)
357 out_db.create(fn_output, overwrite=
True)
358 out_db.connect(fn_output)
359 out_db.create_table(tbl, sorted_names, sorted_types)
361 log.debug(
"Reading %s",fn)
363 names = db.get_table_column_names(tbl)
365 they_are_sorted =
",".join(names)
366 log.debug(
"Retrieving %s", they_are_sorted)
367 sql_command =
"SELECT %s FROM %s" % (they_are_sorted, tbl)
368 data = db.retrieve_data(sql_command)
369 out_db.store_data(tbl, data)