'''
https://www.encode.io/databases/
databases 为您提供了对一系列数据库的简单异步支持。

它使您可以使用功能强大的SQLAlchemy Core表达式语言进行查询，并提供对PostgreSQL，MySQL和SQLite的支持。

'''

# 安装
!pip install databases[mysql]

Collecting databases[mysql]
  Downloading databases-0.3.2-py3-none-any.whl (18 kB)
Requirement already satisfied: sqlalchemy in /Users/wxnacy/.pyenv/versions/3.7.6/Python.framework/Versions/3.7/lib/python3.7/site-packages (from databases[mysql]) (1.3.15)
Collecting aiomysql; extra == "mysql"
  Downloading aiomysql-0.0.20-py3-none-any.whl (40 kB)
     |████████████████████████████████| 40 kB 128 kB/s ta 0:00:01
Requirement already satisfied: pymysql; extra == "mysql" in /Users/wxnacy/.pyenv/versions/3.7.6/Python.framework/Versions/3.7/lib/python3.7/site-packages (from databases[mysql]) (0.9.3)
ERROR: aiomysql 0.0.20 has requirement PyMySQL<=0.9.2,>=0.9, but you'll have pymysql 0.9.3 which is incompatible.
Installing collected packages: aiomysql, databases
Successfully installed aiomysql-0.0.20 databases-0.3.2

# Create a database instance, and connect to it.
from databases import Database
import config
database = Database(config.DATABASE_URL)
await database.connect()

# Create a table.
query = """
DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), score INT);
"""
await database.execute(query=query)

0

# Insert some data.
query = "INSERT INTO test_user (name, score) VALUES (:name, :score)"
values = [
    {"name": "Daisy", "score": 92},
    {"name": "Neil", "score": 87},
    {"name": "Carol", "score": 43},
]
await database.execute_many(query=query, values=values)

# Run a database query.
query = "SELECT * FROM test_user"
await database.fetch_all(query=query)

[(1, 'Daisy', 92), (2, 'Neil', 87), (3, 'Carol', 43)]

# Fetch single row
query = "SELECT * FROM test_user WHERE id = :id"
await database.fetch_one(query=query, values={"id": 1})

(1, 'Daisy', 92)

# Close all connection in the connection pool
await database.disconnect()