Logo Welcome to CSVSQL

CSVSQL ist a Python module that reads a CSV file into a SQLite table and makes it accessible in your application. This is handy whenever you need to apply an SQL SELECT command on your data.

Installation

Run these commands in a terminal:

git clone https://codeberg.org/ralfhersel/csvsql.git
cd csvsql

Make these files executable:

csvsql.py, csvsql_example.sh

Ensure that the shebang in both python files matches your Python environment.

Operation Instructions

Open a terminal and navigate to your csvsql directory and run:

./csvsql_example test.csv

If everything works as expected, you will see this output:

['Name', 'Team', 'Position', 'Height', 'Weight', 'Age']
16 records
('Alvin Colina', 'Catcher', '25.18')
('Albert Pujols', 'First Baseman', '27.12')
('Alex Rios', 'Outfielder', '26.03')
('Alfonso Soriano', 'Outfielder', '31.15')
('Alfredo Amezaga', 'Outfielder', '29.12')
('Alex Escobar', 'Outfielder', '28.48')
('Alan Embree', 'Relief Pitcher', '37.1')
('Alexi Casilla', 'Second Baseman', '22.61')
('Alejandro Machado', 'Second Baseman', '24.85')
('Alex Cintron', 'Shortstop', '28.2')
('Alex Cora', 'Shortstop', '31.37')
('Alberto Callaspo', 'Shortstop', '23.87')
('Alex Gonzalez', 'Shortstop', '30.04')
('Alay Soler', 'Starting Pitcher', '27.39')
('Alfredo Simon', 'Starting Pitcher', '25.81')
('Alex Rodriguez', 'Third Baseman', '31.59')

Your csv file must have a header row which contains the column names. If your csv starts with data in the first row, you will get bullshit results. Keep in mind that CSVSQL only creates one database table from your csv file. The table is not persisted. Whenever you start over, the last database file will be deleted and reloaded with your new csv file.

Features

CSVSQL is a Python 3 module, which is meant to import in your applications. This is done by:

from csvsql import db

Next step is to initialize the db class with a csv file, for example:

data = db('test.csv') 

data is a class instance, that provides these methods:

• data = db('file.csv', reload=True)

  • initializes class db to object data

  • first parameter is the path/filename of the CSV-file

  • second parameter (optional) is reload. True (default) load CSV file into database. False does not reload CSV file, but expects an existing database which will be opened. If the database does not exist, it will be loaded from CSV anyway; regardless of this parameter setting.

• data.get_header() returns a list of the csv column headings

• data.query(sql, type='list', key=0) returns the result of an sql SELECT statement as a Python list (default), dictionary or set. type=[list|dict|set]. key=column number (default=0) for the dictionary key. Keep in mind, that a set does not allow duplicate record.

• data.close() will close the database connection

The method data.query(sql) requires further explanation. Take a look at this example:

col = data.get_header() # get table columns
sql = f"SELECT Name, Position, {col[-1]} | WHERE Name LIKE 'Al%' ORDER BY {col[2]}"
rows = data.query(sql)

The first line retrieves the list of columns, e.g.: ['Name', 'Team', 'Position', 'Height', 'Weight', 'Age']. In the second line, an SQL statement is defined and sent to the query method in line three, which will give you a Python list in 'rows'. If you look at the sql you will recognize that 'FROM tablename' is missing. It is not necessary because it will be always the same. Therefore you only use a pipe '|' instead as a replacement for 'FROM tabelname'. Instead of real column names you also can use single items from the header list, e.g.: col[0] instead of 'Name'.

Missing features

• Currently all columns are of type TEXT. I will implement an optional parameter to define custom column types.

• There is no indexing applied to the database table. There will be an optional parameter to define an index column.

• More testing with different CSV-formats. More edge-case testing.

Release Notes

Version 0.03, 2021年12月13日

• Automatic detection of CSV-Format

• Sanitizing of column names to meet SQL requirements

• Method query can return a list (default), dictionary or set. Type=[list|dict|set]

Version 0.02, 2021年12月12日

• Database will (optionally) not reloaded from CSV if it already exists. Reload=False.

• Method query can return a list (default) or a dictionary. Type=[list|dict]

Version 0.01, 2021年12月10日

• initial release

License

Author: Ralf Hersel

License: GPL3

Repository: https://codeberg.org/ralfhersel/csvsql.git

Contact

https://matrix.to/#/@ralfhersel:feneas.org