dataset databases for lazy people

dataset

https://dataset.readthedocs.io/en/latest/index.html

https://github.com/pudo/dataset

让开发者像操作JSON一样，操作DB

dataset提供了一个简单的抽象层，开发者使用最简单的方法执行CRUD.

Although managing data in relational databases has plenty of benefits, they’re rarely used in day-to-day work with small to medium scale datasets. But why is that? Why do we see an awful lot of data stored in static files in CSV or JSON format, even though they are hard to query and update incrementally?

The answer is that programmers are lazy, and thus they tend to prefer the easiest solution they find. And in Python, a database isn’t the simplest solution for storing a bunch of structured data. This is what dataset is going to change!

dataset provides a simple abstraction layer that removes most direct SQL statements without the necessity for a full ORM model - essentially, databases can be used like a JSON file or NoSQL store.

A simple data loading script using dataset might look like this:

import dataset

db = dataset.connect('sqlite:///:memory:')

table = db['sometable']
table.insert(dict(name='John Doe', age=37))
table.insert(dict(name='Jane Doe', age=34, gender='female'))

john = table.find_one(name='John Doe')

特性：

• 根据schema自动化创建数据表
• 支持创建和更新
• 友好查询接口
• 基于SQLAlchemy，兼容多种数据库

Features

• Automatic schema: If a table or column is written that does not exist in the database, it will be created automatically.

• Upserts: Records are either created or updated, depending on whether an existing version can be found.

• Query helpers for simple queries such as all rows in a table or all distinct values across a set of columns.

• Compatibility: Being built on top of SQLAlchemy, dataset works with all major databases, such as SQLite, PostgreSQL and MySQL.

SQL语句操作

https://gist.github.com/gka/5296492

相比较SQL语句方法，需要用户自己拼凑复杂的SQL语句。

对于数据管理者是不友好的。

import sqlite3

# open connection and get a cursor
conn = sqlite3.connect(':memory:')
c = conn.cursor()

# create schema for a new table
c.execute('CREATE TABLE IF NOT EXISTS sometable (name, age INTEGER)')
conn.commit()

# insert a new row
c.execute('INSERT INTO sometable values (?, ?) ', ('John Doe', 37))
conn.commit()

# extend schema during runtime
c.execute('ALTER TABLE sometable ADD COLUMN gender TEXT')
conn.commit()

# add another row
c.execute('INSERT INTO sometable values (?, ?, ?) ', ('Jane Doe', 34, 'female'))
conn.commit()

# get a single row
c.execute('SELECT name, age FROM sometable WHERE name = ?', ('John Doe', ))
row = list(c)[0]
john = dict(name=row[0], age=row[1])

SQLAlchemy ORM

https://www.geeksforgeeks.org/sqlalchemy-orm-adding-objects/?ref=rp

create 使用 ORM实现，也是比较费力。

from sqlalchemy import Column, Integer, Boolean, String
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Declare Mapping
Base = declarative_base()


# This is the class which is mapped to "posts"
# table to our database
class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True, nullable=False)
    title = Column(String, nullable=False)
    content = Column(String, nullable=False)
    published = Column(Boolean, server_default='true', nullable=False)


# Syntax of database url = "<database_vendor_name>://
# <username>:<password>@ip-address/hostname/<database_name>"
DB_URL = "postgresql://anurag:anurag@localhost/gfg"

engine = create_engine(DB_URL)

local_session = sessionmaker(autoflush=False, autocommit=False, bind=engine)

# With this we get a session to do whatever we
# want to do
db = local_session()

# New post created by a user, assumes you get this
# from the frontend
post = Post(title="GFG Article",
            content="How to add SQL Alchemy objects", published=True)

db.add(post)
db.commit()

# After performing transaction, we should always close
# our connection to the database
db.close()

print("Successfully added a new post")

https://www.cnblogs.com/blueberry-mint/p/14277882.html

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from config import DB_URI

engine = create_engine(DB_URI)
Base = declarative_base(engine)  # SQLORM基类
session = sessionmaker(engine)()  # 构建session对象


class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    sex = Column(String(10))


Base.metadata.create_all()  # 将模型映射到数据库中

# 修改Tony的age为22
session.query(Student).filter(Student.name == 'Tony').update({'age': 22})
session.commit()

item = session.query(Student.name, Student.age).filter(Student.name == 'Tony').first()
print(item)