Skip to content

pwwang/pymedoo

BranchesTags

Repository files navigation

pymedoo - A lightweight database framework for python

it's inspired by Medoo for PHP and Records for python.

Pypi Github Codacy Codacy coverage Building

Install

pip install medoo

Required packages for databases

Database Package Install
sqlite sqlite3 pip install medoo[sqlite]
mysql pymysql(dropped) Use mysql.connector instead. See #6 pip install medoo[mysql]
pgsql psycopg2 pip install medoo[pgsql]
mssql pymssql pip install medoo[mssql]
oracle cx_Oracle pip install medoo[oracle]

Install forr all supported databases:

pip install medoo[all]

Get started

SELECT

from medoo import Medoo

# For other arguments, please refer to the original connect function of each client.
me = Medoo(dbtype = 'sqlite', database = 'file:///path/to/test.sqlite')

# SELECT * FROM "Customers"
rs = me.select('Customers')

print(rs.export('csv', delimiter = '\t'))
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 5021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 5023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden 
# SELECT "CustomerID","CustomerName" FROM "Customers"
me.select('Customers', 'CustomerID, CustomerName')
me.select('Customers', ['CustomerID', 'CustomerName'])

# SELECT "C"."CustomerID" AS "CustomerID","C"."CustomerName" AS "name" FROM "Customers" AS "C"
me.select('Customers(C)', ['C.CustomerID(id)', 'C.CustomerName(name)'])

# SELECT DISTINCT "Country" FROM "Customers"
me.select('Customers', 'Country', distinct = True)

# SELECT COUNT("CustomerID") FROM "Customers"
me.select('Customers', 'CustomerID|COUNT')

# SELECT COUNT(DISTINCT "CustomerID") AS "c" FROM "Customers"
me.select('Customers', 'CustomerID|.COUNT(c)')

# SELECT "CustomerID"+1 FROM "Customers"
from medoo import Field, Raw
me.select('Customers', Field('CustomerID')+1)

# SELECT 'Name: ' || CustomerName AS name FROM "Customers"
rs = me.select('Customers', Raw("'Name: ' || CustomerName AS name"))
for r in rs: print(r.name)
Name: Alfreds Futterkiste
Name: Ana Trujillo Emparedados y helados
Name: Antonio Moreno Taquería
Name: Around the Horn
Name: Berglunds snabbköp

WHERE

Single condition

# SELECT * FROM "Customers" WHERE "CustomerID" = 1
me.select('Customers', where = {'CustomerID': 1})

# SELECT * FROM "Customers" WHERE "CustomerID" < 3
me.select('Customers', where = {'CustomerID[<]': 3})

# SELECT * FROM "Customers" WHERE "CustomerID" IN (1,2,3)
me.select('Customers', where = {'CustomerID': (1,2,3)})

# SELECT * FROM "Customers" WHERE "CustomerName" LIKE '%b%' OR "CustomerName" LIKE '%c%'
me.select('Customers', where = {'CustomerName[~]': ('a', 'b')})

# SELECT * FROM "Customers" WHERE "CustomerID" BETWEEN 1 AND 3
me.select('Customers', where = {'CustomerID[<>]': (1,3)})

# SELECT * FROM "Customers" WHERE NOT "CustomerID" BETWEEN 1 AND 3
me.select('Customers', where = {'!CustomerID[<>]': (1,3)})

# SELECT * FROM "Customers" WHERE "CustomerID" IS NULL
me.select('Customers', where = {'CustomerID[is]': None}) # where = {'id[==]': None}

# SELECT * FROM "Customers" WHERE INSTR("CustomerName", 'Antonio')
me.select('Customers', where = {Raw('INSTR("CustomerName", \'Antonio\')'):None})

Compond

# SELECT * FROM "Customers" WHERE "CustomerID" IN (1,2,3) AND "CustomerName" LIKE '%b%'
me.select('Customers', where = {
    'CustomerID': (1,2,3),
    'CustomerName[~]': 'b'
})
# SELECT * FROM "Customers"
# WHERE ("CustomerID" IN (1,2,3) AND "CustomerName" LIKE '%b%') AND
# ("CustomerName" = 'cd' OR "CustomerID" = 2) AND
# ("CustomerID" < 3 AND NOT "CustomerName" = 'bc')
me.select('Customers', where = {
    'AND': {
        'CustomerID': (1,2,3),
        'CustomerName[~]': 'b'
    },
    'OR': {
        'CustomerName': 'cd',
        'CustomerID': 2
    },
    # you can use comment to distinguish multiple ANDs and ORs
    'AND #2': {
        'CustomerID[<]': 3,
        '!CustomerName': 'bc'
    }
})

