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
`AutoCompletionSQLObject`	a simple class which allows auto completion for tables, columns…
`InterfaceSQL`	Abstract class to connect to a SQL server using various way. It will be used to implement magic functions
`InterfaceSQLException`	a specific exception

Properties¶

property	truncated documentation
`_`	returns the true name of the object
`_f`	returns the filtered name

Static Methods¶

staticmethod	truncated documentation
`_filter_name`	removes unavailable characters
`create`

Methods¶

method	truncated documentation
`__init__`	creates an instance with a given name
`__init__`	Initializes the object.
`__init__`
`__str__`	usual
`_add`	add a subname to the class
`add_function`	add a function to the database which can be called in a SELECT statement
`close`	close the connection to the database
`connect`	connection to the database
`drop_table`	drops a table
`execute`	execute a SQL query
`execute_clean_query`	The function does the same thing as `execute()` but it does not replace autocompletion object. It is …
`get_table_columns`	returns the list of columns in a table
`get_table_list`	returns the list of tables in the database
`import_dataframe`	import a dataframe into the database
`import_flat_file`	import a flat file as a table, we assume the columns separator is `\t` and the file name contains a header
`populate_completion`	the method create an object which contains a class the user could use to speed the typing SQL queries, functions …
`process_query`	replaces autocompletion object by their real names
`refresh_completion`	refresh the auto completion

Documentation¶

Abstract class to connect to a SQL server using various way. It will be used to implement magic functions

source on GitHub

class pyensae.sql.sql_interface.AutoCompletionSQLObject(name)¶

Bases: object

a simple class which allows auto completion for tables, columns…

source on GitHub

creates an instance with a given name

source on GitHub

property _¶

returns the true name of the object

source on GitHub

__init__(name)¶

creates an instance with a given name

source on GitHub

_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 to self.__dict__, if an object already exists with the same name, it will raise an exception

source on GitHub

property _f¶

returns the filtered name

source on GitHub

static _filter_name(name)¶

removes unavailable characters

source on GitHub

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

source on GitHub

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

source on GitHub

__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

source on GitHub

_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

source on GitHub

close()¶

close the connection to the database

source on GitHub

connect()¶

connection to the database

source on GitHub

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
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.

source on GitHub

drop_table(table_name)¶

drops a table

Parameters: table – table name

source on GitHub

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 the true_name.

source on GitHub

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

source on GitHub

get_table_columns(table_name, as_dict=True)¶

returns the list of columns in a table

Parameters