7Rd2d/sqlmapper

sqlmapper - wrapper for sql

Sqlmapper

Easy wrapper for SQL

• Supports Python 2.x, 3.x, MySQL, PostgreSQL, SQLite, asyncio + mysql
• Thread-safe (you can use the same connection from different threads)
• License MIT

Install and update using pip

pip install -U sqlmapper

Examples

db = Connection(db='example')

db.tblname.insert({'name': 'Ubuntu', 'value': 14})
# INSERT INTO `tblname` (`name`, `value`) VALUES ('Ubuntu', 14)

db.tblname.insert({'name': 'MacOS', 'value': 10})
# INSERT INTO `tblname` (`name`, `value`) VALUES ('MacOS', 10)

for d in db.tblname.find({'name': 'Ubuntu'}):
    # SELECT tblname.* FROM `tblname` WHERE `tblname`.`name`='Ubuntu'
    print(d)

db.tblname.update({'name': 'Ubuntu'}, {'value': 16})
# UPDATE `tblname` SET `value` = 16 WHERE `tblname`.`name`='Ubuntu'

db.tblname.find_one({'Name': 'Ubuntu'})
# SELECT tblname.* FROM `tblname` WHERE `name` = 'Ubuntu' LIMIT 1

db.tblname.find_one(2)
# find by primary key
# SELECT tblname.* FROM `tblname` WHERE `id` = 2 LIMIT 1

db.tblname.delete({'name': 'MacOS'})
# DELETE FROM `tblname` WHERE `tblname`.`name`='MacOS'

db.commit()

asyncio

from sqlmapper.aio import Connection

db = await Connection(db='example')

await db.book.add_column('value', 'int', exist_ok=True)
await db.book.insert({'name': 'ubuntu', 'value': 16})
await db.commit()
d = await db.book.find_one(1)
print(d)

await db.book.update(1, {'value': 18})
print(await db.book.count())

Change schema

# a table is created for first column
db.tblname.add_column('id', 'INT(11)', primary=True, auto_increment=True, exist_ok=True)
# CREATE TABLE `tblname` (`id` INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci

db.tblname.add_column('name', 'VARCHAR(32)', exist_ok=True)
# ALTER TABLE `tblname` ADD COLUMN `name` VARCHAR(32)

db.tblname.add_column('value', 'INT(11)', exist_ok=True)
# ALTER TABLE `tblname` ADD COLUMN `value` INT(11)

db.tblname.create_index('name_idx', ['name'], exist_ok=True)
# ALTER TABLE `tblname` ADD INDEX `name_idx`(`name`)

Join

for d in db.parent.find({'name': 'Linux'}, join='child.id=child_id'):
    # SELECT parent.*, "" as __divider, child.* FROM `parent` JOIN child AS child ON child.id = child_id WHERE `parent`.`name`='Linux'
    print(d)
    # d == {
    #   'name': 'Linux',
    #   'child_id': 5,
    #   'child': {
    #       'id': 5,
    #       'name': 'Ubuntu'
    #   }
    # }

Group by

for d in db.tblname.find(group_by='name', columns=['name', 'SUM(value)']):
    # SELECT `name`, SUM(`value`) as `sum_value` FROM `tblname` GROUP BY `name`
    print(d)  # {'name': u'Ubuntu', 'sum_value': 32}

List of tables

for table_name in db:
    print(table_name)