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'))

# 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)

# 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})

# 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'
    }
})

# 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}
})

print(me.select('Orders').export('csv', delimiter = '\t'))

# 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')
})

# 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.

# 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 = 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 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 "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 FROM "Orders" WHERE "OrderID" = 2
me.delete('Orders', where = {'OrderID': 2})

# 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)

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'))