简单的python数据库编排实用程序,使添加表、插入、选择、更新、删除表项变得容易
pyql-db的Python项目详细描述
pyql公司
一个简单的ORM(对象关系映射),用于使用python访问、插入、更新和删除RBDMS表中的数据
安装
$ python3 -m venv env
$ source my-project/bin/activate
用PIP安装
^{pr2}$从Github下载并安装库:
(env)$ git clone https://github.com/codemation/pyql.git
使用安装脚本将pyql安装到激活的环境库中
(env)$ cd pyql; sudo ./install.py install
兼容数据库-目前
- mysql数据库
- sqlite公司
入门
DB连接
import sqlite3
from pyql import data
db = data.Database(
sqlite3.connect,
database="testdb"
)
from pyql import data
import mysql.connector
db = data.Database(
mysql.connector.connect,
database='mysql_database',
user='mysqluser',
password='my-secret-pw',
host='localhost',
type='mysql'
)
数据库中现有的表模式是在数据库对象实例化并准备立即使用时加载的。在
表格创建
需要至少2个项目元组的列表,最多3个
(“列名称”,类型,“修饰符”)
- column_name-str-数据库列名排除适用
- 类型:str、int、float、byte、bool、None-JSON可转储dicts属于str类型
- 非唯一的u修饰符
注:列选项可能存在一些差异,例如AUTOINCREMENT(sqlite)与AUTO_INCREMENT(mysql)- 参考数据库文档。在
注意:惟一约束不是由pyql验证的,而是在db验证的,所以如果支持修饰符,那么在创建表时会添加修饰符。在
# Table Create
db.create_table(
'stocks',
[
('order_num', int, 'AUTO_INCREMENT'),
('date', str),
('trans', str),
('symbol', str),
('qty', float),
('price', str)
],
'order_num' # Primary Key
)
mysql> describe stocks;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| order_num | int(11) | NO | PRI | NULL | auto_increment |
| date | text | YES | | NULL | |
| trans | text | YES | | NULL | |
| condition | text | YES | | NULL | |
| symbol | text | YES | | NULL | |
| qty | double | YES | | NULL | |
| price | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
使用外键创建表
db.create_table(
'departments',
[
('id', int, 'UNIQUE'),
('name', str)
],
'id' # Primary Key
)
db.create_table(
'positions',
[
('id', int, 'UNIQUE'),
('name', str),
('department_id', int)
],
'id', # Primary Key
foreign_keys={
'department_id': {
'table': 'departments',
'ref': 'id',
'mods': 'ON UPDATE CASCADE ON DELETE CASCADE'
}
}
)
db.create_table(
'employees',
[
('id', int, 'UNIQUE'),
('name', str),
('position_id', int)
],
'id', # Primary Key
foreign_keys={
'position_id': {
'table': 'positions',
'ref': 'id',
'mods': 'ON UPDATE CASCADE ON DELETE CASCADE'
}
}
)
插入数据
需要键值对-可以使用dict或以下命令输入
拆箱
# Note order_num is not required as auto_increment was specified
trade = {'date': '2006-01-05', 'trans': 'BUY', 'symbol': 'RHAT', 'qty': 100.0, 'price': 35.14}
db.tables['stocks'].insert(
**trade
)
query:
INSERT INTO
stocks (date, trans, symbol, qty, price)
VALUES ("2006-01-05", "BUY", "RHAT", 100, 35.14)
排队
# Note order_num is not required as auto_increment was specified
db.tables['stocks'].insert(
date='2006-01-05',
trans='BUY',
symbol='RHAT',
qty=200.0,
price=65.14
)
query:
INSERT INTO stocks (date, trans, symbol, qty, price) VALUES ("2006-01-05", "BUY", "RHAT", 200, 65.14)
插入特殊数据
- 在
string类型的列可以将JSON可转储python字典保存为JSON字符串,并在读取时自动转换回dict。在
在 - 在
嵌套dict也可以,但是所有的项都应该是JSON兼容的数据类型
在tx_data = { 'type': 'BUY', 'condition': { 'limit': '36.00', 'time': 'end_of_trading_day' } } trade = { 'order_num': 1, 'date': '2006-01-05', 'trans': tx_data, # 'symbol': 'RHAT', 'qty': 100, 'price': 35.14, 'after_hours': True } db.tables['stocks'].insert(**trade) query: INSERT INTO stocks (order_num, date, trans, symbol, qty, price, after_hours) VALUES (1, "2006-01-05", '{"type": "BUY", "condition": {"limit": "36.00", "time": "end_of_trading_day"}}', "RHAT", 100, 35.14, True) result: In: db.tables['stocks'][1]['trans']['condition'] Out: # {'limit': '36.00', 'time': 'end_of_trading_day'}
选择数据
基本用法:
表中的所有行和列
db.tables['employees'].select('*')
所有行和特定列
db.tables['employees'].select(
'id',
'name',
'position_id'
)
具有匹配值的所有行和特定列
db.tables['employees'].select(
'id',
'name',
'position_id',
where={'id': 1000}
)
具有多个匹配值的所有行和特定列
db.tables['employees'].select(
'id',
'name',
'position_id',
where={
'id': 1000,
'name': 'Frank Franklin'
}
)
高级用法:
雇员的所有行和列,合并表位置的所有行和列(如果外键匹配)
# Basic Join
db.tables['employees'].select(
'*',
join='positions'
)
query:
SELECT * FROM employees JOIN positions ON employees.position_id = positions.id
output:
[{
'employees.id': 1000, 'employees.name': 'Frank Franklin',
'employees.position_id': 100101, 'positions.name': 'Director',
'positions.department_id': 1001},
...
]
雇员的所有行和特定列,合并表位置的所有行和特定列(如果外键匹配)
# Basic Join
db.tables['employees'].select(
'employees.name',
'positions.name',
join='positions'
)
query:
SELECT
employees.name,
positions.name
FROM
employees
JOIN
positions
ON
employees.position_id = positions.id
output:
[
{'employees.name': 'Frank Franklin', 'positions.name': 'Director'},
{'employees.name': 'Eli Doe', 'positions.name': 'Manager'},
...
]
来自employees的所有行和特定列,将表位置的所有行和特定列(如果外键匹配)与匹配的职位名称'值
# Basic Join with conditions
db.tables['employees'].select(
'employees.name',
'positions.name',
join='positions', # Possible due to foreign key relationship
where={
'positions.name': 'Director'
}
)
query:
SELECT
employees.name,
positions.name
FROM
employees
JOIN positions ON
employees.position_id = positions.id
WHERE positions.name='Director'
output:
[
{'employees.name': 'Frank Franklin', 'positions.name': 'Director'},
{'employees.name': 'Elly Doe', 'positions.name': 'Director'},
..
]
来自员工的所有行和特定列,合并表positions&departments的特定行和特定列
注意:join='x\u table'只有在调用表具有对表“x\u table”的f-key引用时才有效
# Multi-table Join with conditions
db.tables['employees'].select(
'employees.name',
'positions.name',
'departments.name',
join={
'positions': {'employees.position_id': 'positions.id'},
'departments': {'positions.department_id': 'departments.id'}
},
where={'positions.name': 'Director'})
query:
SELECT
employees.name,
positions.name,
departments.name
FROM
employees
JOIN positions ON
employees.position_id = positions.id
JOIN departments ON
positions.department_id = departments.id
WHERE
positions.name='Director'
result:
[
{'employees.name': 'Frank Franklin', 'positions.name': 'Director', 'departments.name': 'HR'},
{'employees.name': 'Elly Doe', 'positions.name': 'Director', 'departments.name': 'Sales'}
]
特别注意:在执行多表联接时,必须明确提供联接列。键值顺序并不十分重要,但将确定返回行中出现的列名
join={'y_table': {'y_table.id': 'x_table.y_id'}}
result:
[
{'x_table.a': 'val1', 'y_table.id': 'val2'},
{'x_table.a': 'val1', 'y_table.id': 'val3'}
]
或者
join={'y_table': {'x_table.y_id': 'y_table.id'}}
result:
[
{'x_table.a': 'val1', 'x_table.y_id': 'val2'},
{'x_table.a': 'val1', 'x_table.y_id': 'val3'}
]
运算符语法
列表查询语法中支持以下运算符
“=”、“==”、“<;>”、“!=','>;','>;=','<;','<;=','喜欢','在','不在','不喜欢'
运算符语法需要列表列表,并支持多种组合条件
^{pr21}$示例:
find_employee = db.tables['employees'].select(
'id',
'name',
where=[
['name', 'like', '*ank*']
]
)
query:
SELECT id,name FROM employees WHERE name like '%ank%'
result:
[{'id': 1016, 'name': 'Frank Franklin'}, {'id': 1018, 'name': 'Joe Franklin'}, {'id': 1020, 'name': 'Frank Franklin'}, {'id': 1034, 'name': 'Dana Franklin'}, {'id': 1036, 'name': 'Jane Franklin'}, {'id': 1042, 'name': 'Frank Franklin'}, {'id': 1043, 'name': 'Eli Franklin'}, {'id': 1052, 'name': 'Eli Franklin'}, {'id': 1057, 'name': 'Eli Franklin'}]
delete_department = db.tables['departments'].delete(
where=[
['id', '<', 2000]
]
)
query:
DELETE
FROM
departments
WHERE
id < 2000
join_sel = db.tables['employees'].select(
'*',
join={
'positions': {
'employees.position_id':'positions.id',
'positions.id': 'employees.position_id'
}
},
where=[
[
'positions.name', 'not in', ['Manager', 'Intern', 'Rep']
],
[
'positions.department_id', '<>', 2001 # not equal
]
]
)
query:
SELECT
*
FROM
employees
JOIN
positions
ON
employees.position_id = positions.id
AND
positions.id = employees.position_id
WHERE
positions.name not in ('Manager', 'Intern', 'Rep')
AND
positions.department_id <> 2001
特殊示例:
括号索引只能用于主键并返回整行(如果存在)
db.tables['employees'][1000]
query:
SELECT * FROM employees WHERE id=1000
result:
{'id': 1000, 'name': 'Frank Franklin', 'position_id': 100101}
遍历表-抓取所有行-允许客户端筛选
for row in db.tables['employees']:
print(row['id], row['name'])
query:
SELECT * FROM employees
result:
1000 Frank Franklin
1001 Eli Doe
1002 Chris Smith
1003 Clara Carson
使用列表理解
sel = [(row['id'], row['name']) for row in db.tables['employees']]
query:
SELECT * FROM employees
result:
[
(1000, 'Frank Franklin'),
(1001, 'Eli Doe'),
(1002, 'Chris Smith'),
(1003, 'Clara Carson'),
...
]
更新数据
在线或取消打包定义更新值
db.tables['stocks'].update(symbol='NTAP',trans='SELL', where={'order_num': 1})
query:
UPDATE stocks SET symbol = 'NTAP', trans = 'SELL' WHERE order_num=1
联合国包装
#JSON capable Data
tx_data = {'type': 'BUY', 'condition': {'limit': '36.00', 'time': 'end_of_trading_day'}}
to_update = {'symbol': 'NTAP', 'trans': tx_data}
where = {'order_num': 1}
db.tables['stocks'].update(**to_update, where=where)
query:
UPDATE
stocks
SET
symbol = 'NTAP',
trans = '{"type": "BUY", "condition": {"limit": "36.00", "time": "end_of_trading_day"}}'
WHERE
order_num=1
Bracket assignment-在括号内为value假定主键名称
#JSON capable Data
tx_data = {'type': 'BUY', 'condition': {'limit': '36.00', 'time': 'end_of_trading_day'}}
to_update = {'symbol': 'NTAP', 'trans': tx_data, 'qty': 500}
db.tables['stocks'][2] = to_update
query:
# check that primary_key value 2 exists
SELECT
*
FROM
stocks
WHERE
order_num=2
# update
UPDATE
stocks
SET
symbol = 'NTAP',
trans = '{"type": "BUY", "condition": {"limit": "36.00", "time": "end_of_trading_day"}}',
qty = 500
WHERE
order_num=2
result:
db.tables['stocks'][2]
{
'order_num': 2,
'date': '2006-01-05',
'trans': {'type': 'BUY', 'condition': {'limit': '36.00', 'time': 'end_of_trading_day'}},
'symbol': 'NTAP',
'qty': 500,
'price': 35.16,
'after_hours': True
}
删除数据
db.tables['stocks'].delete(where={'order_num': 1})
其他
表已存在
'employees' in db
query:
show tables
result:
True
主键存在:
^{pr31}$- 项目
标签: