module sql.sql_interface

Inheritance diagram of pyensae.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

source on GitHub

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)[source]

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)[source]

creates an instance with a given name

source on GitHub

_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 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)[source]

removes unavailable characters

source on GitHub

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

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

source on GitHub

_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

source on GitHub

close()[source]

close the connection to the database

source on GitHub

connect()[source]

connection to the database

source on GitHub

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

  • 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)[source]

drops a table

Parameters

table – table name

source on GitHub

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

source on GitHub

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

source on GitHub

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

source on GitHub

get_table_list()[source]

returns the list of tables in the database

Returns

list of strings

source on GitHub

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

source on GitHub

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

source on GitHub

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

AutoCompletionSQLObject

The method should be called when the method connect() is called.

source on GitHub

process_query(sql_query)[source]

replaces autocompletion object by their real names

Parameters

sql_query – SQL query

Returns

clean sql_query

source on GitHub

refresh_completion()[source]

refresh the auto completion

Returns

completion object

source on GitHub

exception pyensae.sql.sql_interface.InterfaceSQLException(message)[source]

Bases: BaseException

a specific exception

source on GitHub

Parameters

message – exception message

source on GitHub

__init__(message)[source]
Parameters

message – exception message

source on GitHub

__str__()[source]

usual

source on GitHub