Source code for pyensae.sql.database_helper

"""
Contains functions to import a text file into a database (SQLite).


:githublink:`%|py|5`
"""

import os
from .database_main import Database


[docs]def import_flatfile_into_database(filedb, filetext, table=None, header=True, columns=None, engine='SQLite', host='localhost', add_key=None, encoding="utf-8", fLOG=print): """ Function which imports a file into a database. It the table exists, it removes it first. There is no addition. :param filedb: something.db3 :param filetext: something.txt or .tsv :param table: table name (in the database), if None, the database name will be the filename without extension :param columns: if header is False, this must be specified. It should be a list of column names. :param header: boolean (does it have a header or not) :param engine: engine to use when using a SQL server (SQLite or ODBCMSSQL) :param host: host (server) :param fLOG: logging function (will display information through the command line) :param add_key: name of a key to add (or None if nothing to add) :param encoding: encoding :return: table name .. exref:: :title: Import a flat file into a SQLite database :tag: SQL :: from pyensae import import_flatfile_into_database dbf = "database.db3" file = "textfile.txt" import_flatfile_into_database(dbf, file) On Windows, `SQLiteSpy <http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index>`_ is a free tool very useful to run SQL queries against a sqlite3 database. :githublink:`%|py|43` """ # connection db = Database(filedb, engine=engine, host=host, LOG=fLOG) db.connect() if table is None: table = os.path.splitext( os.path.split(filetext)[-1])[0].replace(".", "").replace(",", "") if db.has_table(table): fLOG("remove ", table) db.remove_table(table) if header: columns = None db.import_table_from_flat_file(filetext, table, columns=columns, header=header, add_key=add_key, encoding=encoding) db.close() return table