module sql.database_main
¶
Classes¶
class |
truncated documentation |
---|---|
This class allows the user to load table from text files and store them into a SQL file which can be empty or not, … |
Static Methods¶
staticmethod |
truncated documentation |
---|---|
Returns a Database object, creates the database if it does not exists, same for the table. |
|
Returns the schema for a database which would contains this database. |
Methods¶
method |
truncated documentation |
---|---|
Copies all tables into db, we assume both database are not connected. |
|
Imports a DataFrame into a table. |
|
Converts a SQL request into a :epkg:`pandas:Dataframe`. |
Documentation¶
generic class to access a SQL database
-
class
pyensae.sql.database_main.
Database
(dbfile, engine='SQLite', user=None, password=None, host='localhost', LOG=<function fLOG>, attach=None)[source]¶ Bases:
pyensae.sql.database_core.DatabaseCore
,pyensae.sql.database_import_export.DatabaseImportExport
,pyensae.sql.database_object.DatabaseObject
,pyensae.sql.database_join_group.DatabaseJoinGroup
This class allows the user to load table from text files and store them into a SQL file which can be empty or not, it is using SQLite3 module. Under Windows, you can use SQLiteSpy to have a graphical overview of the database. Parameter dbfile can be of type sqlite3.Connection.
- Parameters
dbfile – database file (use
:memory:
to avoid creating a file and using only memory) it can also contain several files separated by ;name_file ; nickname,second_file ; ...
engine – SQLite or MySQL (if it is installed)
user – user if needed
password – password if needed
host – to connect to a MSSQL database
LOG – LOG function
attach – dictionary: { nickname: filename }, list of database to attach
Warning
If the folder does not exist, it will be created
-
__init__
(dbfile, engine='SQLite', user=None, password=None, host='localhost', LOG=<function fLOG>, attach=None)[source]¶ - Parameters
dbfile – database file (use
:memory:
to avoid creating a file and using only memory) it can also contain several files separated by ;name_file ; nickname,second_file ; ...
engine – SQLite or MySQL (if it is installed)
user – user if needed
password – password if needed
host – to connect to a MSSQL database
LOG – LOG function
attach – dictionary: { nickname: filename }, list of database to attach
Warning
If the folder does not exist, it will be created
-
copy_to
(db, subset=None)[source]¶ Copies all tables into db, we assume both database are not connected.
- Parameters
db – another database (possibly empty)
subset – list of tables to copy or None for all
-
static
fill_sql_table
(df, filename_or_database, tablename, add_id='idr', **kwargs)[source]¶ Returns a Database object, creates the database if it does not exists, same for the table.
- Parameters
df – pandas DataFrame
filename_or_database – filename or Database object, in that second case, we assume method connect was called before
tablename – table name
add_id – if != None then the function adds an id, it first takes the
max(id)
and goes on incrementing itkwargs – sent to
Database
- Returns
Database
object (new or the one from the parameters), in both case, the database is not disconnected
import a DataFrame into a SQL table
values = [ {"name":"A", "age":10, "score":34.5 }, {"name":"B", "age":20, "score":-34.5 }, ] df = pandas.DataFrame(values) dbf = "something.db3" db = Database.fill_sql_table(df, dbf, "mytable")
This example could be replaced by:
values = [ {"name":"A", "age":10, "score":34.5 }, {"name":"B", "age":20, "score":-34.5 }, ] df = pandas.DataFrame(values) dbf = "something.db3" db = Database(dbf) db.connect() db.import_dataframe(df, "mytable) db.close()
-
import_dataframe
(df, tablename, add_id='idr')[source]¶ Imports a DataFrame into a table.
- Parameters
df – pandas DataFrame
tablename – table name
add_id – an index, maybe to be added
- Returns
self
-
static
schema_database
(df, add_id=True)[source]¶ Returns the schema for a database which would contains this database.
- Parameters
df – pandas DataFrame
add_id – if True, adds an index “PRIMARYKEY”
- Returns
dictionary { index_column: (name, type) }
-
to_df
(request)[source]¶ Converts a SQL request into a :epkg:`pandas:Dataframe`.
- Parameters
request – SQL request
- Returns
DataFrame