firstprayer / monsql Goto Github PK

MonSQL - Light-weighted, MongoDB-style Wrapper for multiple Relational Databases

License: MIT License

Makefile 10.65% Shell 10.10% Python 79.25%

monsql's Introduction

MonSQL - Light-weighted, MongoDB-style Wrapper for multiple Relational Databases

A light-weighted wrapper for easy interaction with Relational Databases using a mongodb-like interface. It's goal is to be easy to use, even for a complete novince! What's more, it supports seamless switching between different relational databases

Currently supported database

MySQL
SQLite3
PostgreSQL

More documentation can be found here

Usage:

db = monsql.MonSQL(host, port, username, password, dbname, monsql.DB_TYPES.MYSQL)

Now assume you have a table called image. It works like this:

image_tb = db.get('image')
img_cnt  = image_tb.count() # total number of rows
img_cnt  = image_tb.count(distinct=True) # total number of distinct rows

select:

result = image_tb.find({"id": 1}, fields=('id', 'size'))  
result_count = len(result) 

for row in result:
	print row.id, row.size, ...

Complex query operators Complex queries can be formed using complex operators:

{a: 1}                              -> a == 1
{a: {$gt: 1}}                       -> a > 1
{a: {$gte: 1}}                      -> a >= 1
{a: {$lt: 1}}                       -> a < 1
{a: {$lte: 1}}                      -> a <= 1
{a: {$eq: 1}}                       -> a == 1
{a: {$in: [1, 2]}}                  -> a == 1
{a: {$contains: '123'}}             -> a like %123%

{$not: condition}                   -> !(condition)
{$and: [condition1, condition2, ...]}    -> condition1 and condition2
{$or: [condition1, condition2, ...]}     -> condition1 or condition2

insert

image_id = image_tb.insert({"name": "xxx"})

update

update_count = image_tb.update({"name": "xxx"}, {"name": "xxxx"})

delete

removed_count = image_tb.remove({"id": 2})

See? It's just that easy.

Update: now support create/drop operations:

db.create_table('test_table', [('id INT NOT NULL', 'name VARCHAR(50)')])
db.truncate_table('test_table')
db.drop_table('test_table')

Contribites:

TODO:

Pending functionalities include but not limited to:

Add using parameter to Table.remove. This parameter corresponds to the USING clause in DELETE FROM A USING ... WHERE ...

More info in the issue page

Tests

Running tests from Travis CI:

The project is integrated with Travis CI. All required configurations have been placed in .travis.yml. Therefore, once you have forked the repository, all you need to do is simply activate a Github webhook. See Getting Started guide for Travis CI.

Running tests locally:

To run tests locally requires you to manual configure some variables. These configurations values should be placed in tests/config.yml file. Simply copy tests/config.yml.orig to tests/config.yml and modify it to reflect correct values.

license:

The MIT License (MIT)

monsql's People

Contributors

Stargazers

Watchers

Forkers

lowks gochomugo

monsql's Issues

SQLite3 Support

Is it possible to support SQLite3? I would propose to implement different wrappers for different SQL versions.

For example, have a connection object instantiated separately for either SQLite3 or MySQL. Then bind it to a more general wrapper for operations on the database and tables.

What do you think?

Add support for PostgreSQL

Request for Code Commenting and Elaboration

I have been trying to understand code in https://github.com/firstprayer/monsql/blob/develop/monsql/query.py but it seems evasive at the moment. Is it possible you comment it better? 🙏

Add test case for query set filter and limit

Add test case for QuerySet.filter and QuerySet.limit

Support MapReduce

My idea is like this:

add a method mapreduce to the Table class:

def mapreduce(self, map_keys, reduce, prefilter=None, postfilter=None):
    """
    map_keys: a list or tuple of column names
    reduce: a dictionary of {name -> expression}. For example: {'people_num': {'$count': 'id'}}
    prefilter: filter out rows before map phase
    postfilter: filter out results after reduce phase
    return: a list of RowData
    """
    ...

Add distinct to find and find_one

Add using parameter to Table.remove

Add using parameter to Table.remove. This parameter corresponds to the USING clause in DELETE FROM A USING ... WHERE ...

Specify MySQL Database Driver

Which MySQL driver are we supposed to use? There are too many python drivers out there, including this search on pypi.

Add filter parameter to Table.count

Add filter parameter to Table.count. The filter parameter corresponds to the WHERE clause in SELECT COUNT() FROM ... WHERE

Improved Testing

There is an issue with running these tests. The username and password are hard-coded into the test script. This means that if anyone wants to run the tests, they would have do one of the following:

hard-code their credentials into the test script
create a user with same credentials as specified in the test-script

None of this is easy considering changing the test script would mean entirely different changes among collaborators. Also creating a user may not be possible, say on a CI server.

I suggest we use this approach: allow user running the test to create a credentials file, say named .credentials.yml that will be holds the username and password and that will be added to .gitignore to avoid checking it into git. If the user does not have such a file, particularly CI servers, we try use environment variables instead.

Add support for complex operator: $startswith, $endswith

Currently we support $contains, which is represented as LIKE '%pattern%'.

We should also support $startswith: LIKE 'pattern%'
and $endswith: '%pattern'

firstprayer / monsql Goto Github PK

monsql's Introduction

MonSQL - Light-weighted, MongoDB-style Wrapper for multiple Relational Databases

Contribites:

TODO:

Tests

Running tests from Travis CI:

Running tests locally:

license:

monsql's People

Contributors

Stargazers

Watchers

Forkers

monsql's Issues

Recommend Projects

Recommend Topics

Recommend Org