In [4]:
pip install databases

Collecting databases
  Using cached databases-0.3.2-py3-none-any.whl (18 kB)
Requirement already satisfied: sqlalchemy in /Users/wxnacy/.pyenv/versions/3.7.6/envs/notebook/lib/python3.7/site-packages (from databases) (1.3.17)
Installing collected packages: databases
Successfully installed databases-0.3.2
Note: you may need to restart the kernel to use updated packages.
In [1]:
# 使用 sqlalchemy 进行数据库操作
import sqlalchemy
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import Boolean
import config


metadata = sqlalchemy.MetaData()

user = sqlalchemy.Table(
    "test_user",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(length=100)),
    Column("score", Integer),
)
In [2]:
# Create a database instance, and connect to it.
from databases import Database
database = Database(config.DATABASE_URL)
await database.connect()
In [3]:
# Execute
query = user.insert()
values = {"name": "wxnacy", "score": 100}
await database.execute(query=query, values=values)
Out[3]:
20
In [15]:
# Execute many
query = user.insert()
values = [
    {"name": "wxnacy", "score": 100},
    {"name": "wen", "score": 100}
]
await database.execute_many(query=query, values=values)
In [27]:
# Fetch multiple rows
query = user.select()
rows = await database.fetch_all(query=query)
rows, type(rows)
Out[27]:
([(1, 'Daisy', 92),
  (2, 'Neil', 87),
  (3, 'Carol', 43),
  (4, 'wxnacy', 100),
  (5, 'wxnacy', 100),
  (6, 'wen', 100)],
 list)
In [29]:
# Fetch single row
query = user.select()
row = await database.fetch_one(query=query)
row, type(row), row.id
Out[29]:
((1, 'Daisy', 92), sqlalchemy.engine.result.RowProxy, 1)
In [20]:
# Fetch single value, defaults to `column=0`.
query = user.select()
await database.fetch_val(query=query)
Out[20]:
1
In [22]:
# Fetch multiple rows without loading them all into memory at once
query = user.select()
async for row in database.iterate(query=query):
    print(row)

(1, 'Daisy', 92)
(2, 'Neil', 87)
(3, 'Carol', 43)
(4, 'wxnacy', 100)
(5, 'wxnacy', 100)
(6, 'wen', 100)
In [7]:
help(user.select)

Help on method select in module sqlalchemy.sql.selectable:

select(whereclause=None, **params) method of sqlalchemy.sql.schema.Table instance
    return a SELECT of this :class:`.FromClause`.
    
    .. seealso::
    
        :func:`~.sql.expression.select` - general purpose
        method which allows for arbitrary column lists.
In [14]:
from sqlalchemy import text
query = user.select(text("id = 1"))
await database.fetch_all(query=query)
Out[14]:
[(1, 'Daisy', 92)]
In [19]:
help(user.select().where)

query = user.select().where(text("id = 1"))
await database.fetch_all(query=query)

Help on method where in module sqlalchemy.sql.selectable:

where(whereclause) method of sqlalchemy.sql.selectable.Select instance
    return a new select() construct with the given expression added to
    its WHERE clause, joined to the existing clause via AND, if any.
Out[19]:
[(1, 'Daisy', 92)]
In [3]:
# 查看 user 属性
dir(user)
Out[3]:
['__and__',
 '__bool__',
 '__class__',
 '__delattr__',
 '__dict__',
 '__dir__',
 '__doc__',
 '__eq__',
 '__format__',
 '__ge__',
 '__getattribute__',
 '__getstate__',
 '__gt__',
 '__hash__',
 '__init__',
 '__init_subclass__',
 '__invert__',
 '__le__',
 '__lt__',
 '__module__',
 '__ne__',
 '__new__',
 '__nonzero__',
 '__or__',
 '__reduce__',
 '__reduce_ex__',
 '__repr__',
 '__setattr__',
 '__sizeof__',
 '__str__',
 '__subclasshook__',
 '__visit_name__',
 '__weakref__',
 '_annotate',
 '_annotations',
 '_autoincrement_column',
 '_autoload',
 '_clone',
 '_cloned_set',
 '_cols_populated',
 '_columns',
 '_compiler',
 '_compiler_dispatch',
 '_constructor',
 '_copy_internals',
 '_deannotate',
 '_execute_on_connection',
 '_extra_dependencies',
 '_extra_kwargs',
 '_from_objects',
 '_hide_froms',
 '_init',
 '_init_collections',
 '_init_existing',
 '_init_items',
 '_is_clone_of',
 '_is_from_container',
 '_is_join',
 '_is_lateral',
 '_is_lexical_equivalent',
 '_is_select',
 '_kw_reg_for_dialect',
 '_kw_reg_for_dialect_cls',
 '_kw_registry',
 '_memoized_property',
 '_negate',
 '_order_by_label_element',
 '_params',
 '_populate_column_collection',
 '_prefixes',
 '_refresh_for_new_column',
 '_reset_exported',
 '_schema_item_copy',
 '_select_iterable',
 '_set_parent',
 '_set_parent_with_dispatch',
 '_sorted_constraints',
 '_textual',
 '_translate_schema',
 '_validate_dialect_kwargs',
 '_with_annotations',
 'add_is_dependent_on',
 'alias',
 'append_column',
 'append_constraint',
 'append_ddl_listener',
 'argument_for',
 'bind',
 'c',
 'columns',
 'comment',
 'compare',
 'compile',
 'constraints',
 'correspond_on_equivalents',
 'corresponding_column',
 'count',
 'create',
 'delete',
 'description',
 'dialect_kwargs',
 'dialect_options',
 'dispatch',
 'drop',
 'exists',
 'foreign_key_constraints',
 'foreign_keys',
 'fullname',
 'get_children',
 'implicit_returning',
 'indexes',
 'info',
 'insert',
 'is_clause_element',
 'is_derived_from',
 'is_selectable',
 'join',
 'key',
 'kwargs',
 'lateral',
 'metadata',
 'name',
 'named_with_column',
 'outerjoin',
 'params',
 'primary_key',
 'quote',
 'quote_schema',
 'replace_selectable',
 'schema',
 'select',
 'selectable',
 'self_group',
 'supports_execution',
 'tablesample',
 'tometadata',
 'unique_params',
 'update']
In [ ]: