Skip to main content

Lightweight SQL database

Project description

Icecoal

Icecoal is a light weight SQL database

Targets are,

  • Apps that require SQL DB or external DB server
  • Apps that need lightweight replacement for oracle, MySQL etc.., which they use just to store and retrieve data
  • Those who want to add a database in their app without worrying about any technical jargons such as JDBC, ODBC, Connection driver etc..
  • Those who want to conveniently process CSV files with SQL.

Features:

  • Written purely in python
  • Light weight (Just 57 Kb in total)
  • No external dependencies
  • Easy to add in your app/system (Just import!)

How to install

pip install icecoal

Usage

General database operations

from icecoal import query
query("select name,age from travel_db/passengers where native='USA'")

Example

from icecoal import query

def display_names():
    display_query="""
    select
        name, age
    from
        student_db/courses
    where 
        mark>50
    """

    result = query(display_query)
    if result[0]==0:
        for row in result[2]:
            print("Name is "+row[0]+" and Age is "+row[1])
    else:
        print(result[1])

display_names()

Processing your csv data files

from icecoal import query
query("select * from path/to/passengers.csv where native='USA'")

Here icecoal expects first line of csv file to be header line.
if it is not, you can either add headers in first line
or create a new file just with headers delimited with comma and add it along with data file name in the query as below

from icecoal import query
query("select * from path/to/passengers.csv, path/to/header.csv where native='USA'")

Example

from icecoal import query

def display_names():
    display_query="""
    select
        price
    from
        D://reports/sales.csv, D://reports/header.csv
    where 
        store='online'
    """

    result = query(display_query)
    if result[0]==0:
        for row in result[2]:
            print("Price : "+row[0])
    else:
        print(result[1])

display_names()

Change delimiters other than comma

This feature is to expand users ability to process files that are delimited with chatacters other than comma.

from icecoal import setdel,query
setdel("\t")
query("select * from path/to/datafile.xyz")

setdel() function will make further calls to query function to interpret data file as custom delimited (which is given by setdel() parameter)

Get current default delimiter

from icecoal import getdel
getdel()

getdel() function will return current delimiter which is used to process data files.

Return format

[sqlcode,'<message>',[result list]]

SQL return codes

SQL Code Description
2 0 row(s) deleted
3 0 row(s) updated
1 0 row(s) selected
0 Success
-1 Query incomplete
-2 Unexpected character on position <index>
-3 Unexpected character on where clause position <index>
-4 Unsupported operator in where clause
-5 Non numeric operand with arithmatic operator <operator>
-6 Non boolean operand(LHS) with logical operator <operator>
-7 Non boolean operand(RHS) with logical operator <operator>
-8 Operator ! must be followed by =
-9 Unbalanced paranthesis on LHS of operator <operator>
-10 Unbalanced paranthesis on RHS of operator <operator>
-11 No field <fieldname> found in header file
-12 Value for variable <variablename> is missing in atleast one row
-13 Where clause condition returns non-boolean result
-14 Incomplete condition in where clause
-15 Database already exist
-16 Table already exist
-17 Database does not exist
-18 Database name is blank
-19 Not a database name
-20 Table does not exist
-21 Not a table name
-22 Header file does not exist
-23 Provided header is not a file
-24 Values count does not match table fields count

Reserved words

Keywords
SELECT
FROM
WHERE
CREATE
DATABASE
TABLE
INSERT
UPDATE
SET
INTO
TRUNCATE
DROP
DELETE

Supported SQL operations and its syntax

  • SELECT column_list FROM [path/to/]databasename/tablename[.csv] [WHERE condition]
  • CREATE DATABASE [path/to/]databasename
  • CREATE TABLE [path/to/]databasename/tablename[.csv](colum1,column2..)
  • DROP DATABASE [path/to/]databasename
  • DROP TABLE [path/to/]databasename/tablename[.csv]
  • TRUNCATE TABLE [path/to/]databasename/tablename[.csv]
  • INSERT INTO [path/to/]databasename/tablename[.csv](value1,value2,..)
  • UPDATE [path/to/]databasename/tablename[.csv] SET field1=value1,field2=value.. [WHERE condition]
  • DELETE FROM [path/to/]databasename/tablename[.csv] WHERE condition

Character escape

',",\,# shoule be escaped with \ inside a string as \',\",\\,\#
icecoal has an inbuilt function escape() for this. you can call this function as follows.

from icecoal import escape
escaped_string=escape("your string goes here")

Operators supported

Operator type Operators
Arithmatic operators +, -, *, /, %
Comparison operators >, <, >=, <=, =, !=
Logical operators &, |
End of query statement # (Used internally)

Operator precedence (in order from high to low)

NOTE : Expression evaluation will use BODMAS as you studied in school. i.e., It has individual operator precedence unlike python or java.
Example : '9-7+1' will result '1'(That's how we were taught in school), where python eval() will return 3 as it treats + and - with equal precedence from left to right

You can use paranthesis to re-order precedence

Operator precedence
%
/
*
+
-
<,<=
>,>=
!=
=
&
|

File formats supported

Comma seperated value(.csv) files by default.
Can be extended to support any files with setdel() function. for example to process tab seperated files, use setdel("\t") before executing query("...")

Instructions

  • Tables are referred with '/' character from database like 'db/table'
  • String values should be within ''
  • Comparison operator '=' is used instead of '=='
  • Database,table names can contain only alphabet, number and underscore
  • Field names can contain only alphabets,number and underscore
  • All the exceptions are thrown to be handled by users
  • By default icecoal stores all its databases in current directory unless you prefix path with tablename

Licence

MIT Licence. Please raise a pull request to contribute.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

icecoal-1.0.5.tar.gz (18.7 kB view details)

Uploaded Source

Built Distribution

icecoal-1.0.5-py3-none-any.whl (17.5 kB view details)

Uploaded Python 3

File details

Details for the file icecoal-1.0.5.tar.gz.

File metadata

  • Download URL: icecoal-1.0.5.tar.gz
  • Upload date:
  • Size: 18.7 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.1.1 pkginfo/1.5.0.1 requests/2.23.0 setuptools/46.1.3 requests-toolbelt/0.9.1 tqdm/4.45.0 CPython/3.8.2

File hashes

Hashes for icecoal-1.0.5.tar.gz
Algorithm Hash digest
SHA256 e0da2883ceb3a8fbeb5373da58ecdd6d877d4576fd81117118585bdd98afab27
MD5 f7070f6c988db24f3aeeed6d940a059f
BLAKE2b-256 68a06aa81e9a45ef3d334327dbd4209b4b3e7ceb14531bc02a7e5154e51bbabc

See more details on using hashes here.

File details

Details for the file icecoal-1.0.5-py3-none-any.whl.

File metadata

  • Download URL: icecoal-1.0.5-py3-none-any.whl
  • Upload date:
  • Size: 17.5 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.1.1 pkginfo/1.5.0.1 requests/2.23.0 setuptools/46.1.3 requests-toolbelt/0.9.1 tqdm/4.45.0 CPython/3.8.2

File hashes

Hashes for icecoal-1.0.5-py3-none-any.whl
Algorithm Hash digest
SHA256 4c8cba623396a078f74a50bc946ade267b3cfbe43567b38008218fe7ae88a1e2
MD5 237715ad63191bd95842330fdf9bfaaa
BLAKE2b-256 88897c624bd73653a7c1f51863b0a5acf0c295e8b20ae3586e55cc858c7ac789

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page