Modifier

# SELECT * FROM "Customers" ORDER BY "CustomerID" DESC, "CustomerName" ASC LIMIT 2 OFFSET 1
# MSSQL:
# SELECT * FROM "Customers" ORDER BY "CustomerID" DESC, "CustomerName" ASC
# OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
me.select('Customers', where = {
    'ORDER': {'CustomerID': 'desc', 'CustomerName': 'asc'},
    'LIMIT': (2, 1)
})

# SELECT COUNT("CustomerID") AS "c","CustomerName" FROM "Customers" GROUP BY "Country" HAVING "CustomerID" > 1
me.select('Customers', 'CustomerID|count(c), CustomerName', where = {
    'GROUP': 'Country',
    'HAVING': {'CustomerID[>]': 1}
})

Using subquery

print(me.select('Orders').export('csv', delimiter = '\t'))
OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20 
# SELECT * FROM "Customers" AS "C",(SELECT "CustomerID" FROM "Orders") AS "O"
#   WHERE "C"."CustomerID" = "O"."CustomerID"
me.select([
    'Customers(C)', # the first table
    me.builder.select('Orders', 'CustomerID', sub = 'O')
], where = {
    'C.CustomerID': Field('O.CustomerID')
})

# SELECT * FROM "Customers" WHERE "CustomerID" IN (SELECT "CustomerID" FROM "Orders")
me.select('Customers', where = {
    'CustomerID': me.builder.select('Orders', 'CustomerID')
})

JOIN

# SELECT "O"."OrderID","C"."CustomerName","O"."OrderDate" FROM "Orders" AS "O"
#   INNER JOIN "Customers" AS "C" ON "C"."CustomerID"="O"."CustomerID"
me.select('Orders(O)', 'O.OrderID,C.CustomerName,O.OrderDate', join = {
    'Customers(C)': 'CustomerID'
})

# equivalent to
me.select('Orders(O)', 'O.OrderID,C.CustomerName,O.OrderDate', join = {
    '[><]Customers(C)': 'CustomerID'
})
# [>] LEFT JOIN, [<] RIGHT JOIN [<>] FULL OUTER JOIN

# Join on multiple columns (same in different tables)
# join = { '[><]Customers(C)': ['CustomerID', 'OtherColumn'] }

# Join on different columns: JOIN "Customers" AS "C" ON "C"."CustomerID"="O"."OtherID"
# join = { '[><]Customers(C)': {'CustomerID', 'OtherID'} }

# You can join multiple tables, use OrderedDict if you want to keep the order.

UNION

# SELECT "CustomerID" FROM "Customers" UNION SELECT "CustomerID" FROM "Orders"
me.union(
    me.builder.select('Customers', 'CustomerID'),
    me.builder.select('Orders', 'CustomerID')
)

# SELECT "CustomerID" FROM "Customers" UNION ALL SELECT "CustomerID" FROM "Orders"
me.union(
    me.builder.select('Customers', 'CustomerID'),
    me.builder.select('Orders', 'CustomerID', sub = True)
)

Records

Medoo.select and Medoo.union return a collection of records, which is basically a generator, but you can still get items from it, as it will consume the generate if necessary. The idea is borrowed from Records.

records = me.select('Customers', 'CustomerID(id)')
record  = records.first() # <Record {'id': 1}>

# equivalent to
record  = records[0]

# you may also select other rows: records[1], records[2]
# or return all rows:
print(records.all())

# you can also export the records
# this is the courtesy from tablib (https://github.com/kennethreitz/tablib)
# check the kwargs with its documentation
print(records.export('csv', delimiter = '\t'))

# You can also apply tablib's other function on the data:
# records.tldata.<function>(<args>)

# to get the value of each field from a record:
print(record[0]) # 1
print(record['id']) # 1
print(record.id) # 1
print(record.as_dict()) # {'id': 1}

INSERT

