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 JDBC, ODBC, connection driver etc. and other technical jargons
- Those who want to conveniently process CSV files with SQL.
Github project
github.com/ppml38/icecoal
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 oprations
from icecoal import query
query("select name,age from travel_db/passengers where native='USA'")
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'")
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
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.
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 in github.com/ppml38/icecoal 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.