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)[source]¶ 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
-
_add
(name)[source]¶ 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
-
property
-
class
pyensae.sql.sql_interface.
InterfaceSQL
(obj)[source]¶ 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)[source]¶ 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)[source]¶ add a function to the database which can be called in a SELECT statement
- Parameters
code_function – pointer to the function
-
static
create
(obj)[source]¶ - 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.
-
execute
(sql_query)[source]¶ 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)[source]¶ 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)[source]¶ 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
-
import_dataframe
(tablename, df)[source]¶ 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)[source]¶ 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
()[source]¶ 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)[source]¶ replaces autocompletion object by their real names
- Parameters
sql_query – SQL query
- Returns
clean sql_query