# INSERT INTO "Orders" ("OrderID","CustomerID","OrderDate") VALUES (1,2,'1999-09-09'),(2,8,'2001-10-12')
me.insert(
    'Orders', # table
    'OrderID, CustomerID, OrderDate', # fields
    (1,2,'1999-09-09'), # values
    (2,8,'2001-10-12')
    # ...
)
# get the last insert row id
print(me.id()) # 5

# INSERT INTO "Orders" ("OrderID","CustomerID","OrderDate") VALUES (1,2,'1999-09-09'),(2,8,'2001-10,12')
me.insert(
    'Orders', # table
    {'OrderID': 1, 'CustomerID': 2, 'OrderDate': '1999-09-09'}, # fields with the first value
    (2,8,'2001-10-12')
    # ...
)
me.insert(
    'Orders', # table
    {'OrderID': 1, 'CustomerID': 2, 'OrderDate': '1999-09-09'}, # fields with the first value
    {'OrderID': 2, 'CustomerID': 8, 'OrderDate': '2001-10-12'}  # specify the fields as well
    # ...
)
# Or if your values have all the fields
# INSERT INTO "Orders" VALUES (1,2,'1999-09-09'),(2,8,'2001-10-12')
me.insert(
    'Orders', # table
    (1,2,'1999-09-09')
    (2,8,'2001-10-12')
    # ...
)

# You may hold the changes until all data inserted
me.insert(..., commit = False)
me.insert(..., commit = False)
me.insert(..., commit = False)
me.insert(..., commit = False)
me.commit()
# This applies with UPDATE and DELETE as well.

UPDATE

# UPDATE "Orders" SET "CustomerID"=10 WHERE "OrderID" = 2
me.update(
    'Orders', # table
    data  = {'CustomerID': 10},
    where = {'OrderID': 2}
)
# UPDATE "Orders" SET "CustomerID"="CustomerID"+1 WHERE "OrderID" = 2
me.update(
    'Orders', # table
    data  = {'CustomerID[+]': 1},
    where = {'OrderID': 2}
)

DELETE

# DELETE FROM "Orders" WHERE "OrderID" = 2
me.delete('Orders', where = {'OrderID': 2})

Other functions of Medoo

# Fetch a single value
me.get('Customers', 'CustomerID', where = {'CustomerName': 'Around the Horn'}) # == 1

# Check if a record exists
me.has('Customers', where = {'CustomerID': 10}) # == False

# Return the last query
me.last() # SELECT * FROM "Customers" WHERE "CustomerID" = 10

# Show all the queries bound with `me`

# You have to passing `logging = True` to `Medoo(..., logging = True)`
me.log()

# Return the errors
me.error()

# Submit an SQL query
me.query(sql, commit = True)

Extending pymedoo

pymedoo is highly extendable, including the operators in WHERE conditions and UPDATE SET clause, JOIN operators, and some functions such as how to quote the table names, field names and values. All of these have been defined with Dialect class, what you need to do is just extend this class and specify it to the Medoo instance. For example, let's define a case-insensitive LIKE operator using a shortcut ~~:

from medoo import Medoo, Dialect

class MyDialect(Dialect):
    OPERATOR_MAP = {
        '~~': 'ilike'
    }

    @classmethod
    def ilike(klass, field, value):
        # support single value
        if not isinstance(value, list):
            value = [value]

        terms = [
            "UPPER({}) LIKE UPPER({})".format(field, klass.value(v)) # quote the value
            for v in value
        ]
        # use OR to connect
        return ' OR '.join(terms)

# tell medoo to use this dialect
me = Medoo(...)
me.dialect(MyDialect)

# SELECT * FROM "Customers" WHERE UPPER("CustomerName") LIKE UPPER('%an%')
records = me.select('Customers', where = {
    'CustomerName[~~]': '%an%'
})
print(records.export('csv', delimiter = '\t'))
CustomerID CustomerName ContactName Address City PostalCode Country
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 5021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 5023 Mexico

About

A lightweight database framework for python

Topics

python database-framework lightweight-database-framework

Resources

Readme

License

MIT license
Activity

Stars

15 stars

Watchers

6 watching

Forks

4 forks
Report repository

Releases 3

0.1.2 Latest
Nov 11, 2022
+ 2 releases

Packages

No packages published

Contributors 2

  •  
  •  

Languages