SQL

magic commands

The magic command implements an easy way to access a SQLite3 database.

pyensae.sql.magic_sql.MagicSQL (self, shell = None, kwargs)

Defines SQL commands to play with sqlite3 See notebook SQL Magic Commands with SQLite in a Notebook.

database

These classes implements manipulation with databases. You should prefer pandas or sqlite3 instead.

pyensae.sql.Database (self, dbfile, engine = ‘SQLite’, user = None, password = None, host = ‘localhost’, LOG = <function fLOG at 0x7fb2d395a488>, attach = None)

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.

pyensae.sql.sql_interface_database.InterfaceSQLDatabase (self, filename)

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

import file

These methods do not handle sparse files but they guess types and handles missing values when converting a file into a database or a dataframe. You should prefer :epkg:`pandas` or :epkg:`*py:sqlite3` instead. They become sometimes useful if :epkg:`pandas` fails.

pyensae.sql.import_flatfile_into_database_pandas (filedb, filetext, table = None, engine = ‘SQLite’, host = ‘localhost’, add_key = None, fLOG = <built-in function print>, options)

Function which imports a file into a database using pandas. It the table exists, it removes it first. There is no addition.

pyensae.sql.import_flatfile_into_database (filedb, filetext, table = None, header = True, columns = None, engine = ‘SQLite’, host = ‘localhost’, add_key = None, encoding = ‘utf-8’, fLOG = <built-in function print>)

Function which imports a file into a database. It the table exists, it removes it first. There is no addition.

These class reads :epkg:`csv` files. You should use them if regulars ways fail.

pyensae.sql.file_text_binary.TextFile (self, filename, errors = None, fLOG = <function noLOG at 0x7fb2d395a400>, buffer_size = 1048576, filter = None, separated = False, encoding = ‘utf-8’)

This class opens a text file as if it were a binary file. It can deal with null characters which are missed by open function.

pyensae.sql.TextFileColumns (self, filename, errors = None, fLOG = <function noLOG at 0x7fb2d395a400>, force_header = False, changes = None, force_noheader = False, regex = None, filter = None, fields = None, keep_text_when_bad_type = False, break_at = -1, strip_space = True, force_sep = None, nb_line_guess = 100, mistake = 3, encoding = ‘utf-8’, strict_separator = False)

This class opens a text file as if it were a binary file. It can deal with null characters. The file is interpreted as a TSV file or file containing columns. The separator is found automatically. The columns are assumed to be in the first line but it is not mandatory. It walks along a file through an iterator, every line is automatically converted into a dictionary { column : value }. If the class was able to guess what type is which column, the conversion will automatically take place.