module sql.database_main
¶
Short summary¶
module pyensae.sql.database_main
generic class to access a SQL database
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)¶
Bases:
DatabaseCore
,DatabaseImportExport
,DatabaseObject
,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)¶
- 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)¶
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)¶
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')¶
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)¶
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)¶
Converts a SQL request into a :epkg:`pandas:Dataframe`.
- Parameters:
request – SQL request
- Returns:
DataFrame