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

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

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

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

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}

for table_name in db: print(table_name)