module sql.sql_interface
¶
Short summary¶
module pyensae.sql.sql_interface
Abstract class to connect to a SQL server using various way. It will be used to implement magic functions
Classes¶
class |
truncated documentation |
---|---|
a simple class which allows auto completion for tables, columns… |
|
Abstract class to connect to a SQL server using various way. It will be used to implement magic functions |
|
a specific exception |
Properties¶
property |
truncated documentation |
---|---|
returns the true name of the object |
|
returns the filtered name |
Static Methods¶
staticmethod |
truncated documentation |
---|---|
removes unavailable characters |
|
Methods¶
method |
truncated documentation |
---|---|
creates an instance with a given name |
|
Initializes the object. |
|
usual |
|
add a subname to the class |
|
add a function to the database which can be called in a SELECT statement |
|
close the connection to the database |
|
connection to the database |
|
drops a table |
|
execute a SQL query |
|
The function does the same thing as |
|
returns the list of columns in a table |
|
returns the list of tables in the database |
|
import a dataframe into the database |
|
import a flat file as a table, we assume the columns separator is |
|
the method create an object which contains a class the user could use to speed the typing SQL queries, functions … |
|
replaces autocompletion object by their real names |
|
refresh the auto completion |
Documentation¶
Abstract class to connect to a SQL server using various way. It will be used to implement magic functions
- class pyensae.sql.sql_interface.AutoCompletionSQLObject(name)¶
Bases:
object
a simple class which allows auto completion for tables, columns…
creates an instance with a given name
- property _¶
returns the true name of the object
- __init__(name)¶
creates an instance with a given name
- _add(name)¶
add a subname to the class
- Parameters:
name – string
- Returns:
an AutoCompletionSQLObject
the filtered name (
_f
) of the new object will be added toself.__dict__
, if an object already exists with the same name, it will raise an exception
- property _f¶
returns the filtered name
- static _filter_name(name)¶
removes unavailable characters
- class pyensae.sql.sql_interface.InterfaceSQL(obj)¶
Bases:
object
Abstract class to connect to a SQL server using various way. It will be used to implement magic functions
Initializes the object.
- Parameters:
obj – anything, see below
obj
can be a:- file –> the class
Database
will be used, we assume this file is sqlite database, the file does not have to exist, in that case, it will created
- file –> the class
- __init__(obj)¶
Initializes the object.
- Parameters:
obj – anything, see below
obj
can be a:- file –> the class
Database
will be used, we assume this file is sqlite database, the file does not have to exist, in that case, it will created
- file –> the class
- _exp = re.compile('(DB[.]CC[.][a-zA-Z0-9_]+([.][a-zA-Z0-9_]+)*)')¶
- add_function(code_function)¶
add a function to the database which can be called in a SELECT statement
- Parameters:
code_function – pointer to the function
- close()¶
close the connection to the database
- connect()¶
connection to the database
- static create(obj)¶
- Parameters:
obj – a filename, a connection string, …
obj
can be a:- file –> the class
Database
will be used, we assume this file is sqlite database, the file does not have to exist, in that case, it will created
- file –> the class
sqlite3.Connection –> the object will be wrapped into a
Database
InterfaceSQL –> returns the object itself
Changed in version 1.1: Parameter dbfile can be of type sqlite3.Connection.
- drop_table(table_name)¶
drops a table
- Parameters:
table – table name
- execute(sql_query)¶
execute a SQL query
- Parameters:
sql_query – query to execute
- Returns:
pandas DataFrame
The function takes care of the unexpected syntax introduction by the autocompletion object: it just replaces
DB.CC.<name>
by thetrue_name
.
- execute_clean_query(sql_query)¶
The function does the same thing as
execute()
but it does not replace autocompletion object. It is this function which should be overloaded by classes inheriting from this one.- Parameters:
sql_query – query to execute
- Returns:
pandas DataFrame
- get_table_columns(table_name, as_dict=True)¶
returns the list of columns in a table
- Parameters:
table_name – table name
as_dict – True, as dictionary, as a list otherwise
- Returns:
dictionary { “column”: (position, type) } or a list
- get_table_list()¶
returns the list of tables in the database
- Returns:
list of strings
- import_dataframe(tablename, df)¶
import a dataframe into the database
- Parameters:
tablename – name of the table
df – dataframe
- Returns:
the number of added rows
- import_flat_file(filename, table_name)¶
import a flat file as a table, we assume the columns separator is
\t
and the file name contains a header- Parameters:
filename – filename
table – table name
- Returns:
the number of added rows
- populate_completion()¶
the method create an object which contains a class the user could use to speed the typing SQL queries, functions in a notebook
This object will added with the name
CC
, it is returned by the function.- Returns:
The method should be called when the method
connect()
is called.
- process_query(sql_query)¶
replaces autocompletion object by their real names
- Parameters:
sql_query – SQL query
- Returns:
clean sql_query
- refresh_completion()¶
refresh the auto completion
- Returns:
completion object
- exception pyensae.sql.sql_interface.InterfaceSQLException(message)¶
Bases:
BaseException
a specific exception
- Parameters:
message – exception message
- __init__(message)¶
- Parameters:
message – exception message
- __str__()¶
usual