python的轻量级数据库框架
pymedoo的Python项目详细描述
pymedoo-一个面向python的轻量级数据库框架。
它的灵感来自于php的Medoo和python的Records。
安装
pip install medoo
数据库所需的软件包
| Database | Package |
|---|---|
| sqlite | sqlite3 |
| mysql | pymysql |
| pgsql | psycopg2 |
| mssql | pymssql |
| oracle | cx_Oracle |
开始
选择
frommedooimportMedoo# 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"frommedooimportField,Rawme.select('Customers',Field('CustomerID')+1)# SELECT 'Name: ' || CustomerName AS name FROM "Customers"rs=me.select('Customers',Raw("'Name: ' || CustomerName AS name"))forrinrs: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
其中
单一条件
# SELECT * FROM "Customers" WHERE "CustomerID" = 1me.select('Customers',where={'CustomerID':1})# SELECT * FROM "Customers" WHERE "CustomerID" < 3me.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 3me.select('Customers',where={'CustomerID[<>]':(1,3)})# SELECT * FROM "Customers" WHERE NOT "CustomerID" BETWEEN 1 AND 3me.select('Customers',where={'!CustomerID[<>]':(1,3)})# SELECT * FROM "Customers" WHERE "CustomerID" IS NULLme.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 ONLYme.select('Customers',where={'ORDER':{'CustomerID':'desc','CustomerName':'asc'},'LIMIT':(2,1)})# SELECT COUNT("CustomerID") AS "c","CustomerName" FROM "Customers" GROUP BY "Country" HAVING "CustomerID" > 1me.select('Customers','CustomerID|count(c), CustomerName',where={'GROUP':'Country','HAVING':{'CustomerID[>]':1}})
使用子查询
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 tableme.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 tome.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))
记录
Medoo.select和Medoo.union返回一个记录集合，它基本上是一个生成器，但您仍然可以从中获取项，因为它将在必要时使用生成。这个想法是从Records中借用的。
records=me.select('Customers','CustomerID(id)')record=records.first()# <Record {'id': 1}># equivalent torecord=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 documentationprint(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])# 1print(record['id'])# 1print(record.id)# 1print(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 idprint(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 insertedme.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" = 2me.update('Orders',# tabledata={'CustomerID':10},where={'OrderID':2})# UPDATE "Orders" SET "CustomerID"="CustomerID"+1 WHERE "OrderID" = 2me.update('Orders',# tabledata={'CustomerID[+]':1},where={'OrderID':2})
删除
# DELETE FROM "Orders" WHERE "OrderID" = 2me.delete('Orders',where={'OrderID':2})
Medoo
的其他功能
# Fetch a single valueme.get('Customers','CustomerID',where={'CustomerName':'Around the Horn'})# == 1# Check if a record existsme.has('Customers',where={'CustomerID':10})# == False# Return the last queryme.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 errorsme.error()# Submit an SQL queryme.query(sql,commit=True)
延伸pymedoo
pymedoo是高度可扩展的，包括WHERE条件和UPDATE SET子句中的运算符、JOIN运算符以及一些函数，例如如何引用表名、字段名和值。所有这些都是用Dialect类定义的，您只需扩展这个类并将其指定给Medoo实例即可。
例如，让我们使用快捷方式定义一个不区分大小写的LIKE运算符~~：
frommedooimportMedoo,DialectclassMyDialect(Dialect):OPERATOR_MAP={'~~':'ilike'}@classmethoddefilike(klass,field,value):# support single valueifnotisinstance(value,list):value=[value]terms=["UPPER({}) LIKE UPPER({})".format(field,klass.value(v))# quote the valueforvinvalue]# use OR to connectreturn' OR '.join(terms)# tell medoo to use this dialectme=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 |
欢迎加入QQ群-->： 979659372
