1 """@namespace IMP.EMageFit.database
2 Utility functions to manage SQL databases with sqlite3.
5 import sqlite3
as sqlite
10 log = logging.getLogger(
"Database")
15 """ Class to manage a SQL database built with sqlite3 """
19 self.connection =
None
25 def create(self, filename, overwrite=False):
26 """ Creates a database by simply connecting to the file """
27 log.info(
"Creating database")
28 if overwrite
and os.path.exists(filename):
30 sqlite.connect(filename)
33 """ Connects to the database in filename """
34 if not os.path.isfile(filename):
35 raise IOError(
"Database file not found: %s" % filename)
36 self.connection = sqlite.connect(filename)
37 self.cursor = self.connection.cursor()
40 """ Checks if the class is connected to the database filename """
41 if self.connection
is None:
42 raise ValueError(
"The database has not been created "
43 "or connection not established ")
46 """ Creates a table. It expects a sorted dictionary
47 of (data_field,typename) entries """
48 log.info(
"Creating table %s", table_name)
50 sql_command =
"CREATE TABLE %s (" % (table_name)
51 for name, data_type
in zip(column_names, column_types):
52 sql_typename = get_sql_type_name(data_type)
53 sql_command +=
"%s %s," % (name, sql_typename)
56 sql_command = sql_command[0:n - 1] +
")"
57 log.debug(sql_command)
58 self.cursor.execute(sql_command)
59 self.connection.commit()
63 Delete a table if it exists
65 log.info(
"Deleting table %s", table_name)
67 sql_command =
"DROP TABLE IF EXISTS %s" % (table_name)
68 log.debug(sql_command)
69 self.cursor.execute(sql_command)
70 self.connection.commit()
73 """ Inserts information in a given table of the database.
74 The info must be a list of tuples containing as many values
75 as columns in the table
76 Conversion of values is done AUTOMATICALLY after checking the
77 types stored in the table
81 tuple_format =
"(" +
"?," * (n - 1) +
"?)"
82 sql_command =
"INSERT INTO %s VALUES %s " % (table_name, tuple_format)
90 y = [apply_type(i)
for i, apply_type
in zip(x, types)]
91 self.cursor.execute(sql_command, y)
92 self.connection.commit()
95 """ Inserts information in a given table of the database.
96 The info must be a list of tuples containing as many values
97 as columns in the table
98 Conversion of values is done AUTOMATICALLY after checking the
99 types stored in the table
102 log.warning(
"Inserting empty data")
106 tuple_format =
"(" +
"?," * (n - 1) +
"?)"
107 sql_command =
"INSERT INTO %s VALUES %s " % (table_name, tuple_format)
111 for i
in range(len(data)):
112 data[i] = [apply_type(d)
for d, apply_type
in zip(data[i], types)]
113 self.cursor.executemany(sql_command, data)
114 self.connection.commit()
117 """ Retrieves data from the database using the sql_command
118 returns the records as a list of tuples"""
120 log.debug(
"Retrieving data: %s" % sql_command)
121 self.cursor.execute(sql_command)
122 return self.cursor.fetchall()
129 """ updates the register in the table identified by the condition
130 values for the condition fields
133 sql_command =
"UPDATE %s SET " % (table_name)
134 for field, value
in zip(updated_fields, updated_values):
135 sql_command +=
"%s=%s," % (field, value)
136 sql_command = sql_command.rstrip(
",") +
" WHERE "
138 sql_command = sql_command + s
140 log.debug(
"Updating %s: %s", table_name, sql_command)
141 self.cursor.execute(sql_command)
142 self.connection.commit()
145 condition_fields, condition_values):
146 """ creates a view of the given table where the values are selected
147 using the condition values. See the help for update_data()
153 sql_command =
'CREATE VIEW %s AS SELECT * FROM %s WHERE ' % (
154 view_name, table_name)
156 condition_fields, condition_values)
157 sql_command += condition
158 log.info(
"Creating view %s", sql_command)
159 self.cursor.execute(sql_command)
161 def create_view_of_best_records(
171 sql_command =
"""CREATE VIEW %s AS SELECT * FROM %s
172 ORDER BY %s ASC LIMIT %d """ % (view_name, table_name, orderby, n_records)
173 log.info(
"Creating view %s", sql_command)
174 self.cursor.execute(sql_command)
177 """ Removes a view from the database """
178 self.cursor.execute(
'DROP VIEW %s' % view_name)
180 def get_table(self, table_name, fields=False, orderby=False):
181 """ Returns th fields requested from the table """
182 fields = self.get_fields_string(fields)
183 sql_command =
"SELECT %s FROM %s " % (fields, table_name)
185 sql_command +=
" ORDER BY %s ASC" % orderby
189 def get_fields_string(self, fields, field_delim=","):
191 return field_delim.join(fields)
195 """ Closes the database """
198 self.connection.close()
201 """ creates a condition applying each value to each field
204 for field, value
in zip(fields, values):
205 s +=
"%s=%s AND " % (field, value)
213 Gets info about a table and retuns all the types in it
216 sql_command =
"PRAGMA table_info(%s)" % name
217 self.cursor.execute(sql_command)
218 info = self.cursor.fetchall()
223 elif row[2] ==
"DOUBLE":
225 elif row[2][0:7] ==
"VARCHAR":
231 Get the names of the columns for a given table
234 sql_command =
"PRAGMA table_info(%s)" % name
235 self.cursor.execute(sql_command)
236 info = self.cursor.fetchall()
237 return [row[1]
for row
in info]
239 def execute_sql_command(self, sql_command):
241 self.cursor.execute(sql_command)
242 self.connection.commit()
246 Add a column to a table
247 column - the name of the column.
248 data_type - the type: int, float, str
250 sql_typename = get_sql_type_name(data_type)
251 sql_command =
"ALTER TABLE %s ADD %s %s" % (
252 table, column, sql_typename)
253 self.execute_sql_command(sql_command)
257 Add columns to the database. If check=True, columns with names
258 already in the database are skipped. If check=False no check
259 is done and trying to add a column that already exists will
264 for name, dtype
in zip(names, types):
265 if name
not in col_names:
268 for name, dtype
in zip(names, types):
271 def get_tables_names(self):
272 sql_command =
""" SELECT tbl_name FROM sqlite_master """
274 names = [d[0]
for d
in data]
279 Prompt for tables so the user can choose one
283 tables = self.get_tables_names()
286 while say
not in (
'n',
'y'):
287 say = raw_input(
"Use table %s (y/n) " % t)
292 return table_name, columns
294 def drop_columns(self, table, columns):
299 names_txt =
", ".join(cnames)
301 "CREATE TEMPORARY TABLE backup(%s);" % names_txt,
302 "INSERT INTO backup SELECT %s FROM %s" % (names_txt, table),
303 "DROP TABLE %s;" % table,
304 "CREATE TABLE %s(%s);" % (table, names_txt),
305 "INSERT INTO %s SELECT * FROM backup;" % table,
306 "DROP TABLE backup;",
308 for command
in sql_command:
311 self.cursor.execute(command)
315 """ Prints the data recovered from a database """
317 line = delimiter.join([str(x)
for x
in row])
322 """writes data to a file. The output file is expected to be a python
324 w = csv.writer(output_file, delimiter=delimiter)
329 def get_sql_type_name(data_type):
330 if(data_type == int):
332 elif(data_type == float):
334 elif(data_type == str):
341 def open(fn_database):
343 db.connect(fn_database)
347 def read_data(fn_database, sql_command):
349 db.connect(fn_database)
350 data = db.retrieve_data(sql_command)
356 """ Return indices that sort the list l """
357 pairs = sorted([(element, i)
for i, element
in enumerate(l)])
358 indices = [p[1]
for p
in pairs]
364 Reads a table from a set of database files into a single file
365 Makes sure to reorder all column names if necessary before merging
370 names = db.get_table_column_names(tbl)
371 types = db.get_table_types(tbl)
373 sorted_names = [names[i]
for i
in indices]
374 sorted_types = [types[i]
for i
in indices]
375 log.info(
"Merging databases. Saving to %s", fn_output)
377 out_db.create(fn_output, overwrite=
True)
378 out_db.connect(fn_output)
379 out_db.create_table(tbl, sorted_names, sorted_types)
381 log.debug(
"Reading %s", fn)
383 names = sorted(db.get_table_column_names(tbl))
384 they_are_sorted =
",".join(names)
385 log.debug(
"Retrieving %s", they_are_sorted)
386 sql_command =
"SELECT %s FROM %s" % (they_are_sorted, tbl)
387 data = db.retrieve_data(sql_command)
388 out_db.store_data(tbl, data)
def get_table_column_names
Get the names of the columns for a given table.
def close
Closes the database.
Class to manage a SQL database built with sqlite3.
def get_table_types
Gets info about a table and retuns all the types in it.
def get_sorting_indices
Return indices that sort the list l.
def store_data
Inserts information in a given table of the database.
def get_condition_string
creates a condition applying each value to each field
def create_table
Creates a table.
def retrieve_data
Retrieves data from the database using the sql_command returns the records as a list of tuples...
def create
Creates a database by simply connecting to the file.
def print_data
Prints the data recovered from a database.
def get_table
Returns th fields requested from the table.
def drop_table
Delete a table if it exists.
def create_view
creates a view of the given table where the values are selected using the condition values...
def connect
Connects to the database in filename.
def add_column
Add a column to a table column - the name of the column.
def store_dataV1
Inserts information in a given table of the database.
def select_table
Prompt for tables so the user can choose one.
def merge_databases
Reads a table from a set of database files into a single file Makes sure to reorder all column names ...
def add_columns
Add columns to the database.
def drop_view
Removes a view from the database.
def update_data
updates the register in the table identified by the condition values for the condition fields ...
def check_if_is_connected
Checks if the class is connected to the database filename.
def write_data
writes data to a file.