Coverage for src/pyensae/sql/database_main.py: 85%
67 statements
« prev ^ index » next coverage.py v7.2.7, created at 2023-07-03 02:16 +0200
1"""
2@file
3@brief generic class to access a SQL database
4"""
5from pyquickhelper.loghelper import fLOG
6from .database_core import DatabaseCore
7from .database_import_export import DatabaseImportExport
8from .database_object import DatabaseObject
9from .database_join_group import DatabaseJoinGroup
12class Database(DatabaseCore, DatabaseImportExport, DatabaseObject, DatabaseJoinGroup):
13 """
14 This class allows the user to load table from text files and store them into a
15 SQL file which can be empty or not,
16 it is using :epkg:`SQLite3` module.
17 Under Windows, you can use
18 `SQLiteSpy <http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index>`_
19 to have a graphical overview of the database.
20 Parameter *dbfile* can be of type
21 `sqlite3.Connection <https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection>`_.
22 """
24 def __init__(self, dbfile, engine="SQLite", user=None, password=None,
25 host="localhost", LOG=fLOG, attach=None):
26 """
28 @param dbfile database file (use ``:memory:`` to avoid creating a file and using only memory)
29 it can also contain several files separated by ;
30 ``name_file ; nickname,second_file ; ...``
31 @param engine SQLite or MySQL (if it is installed)
32 @param user user if needed
33 @param password password if needed
34 @param host to connect to a MSSQL database
35 @param LOG LOG function
36 @param attach dictionary: { nickname: filename }, list of database to attach
38 @warning If the folder does not exist, it will be created
39 """
40 DatabaseJoinGroup.__init__(self)
41 DatabaseCore.__init__(self, sql_file=dbfile, engine=engine, user=user, password=password,
42 host=host, LOG=LOG, attach=attach)
44 @staticmethod
45 def schema_database(df, add_id=True):
46 """
47 Returns the schema for a database which would contains this database.
49 @param df pandas DataFrame
50 @param add_id if True, adds an index "PRIMARYKEY"
51 @return dictionary { index_column: (name, type) }
52 """
53 schema = {i: (l, str) for i, l in enumerate(df.columns)}
54 if add_id is not None:
55 if isinstance(add_id, bool):
56 if add_id:
57 add_id = "PRIMARYKEY"
58 schema[-1] = (add_id, int, "PRIMARYKEY", "AUTOINCREMENT")
59 else:
60 schema[-1] = (add_id, int, "PRIMARYKEY", "AUTOINCREMENT")
62 if len(df) > 0:
63 # we use the first row to determine type
64 for i, v in enumerate(df.values[0]):
65 if not isinstance(v, str):
66 schema[i] = (schema[i][0], type(v))
67 return schema
69 @staticmethod
70 def fill_sql_table(df, filename_or_database, tablename, add_id="idr", **kwargs):
71 """
72 Returns a Database object, creates the database if it does not exists,
73 same for the table.
75 @param df pandas DataFrame
76 @param filename_or_database filename or Database object,
77 in that second case, we assume method connect was called before
78 @param tablename table name
79 @param add_id if != None then the function adds an id, it first takes the
80 ``max(id)`` and goes on incrementing it
81 @param kwargs sent to @see cl Database
82 @return ``Database`` object (new or the one from the parameters),
83 in both case, the database is not disconnected
85 .. exref::
86 :title: import a DataFrame into a SQL table
87 :tag: SQL
89 ::
91 values = [ {"name":"A", "age":10, "score":34.5 },
92 {"name":"B", "age":20, "score":-34.5 }, ]
93 df = pandas.DataFrame(values)
94 dbf = "something.db3"
95 db = Database.fill_sql_table(df, dbf, "mytable")
97 This example could be replaced by:
99 ::
101 values = [ {"name":"A", "age":10, "score":34.5 },
102 {"name":"B", "age":20, "score":-34.5 }, ]
103 df = pandas.DataFrame(values)
104 dbf = "something.db3"
105 db = Database(dbf)
106 db.connect()
107 db.import_dataframe(df, "mytable)
108 db.close()
109 """
111 schema = Database.schema_database(df, add_id)
113 if isinstance(filename_or_database, str):
114 db = Database(filename_or_database, **kwargs)
115 db.connect()
117 if tablename not in db.get_table_list():
118 cursor = db.create_table(tablename, schema)
119 db.append_values(df.values, tablename, schema, cursor=cursor)
120 else:
121 db.append_values(df.values, tablename, schema)
122 else:
123 db = filename_or_database
124 if tablename not in db.get_table_list():
125 cursor = db.create_table(tablename, schema)
126 db.append_values(df.values, tablename, schema, cursor=cursor)
127 else:
128 db.append_values(df.values, tablename, schema)
130 return db
132 def import_dataframe(self, df, tablename, add_id="idr"):
133 """
134 Imports a DataFrame into a table.
136 @param df pandas DataFrame
137 @param tablename table name
138 @param add_id an index, maybe to be added
139 @return self
140 """
141 return Database.fill_sql_table(df, self, tablename, add_id)
143 def to_df(self, request):
144 """
145 Converts a SQL request into a :epkg:`pandas:Dataframe`.
147 @param request SQL request
148 @return DataFrame
149 """
150 import pandas # pylint: disable=C0415
151 cols = self.get_sql_columns(request)
152 iter = self.execute_view(request, nolog=True)
153 return pandas.DataFrame(iter, columns=cols)
155 def copy_to(self, db, subset=None):
156 """
157 Copies all tables into db, we assume both database are not connected.
159 @param db another database (possibly empty)
160 @param subset list of tables to copy or None for all
161 """
162 self.connect()
163 db.connect()
164 for tbl in self.get_table_list():
165 if subset is None or tbl in subset:
166 self.LOG("copy_to: create table " + tbl)
167 sch = self.get_table_columns_list(tbl, True)
168 curins = db.create_table(tbl, sch)
169 cursor = self.execute("SELECT * FROM %s" % tbl)
170 buffer = []
171 for row in cursor:
172 buffer.append(row)
173 if len(buffer) >= 1000:
174 db.insert(tbl, buffer, cursor=curins)
175 buffer = []
176 if len(buffer) > 0:
177 db.insert(tbl, buffer)
178 db.commit()
179 cursor.close()
180 self.close()
181 db.close()