Skip to content

Latest commit

 

History

History
238 lines (137 loc) · 5.89 KB

dbpp.utils.sqlutils.md

File metadata and controls

238 lines (137 loc) · 5.89 KB

dbpp.widgets package: GuiBaseClass - AutoScrollbar - Balloon - Ctext - LabEntry - RoText - Scrolled - SqlText - StatusBar - TableView - TextMixins - XTableView - XTreeView

dbpp.kroki - dbpp.kroki.KrokiEncoder - dbpp.utils - dbpp.utils.SqlUtils -

apps: dbpp.peditor

module utils.sqlutils

dbpp.utils.SqlUtils - class to work with Sqlite3 and Csv files.

This class provides methods to convert CSV and TAB files into Sqlite3 databases and as well allows the extraction of data from databases into CSV and TAB files using Python as programming language.

The class has the following attributes and methods:

@startuml
class SqlUtils {
     + self.filename
     + __init__(filename)
     + csv2sql(filename)
     + getTables()
     + getViews()
     + getColumns()
}
@enduml

Examples:

     >>> import sys, os
     >>> sys.path.append(os.path.join(os.path.dirname(__file__), "..", ".."))
     >>> import dbpp.utils.sqlutils as sql
     >>> sqlo = sql.SqlUtils(':memory:')
     >>> sqlo.get_tables() 
     []
     >>> tabfile = os.path.join(os.path.dirname(__file__),"..","data","iris.tab")
     >>> tabfile
     '/home/groth/workspace/dbpp/dbpp/utils/../data/iris.tab'
     >>> sqlo.csv2sql(tabfile,"iris")
     150
     >>> sqlo.get_tables()
     ['iris']
     >>> sqlo.select("select RNames, Sepal_Length from iris limit 2")
     (['RNames', 'Sepal_Length'], [('R001', '5.1'), ('R002', '4.9')])

function main

main(argv)

Runs the terminal application usually with sys.argv.

Arguments:

  • argv (list): arguments are in order: application. csvfile, sqlitefile, tablename and optionally tabchar and quotechar

class SqlUtils

Class to import and export data and to query information out of SQLite databases.

Attributes:

  • self.filename (str): the name of the SQLite 3 database
  • self.connection (sqlite3.Connection): the connection to the current database

method __init__

__init__(filename=':memory:')

Initialize the object with a database filename or in memory.

Args:

  • filename (str): the filename of a SQLite 3 database, if it does not exists, it is silently created, defaults to ':memory:'

method csv2sql

csv2sql(csvfile, tablename, delimiter='\t', quotechar='"')

Import a CSV or TAB file as a new table into the data base.

Arguments:

  • csvfile (str): filename of a csvfile
  • tablename (str): name of the table to create in the current database
  • delimiter (chr): column delimiter, defaults to ' ' the tabstop
  • quotechar (chr): quoting character for strings with spaces, defaults to the double quote

Returns:

  • int : the number of rows added

method get_columns

get_columns(tname)

Return the column names for the given table or view name.

method get_tables

get_tables()

Return the names of the tables of the database.

method get_views

get_views()

Return the names of the views of the database.

method select

select(statement)

Return the result set for the given select statement.

method sql2csv

sql2csv(tablename, csvfile)

Exporting a table from a SQLite3 database to a CSV file

Args:

  • tablename (str): name of the table to export
  • csvfile (str): name of the csv file for output

Returns:

  • int : number of data rows exported

This file was automatically generated via lazydocs.