Introduction

Do you guys use ORM? The ORM in Python is as follows.

Not limited to these, if you want to use ORM, you need to define a model, It's quite difficult to write a script for the DB of an existing project and use it quickly.

Therefore, I would like to introduce an OR mapper dataset that can be used quickly without having to write a troublesome model definition.

pudo/dataset

No model definition is required, so even projects with Hibernate ORM in Java and Doctrine in PHP It's easy to deploy.

SQLAlchemy is used inside the dataset.

SELECT

import dataset

db = dataset.connect('mysql://[email protected]/information_schema')

table = db['COLLATIONS']
results = table.find(CHARACTER_SET_NAME='utf8', order_by='-ID', _limit=3)
for r in results:
    print(r)
    # OrderedDict([('COLLATION_NAME', 'utf8_general_mysql500_ci'), ('CHARACTER_SET_NAME', 'utf8'), ('ID', 223L), ('IS_DEFAULT', ''), ('IS_COMPILED', 'Yes'), ('SORTLEN', 1L)])
    # OrderedDict([('COLLATION_NAME', 'utf8_vietnamese_ci'), ('CHARACTER_SET_NAME', 'utf8'), ('ID', 215L), ('IS_DEFAULT', ''), ('IS_COMPILED', 'Yes'), ('SORTLEN', 8L)])
    # OrderedDict([('COLLATION_NAME', 'utf8_unicode_520_ci'), ('CHARACTER_SET_NAME', 'utf8'), ('ID', 214L), ('IS_DEFAULT', ''), ('IS_COMPILED', 'Yes'), ('SORTLEN', 8L)])

SQL In the above example, the following SQL will be executed


SELECT `COLLATIONS`.`COLLATION_NAME`, `COLLATIONS`.`CHARACTER_SET_NAME`, `COLLATIONS`.`ID`, `COLLATIONS`.`IS_DEFAULT`, `COLLATIONS`.`IS_COMPILED`, `COLLATIONS`.`SORTLEN` 
FROM `COLLATIONS` 
WHERE `COLLATIONS`.`CHARACTER_SET_NAME` = 'utf8' ORDER BY `COLLATIONS`.`ID` DESC 
 LIMIT 0, 3

Limits

Only the WHERE clause in the equal condition is supported, so when using SQL functions such as the LIKE condition and count, [SQLAlchemy notation](http://docs.sqlalchemy.org/en/latest/core/tutorial. Use db.query () in html # selecting).

INSERT

First create an empty DB.

$ mysqladmin create sandbox

Pass the data to be INSERTed in dictionary format.

import dataset

db = dataset.connect('mysql://[email protected]/sandbox')

table = db['Alchemist']

table.insert(dict(name='Alphonse Elric'))
table.insert_many([dict(name='Edward Elric', titled='Fullmetal'), dict(name='Roy Mustang', titled='Flame')])

This will create 3 records.

SQL

Let's take a look at the issued SQL.

SHOW FULL TABLES FROM `sandbox`
rollback
--Check if there is a table
DESCRIBE `Alchemist`
rollback
rollback
--Create table
CREATE TABLE `Alchemist` (
        id INTEGER NOT NULL AUTO_INCREMENT, 
        PRIMARY KEY (id)
)
commit
rollback
--Add column
ALTER TABLE `Alchemist` ADD COLUMN name TEXT
commit
rollback
SHOW FULL TABLES FROM `sandbox`
--Check the table
SHOW CREATE TABLE `Alchemist`
rollback
-- table.insert(dict(name='Alphonse Elric'))
INSERT INTO `Alchemist` (name) VALUES ('Alphonse Elric')
commit
rollback
--Add column
ALTER TABLE `Alchemist` ADD COLUMN titled TEXT
commit
rollback
SHOW FULL TABLES FROM `sandbox`
SHOW CREATE TABLE `Alchemist`
rollback
-- table.insert_many([dict(name='Edward Elric', titled='Fullmetal'), dict(name='Roy Mustang', titled='Flame')])
INSERT INTO `Alchemist` (name, titled) VALUES 
('Edward Elric', 'Fullmetal'),
('Roy Mustang', 'Flame')
commit

ʻIf there is no table or column to INSERT`, it will be created. How easy ~~ scary ~~! To be honest, I can't recommend it for use in a production environment.

Other

The character logo on the official website is cute. dataset: databases for lazy people — dataset 0.6.0 documentation

You can also write the SQL result in json format. Export DB data in json format --Qiita

[PYTHON] Super easy OR mapper dataset for lazy people

Introduction

Limits

Other