Skip to main content

告别SQL语句,python操作mysql的贴心助手

Project description

更新历史

  • v2版本

说明

  • 从此告别SQL语句,直接调用方法就完事
  • python3.10+
  • 持续更新中...

如何安装?

  • pip install sqlman

拿什么吸引你这个靓仔?

  • 使用方式简单暴力

  • 不用写SQL就能进行增删改查

连接方式是如此简易

  • 一个字典参数即可

插入数据是如此贴心

  • 自动推导

    • 传入dict是插入一条数据,传入list是插入多条数据
  • 多种插入模式

    • 模式1,插入时,数据冲突则报错
    • 模式2,插入时,数据冲突则忽略
    • 模式3,插入时,数据发生冲突,把数据进行更新操作
    • 模式4,插入时,自动过滤掉冲突的数据,只插入不冲突的数据

等等等等...

操练起来

连接mysql

  • 数据库对象
from sqlman import Connector

# 方式1
db = Connector(host="localhost", port=3306, username="root", password="root@0", db="test")  # 数据库对象

# 方式2
MYSQL_CONF = {
    'host': 'localhost',
    'port': 3306,
    'username': 'root',
    'password': 'root@0',
    'db': 'test'
}
db = Connector(**MYSQL_CONF)  # 数据库对象

# 方式3
MYSQL_URL = "mysql://root:root@0@localhost:3306/test"
db = Connector.from_url(MYSQL_URL)  # 数据库对象
  • 表格对象
student = db['student']
student = db.pick_table('student')

准备测试数据

# 一条龙服务,创建people表并插入测试数据,每次插入一千条,累计插入一万条
db.gen_test_table('people', once=1000, total=10000)
people = db['people']

插入数据

单条插入

data = {'id': 10001, 'name': '小明', 'age': 10, 'gender': '男'}

# 插入一条数据
people.insert_data(data)

# 当插入的数据与表中的数据存在冲突时,直接插入会报错,如果补充<unique>参数,则不报错
people.insert_data(data, unique='id')

批量插入

data = [
    {'id': 10002, 'name': '小红', 'age': 12, 'gender': '女'},
    {'id': 10003, 'name': '小强', 'age': 13, 'gender': '男'},
    {'id': 10004, 'name': '小白', 'age': 14, 'gender': '男'}
]

# 插入多条数据
people.insert_data(data)

插入数据时,如果数据冲突则进行更新

data = {'id': 10001, 'name': '小明', 'age': 10, 'gender': '男'}

# 当数据冲突时,也可以直接进行更新操作,下面是把age更新为11
people.insert_data(data, update='age=age+1')

删除数据

# delete from people where id=1
people.delete(id=1)

# delete from people where id in (1, 2, 3)
people.delete(id=[1, 2, 3])

# delete from people where age=18 limit 100
people.delete(age=18, limit=100)

更新数据

# update people set name='tony', job='理发师' where id=1
people.update(new={'name': 'tony', 'job': '理发师'}, id=1)

# update people set job='程序员' where name='thomas' and phone='18959176772'
people.update(new={'job': '程序员'}, name='thomas', phone='18959176772')

查询数据

# select * from people where id=1
people.query(id=1)

# select name, age from people where id=2
people.query(pick='name, age', id=2)

# select * from people where age=18 and gender in ('男', '女')
people.query(age=18, gender=['男', '女'])

# select name from people where age=18 and gender in ('男', '女') limit 5
people.query(pick='name', age=18, gender=['男', '女'], limit=5)

随机数据

# 随机返回1条数据<dict>
print(people.random())

# 随机返回5条数据<list>
print(people.random(limit=5))

遍历表

# 遍历整张表,默认每轮扫描1000条,默认只打印数据
people.scan()


def show(lines):
    for some in enumerate(lines, start=1):
        print('第{}{}'.format(*some))


# 限制id范围为101~222,每轮扫描100条,每轮的回调函数为show
people.scan(sort_field='id', start=101, end=222, once=100, dealer=show)

# 限制id范围的基础上,限制age=18
people.scan(sort_field='id', start=101, end=222, once=100, dealer=show, add_cond='age=18')

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

sqlman-0.4.6.tar.gz (13.3 kB view hashes)

Uploaded Source

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page