module sql.database_main

Inheritance diagram of pyensae.sql.database_main

Short summary

module pyensae.sql.database_main

generic class to access a SQL database

source on GitHub

Classes

class

truncated documentation

Database

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

fill_sql_table

Returns a Database object, creates the database if it does not exists, same for the table.

schema_database

Returns the schema for a database which would contains this database.

Methods

method

truncated documentation

__init__

copy_to

Copies all tables into db, we assume both database are not connected.

import_dataframe

Imports a DataFrame into a table.

to_df

Converts a SQL request into a :epkg:`pandas:Dataframe`.

Documentation

generic class to access a SQL database

source on GitHub

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.

source on GitHub

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

source on GitHub

__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

source on GitHub

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

source on GitHub

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 it

  • kwargs – 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()

source on GitHub

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

source on GitHub

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) }

source on GitHub

to_df(request)[source]

Converts a SQL request into a :epkg:`pandas:Dataframe`.

Parameters

request – SQL request

Returns

DataFrame

source on GitHub