1 """@namespace IMP.EMageFit.database
2 Utility functions to manage SQL databases with sqlite3.
5 import sqlite3
as sqlite
10 if sys.version_info[0] == 2:
13 log = logging.getLogger(
"Database")
18 """ Class to manage a SQL database built with sqlite3 """
22 self.connection =
None
28 def create(self, filename, overwrite=False):
29 """ Creates a database by simply connecting to the file """
30 log.info(
"Creating database")
31 if overwrite
and os.path.exists(filename):
33 sqlite.connect(filename)
36 """ Connects to the database in filename """
37 if not os.path.isfile(filename):
38 raise IOError(
"Database file not found: %s" % filename)
39 self.connection = sqlite.connect(filename)
40 self.cursor = self.connection.cursor()
43 """ Checks if the class is connected to the database filename """
44 if self.connection
is None:
45 raise ValueError(
"The database has not been created "
46 "or connection not established ")
49 """ Creates a table. It expects a sorted dictionary
50 of (data_field,typename) entries """
51 log.info(
"Creating table %s", table_name)
53 sql_command =
"CREATE TABLE %s (" % (table_name)
54 for name, data_type
in zip(column_names, column_types):
55 sql_typename = get_sql_type_name(data_type)
56 sql_command +=
"%s %s," % (name, sql_typename)
59 sql_command = sql_command[0:n - 1] +
")"
60 log.debug(sql_command)
61 self.cursor.execute(sql_command)
62 self.connection.commit()
66 Delete a table if it exists
68 log.info(
"Deleting table %s", table_name)
70 sql_command =
"DROP TABLE IF EXISTS %s" % (table_name)
71 log.debug(sql_command)
72 self.cursor.execute(sql_command)
73 self.connection.commit()
76 """ Inserts information in a given table of the database.
77 The info must be a list of tuples containing as many values
78 as columns in the table
79 Conversion of values is done AUTOMATICALLY after checking the
80 types stored in the table
84 tuple_format =
"(" +
"?," * (n - 1) +
"?)"
85 sql_command =
"INSERT INTO %s VALUES %s " % (table_name, tuple_format)
91 y = [apply_type(i)
for i, apply_type
in zip(x, types)]
92 self.cursor.execute(sql_command, y)
93 self.connection.commit()
96 """ Inserts information in a given table of the database.
97 The info must be a list of tuples containing as many values
98 as columns in the table
99 Conversion of values is done AUTOMATICALLY after checking the
100 types stored in the table
103 log.warning(
"Inserting empty data")
107 tuple_format =
"(" +
"?," * (n - 1) +
"?)"
108 sql_command =
"INSERT INTO %s VALUES %s " % (table_name, tuple_format)
112 for i
in range(len(data)):
113 data[i] = [apply_type(d)
for d, apply_type
in zip(data[i], types)]
114 self.cursor.executemany(sql_command, data)
115 self.connection.commit()
118 """ Retrieves data from the database using the sql_command
119 returns the records as a list of tuples"""
121 log.debug(
"Retrieving data: %s" % sql_command)
122 self.cursor.execute(sql_command)
123 return self.cursor.fetchall()
130 """ updates the register in the table identified by the condition
131 values for the condition fields
134 sql_command =
"UPDATE %s SET " % (table_name)
135 for field, value
in zip(updated_fields, updated_values):
136 sql_command +=
"%s=%s," % (field, value)
137 sql_command = sql_command.rstrip(
",") +
" WHERE "
139 sql_command = sql_command + s
141 log.debug(
"Updating %s: %s", table_name, sql_command)
142 self.cursor.execute(sql_command)
143 self.connection.commit()
146 condition_fields, condition_values):
147 """ creates a view of the given table where the values are selected
148 using the condition values. See the help for update_data()
154 sql_command =
'CREATE VIEW %s AS SELECT * FROM %s WHERE ' % (
155 view_name, table_name)
157 condition_fields, condition_values)
158 sql_command += condition
159 log.info(
"Creating view %s", sql_command)
160 self.cursor.execute(sql_command)
162 def create_view_of_best_records(
172 sql_command =
"""CREATE VIEW %s AS SELECT * FROM %s
173 ORDER BY %s ASC LIMIT %d """ % (view_name, table_name,
175 log.info(
"Creating view %s", sql_command)
176 self.cursor.execute(sql_command)
179 """ Removes a view from the database """
180 self.cursor.execute(
'DROP VIEW %s' % view_name)
182 def get_table(self, table_name, fields=False, orderby=False):
183 """ Returns th fields requested from the table """
184 fields = self.get_fields_string(fields)
185 sql_command =
"SELECT %s FROM %s " % (fields, table_name)
187 sql_command +=
" ORDER BY %s ASC" % orderby
191 def get_fields_string(self, fields, field_delim=","):
193 return field_delim.join(fields)
197 """ Closes the database """
200 self.connection.close()
203 """ creates a condition applying each value to each field
206 for field, value
in zip(fields, values):
207 s +=
"%s=%s AND " % (field, value)
215 Gets info about a table and returns all the types in it
218 sql_command =
"PRAGMA table_info(%s)" % name
219 self.cursor.execute(sql_command)
220 info = self.cursor.fetchall()
225 elif row[2] ==
"DOUBLE":
227 elif row[2][0:7] ==
"VARCHAR":
233 Get the names of the columns for a given table
236 sql_command =
"PRAGMA table_info(%s)" % name
237 self.cursor.execute(sql_command)
238 info = self.cursor.fetchall()
239 return [row[1]
for row
in info]
241 def execute_sql_command(self, sql_command):
243 self.cursor.execute(sql_command)
244 self.connection.commit()
248 Add a column to a table
249 column - the name of the column.
250 data_type - the type: int, float, str
252 sql_typename = get_sql_type_name(data_type)
253 sql_command =
"ALTER TABLE %s ADD %s %s" % (
254 table, column, sql_typename)
255 self.execute_sql_command(sql_command)
259 Add columns to the database. If check=True, columns with names
260 already in the database are skipped. If check=False no check
261 is done and trying to add a column that already exists will
266 for name, dtype
in zip(names, types):
267 if name
not in col_names:
270 for name, dtype
in zip(names, types):
273 def get_tables_names(self):
274 sql_command =
""" SELECT tbl_name FROM sqlite_master """
276 names = [d[0]
for d
in data]
281 Prompt for tables so the user can choose one
285 tables = self.get_tables_names()
288 while say
not in (
'n',
'y'):
289 say = input(
"Use table %s (y/n) " % t)
294 return table_name, columns
296 def drop_columns(self, table, columns):
301 names_txt =
", ".join(cnames)
303 "CREATE TEMPORARY TABLE backup(%s);" % names_txt,
304 "INSERT INTO backup SELECT %s FROM %s" % (names_txt, table),
305 "DROP TABLE %s;" % table,
306 "CREATE TABLE %s(%s);" % (table, names_txt),
307 "INSERT INTO %s SELECT * FROM backup;" % table,
308 "DROP TABLE backup;",
310 for command
in sql_command:
313 self.cursor.execute(command)
317 """ Prints the data recovered from a database """
319 line = delimiter.join([str(x)
for x
in row])
324 """writes data to a file. The output file is expected to be a python
326 w = csv.writer(output_file, delimiter=delimiter)
331 def get_sql_type_name(data_type):
334 elif data_type == float:
336 elif data_type == str:
343 def open(fn_database):
345 db.connect(fn_database)
349 def read_data(fn_database, sql_command):
351 db.connect(fn_database)
352 data = db.retrieve_data(sql_command)
358 """ Return indices that sort the list ls"""
359 pairs = sorted([(element, i)
for i, element
in enumerate(ls)])
360 indices = [p[1]
for p
in pairs]
366 Reads a table from a set of database files into a single file
367 Makes sure to reorder all column names if necessary before merging
372 names = db.get_table_column_names(tbl)
373 types = db.get_table_types(tbl)
375 sorted_names = [names[i]
for i
in indices]
376 sorted_types = [types[i]
for i
in indices]
377 log.info(
"Merging databases. Saving to %s", fn_output)
379 out_db.create(fn_output, overwrite=
True)
380 out_db.connect(fn_output)
381 out_db.create_table(tbl, sorted_names, sorted_types)
383 log.debug(
"Reading %s", fn)
385 names = sorted(db.get_table_column_names(tbl))
386 they_are_sorted =
",".join(names)
387 log.debug(
"Retrieving %s", they_are_sorted)
388 sql_command =
"SELECT %s FROM %s" % (they_are_sorted, tbl)
389 data = db.retrieve_data(sql_command)
390 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 returns all the types in it.
def get_sorting_indices
Return indices that sort the list ls.
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.