معرفی شرکت ها
embrace-4.2.0
تبلیغات ما
مشتریان به طور فزاینده ای آنلاین هستند. تبلیغات می تواند به آنها کمک کند تا کسب و کار شما را پیدا کنند.
مشاهده بیشتر
تبلیغات ما
مشتریان به طور فزاینده ای آنلاین هستند. تبلیغات می تواند به آنها کمک کند تا کسب و کار شما را پیدا کنند.
مشاهده بیشتر
تبلیغات ما
مشتریان به طور فزاینده ای آنلاین هستند. تبلیغات می تواند به آنها کمک کند تا کسب و کار شما را پیدا کنند.
مشاهده بیشتر
تبلیغات ما
مشتریان به طور فزاینده ای آنلاین هستند. تبلیغات می تواند به آنها کمک کند تا کسب و کار شما را پیدا کنند.
مشاهده بیشتر
تبلیغات ما
مشتریان به طور فزاینده ای آنلاین هستند. تبلیغات می تواند به آنها کمک کند تا کسب و کار شما را پیدا کنند.
مشاهده بیشترتوضیحات
Embrace SQL keeps your SQL queries in SQL files. An anti-ORM inspired by HugSQL and PugSQL
| ویژگی | مقدار |
|---|---|
| سیستم عامل | - |
| نام فایل | embrace-4.2.0 |
| نام | embrace |
| نسخه کتابخانه | 4.2.0 |
| نگهدارنده | [] |
| ایمیل نگهدارنده | [] |
| نویسنده | Oliver Cope |
| ایمیل نویسنده | oliver@redgecko.org |
| آدرس صفحه اصلی | https://hg.sr.ht/~olly/embrace-sql |
| آدرس اینترنتی | https://pypi.org/project/embrace/ |
| مجوز | Apache |
An interface for using plain SQL, in files.
=============================================
Does writing complex queries in an ORM feel like driving with the handbrake on?
Embrace SQL! Put your SQL queries in regular ``.sql`` files, and embrace will
load them.
Installation::
pip install embrace
Usage::
import embrace
# Connect to your database, using any db-api connector.
# If python supports it, so does embrace.
conn = psycopg2.connect("postgresql:///mydb")
# Create a module populated with queries from a collection of *.sql files:
queries = embrace.module("resources/sql")
# Run a query
users = queries.list_users(conn, order_by='created_at')
Add ``resources/sql/list_users.sql`` containing an SQL query::
-- :name list_users :many
select * from users where active = :active order by :identifier:order_by
What is the format of a query SQL file?
----------------------------------------
Embrace-SQL tries to stick close to the format used by HugSQL and PugSQL.
SQL files contain special comments to specify the query name and result type.
::
-- :name get_user_count
-- :result :scalar
SELECT count(1) FROM users
If a result type is omitted, it will default to ``cursor``. The result type
can be included in the same line as the name:
::
-- :name get_user_count :scalar
If ``:name`` is omitted, it will default to the filename without extension.
A single file may contain multiple SQL queries, separated by a structured SQL
comment. For example to create two query objects accessible as
``queries.list_users()`` and ``queries.get_user_by_id()``:
::
-- :name list_users :many
select * from users
-- :name get_user_by_id :one
select * from users where id=:id
But if you *don't* have the separating comment, embrace-sql can run
multiple statements in a single query call, returning the result from just the last one.
Why? Because it makes this possible in MySQL:
::
-- :result :column
insert into users (name, email) values (:name, :email);
select last_insert_id();
Including queries
`````````````````
You may include one query inside another using the ``:include:`` directive. For
example:
::
-- :name select_valid_users
SELECT * FROM users WHERE deleted_at IS NULL;
-- :name select_user_by_email
SELECT * FROM (:include:select_valid_users) WHERE email = :email;
What can queries return?
------------------------------
The following result types are supported:
========================= ======================================================
``:affected``, ``:n`` The number of rows affected
``:first`` The first row, as returned by ``cursor.fetchone()``,
or ``None`` if no row is found.
``:one``, ``:1`` A single row, as returned by ``cursor.fetchone()``,
usually as a tuple (but most db-api modules have
extensions allowing you to access rows as dicts or
named tuples.
If no row is generated by the query,
``embrace.exceptions.NoResultFound`` will be raised.
If more than one row is generated by the query,
``embrace.exceptions.MultipleResultsFound`` will be
raised.
``exactly-one`, ``:=1`` Synonyms for ``:one``, retained for compatibility
``:one-or-none`` As ``one``, but returns None if no row is returned by
the query.
``:many``, ``:*`` An iterator over a number of rows. Each row will be
the value returned by ``cursor.fetchone()``, usually
a tuple.
``:cursor``, ``:raw`` The cursor object.
``:scalar`` The value of the first column of the first row
returned by the query.
If no row is generated by the query, a
``NoResultFound`` will be raised.
``:column`` An iterator over the values in the first column
returned.
``:resultset`` An object supporting access to query results as any of
the above result types.
**This is the default result type if no result type is
specified**
========================= ======================================================
You can override the return type specified by the query from Python code by
using one of the following methods on the ``Query`` object:
- ``affected``
- ``one``
- ``exactlyone``
- ``many``
- ``cursor``
- ``scalar``
- ``column``
- ``resultset``
Use ``resultset`` to get access to both the results and cursor metadata, for example::
result = Query("SELECT * from mytable").resultset(conn)
print(result.many())
print(result.cursor.description)
How do I return rows as dicts or named tuples?
----------------------------------------------
Queries return rows directly from the underlying db-api driver.
Many drivers have options to return data structures other than tuples (for
example ``sqlite3.Row`` or ``psycopg2.extras.DictCursor``). You will need to
configure these at the connection level.
See the next section for how to use ``embrace.query.mapobject`` to map rows
on to namedtuples, dicts or your own ORM-style model classes.
How do I map rows onto objects?
-------------------------------
Embrace supports simple ORM style mapping.
Example::
import embrace
from dataclasses import dataclass
@dataclass
class User:
id: int
name: str
query = queries.query("SELECT * from users").returning(User)
users = query.many(conn)
Map multiple classes in a single query::
query = queries.query(
"SELECT * FROM posts JOIN users ON posts.user_id = users.id"
).returning((Post, User))
for post, user in query.many(conn):
…
By default embrace looks for fields named ``id`` (case insensitive) to
split up the row.
If you need to split on different columns, use ``mapobject`` to specify how to
map the returned columns onto objects::
from embrace import mapobject
query = queries.query(
"""
SELECT posts.*, users.*
FROM posts JOIN users ON posts.user_id = users.id
"""
).returning(
(
mapobject(Post, split="post_id"),
mapobject(User, split="user_id")
)
)
for post, user in query.many(conn):
…
``mapobject`` can also load columns into dicts and namedtuples::
from embrace import mapobject
query = queries.query(
"""
SELECT posts.*, users.*
FROM posts JOIN users ON posts.user_id = users.id
"""
).returning(
(
mapobject.dict(split="post_id"),
mapobject.namedtuple(split="user_id")
)
)
for post, user in query.many(conn):
…
and pass individual columns through unchanged::
query = queries.query(
"""
SELECT posts.*, count(*) as reply_count
FROM posts JOIN replies ON posts.id = replies.post_id
"""
).returning(
(
mapobject(Post, split="post_id"),
mapobject.passthrough(split="reply_count"),
)
)
for post, reply_count in query.many(conn):
…
You can also tell embrace to populate join relationships::
from embrace import one_to_many
from embrace import one_to_one
query = queries.query(
"""
SELECT users.*, orders.*, products.*
FROM users
JOIN orders ON orders.user_id = users.id
JOIN products ON orders.product_id = products.id
ORDER BY users.id, orders.id
"""
).returning(
# Each row of this query returns data for a User, Order and Product
# object. The `key` parameter tells embrace to map items with identical
# key values to the same python object.
(
mapobject(User, key="id"),
mapobject(Order, key="id"),
mapobject(Product, key="id"),
),
joins=[
# Populate User.orders with the list of Order objects
one_to_many(User, 'orders', Order),
# Populate Order.product with the product object
one_to_one(Order, 'product', Product),
],
)
for user in query.many(conn):
for order in user.order:
product = order.product
…
Note that methods like ``query.one`` operate at the level of the database
cursor.
If you use ``joins`` to consolidate multiple database rows into a single
object,
you will still need to call ``query.many`` even if you only require a
single object to be returned.
How do parameters work?
------------------------
Placeholders inserted using the ``:name`` syntax are escaped by the db-api
driver:
::
-- Outputs `select * from user where name = 'o''brien'`;
select * from users where name = :name
You can interpolate lists and tuples too:
``:tuple:`` creates a placeholder like this ``(?, ?, ?)``
``:value*:`` creates a placeholder like this ``?, ?, ?``
``:tuple*:`` creates a placeholder like this ``(?, ?, ?), (?, ?, ?), …``
(useful for multiple insert queries)
::
-- Call this with `queries.insert_foo(data=(1, 2, 3))`
INSERT INTO foo (a, b, c) VALUES :tuple:data
-- Call this with `queries.get_matching_users(names=("carolyn", "douglas"))`
SELECT * from users WHERE name in (:value*:names)
You can escape identifiers with ``:identifier:``, like this:
::
-- Outputs `select * from "some random table"`
select * from :identifier:table_name
You can pass through raw sql too. This leaves you open to SQL injection attacks if you allow user input into such parameters:
::
-- Outputs `select * from users order by name desc`
select * from users order by :raw:order_clause
How do I handle connections? Transactions?
------------------------------------------
You must pass a db-api connection object every time you call a query.
You can manage these connections yourself, but Embrace also offers a connection
pooling module.
::
from embrace import pool
# Create a connection pool
connection_pool = pool.ConnectionPool(
partial(psycopg2.connect, database='mydb'),
limit=10
)
# Example 1 - explicit calls to getconn/release
conn = connection_pool.getconn()
try:
queries.execute_some_query(conn)
finally:
connection_pool.release(conn)
# Example 2 - context manager
with connection_pool.connect() as conn:
queries.execute_some_query(conn)
Transaction handling may be handled manually by calling ``commit()`` or
``rollback()`` on the connection object, or you can also use the
``transaction`` context run to queries in a transaction:
::
with queries.transaction(conn) as q:
q.increment_counter()
The transaction will be commited when the ``with`` block exits, or rolled back
if an exception occurred.
How do I reload queries when the underlying files change?
---------------------------------------------------------
Pass auto_reload=True when constructing a module:
::
m = module('resources/sql', auto_reload=True)
Exceptions
----------
Exceptions raised from the underlying db-api connection are wrapped in
exception classes from ``embrace.exceptions``, with PEP-249 compliant names.
You can use this like so:
::
try:
queries.execute("SELECT 1.0 / 0.0")
except embrace.exceptions.DataError:
pass
The original exception is available in the ``__cause__`` attribute of the
embrace exception object.
نیازمندی
| مقدار | نام |
|---|---|
| - | sqlparse |
| - | wrapt |
نحوه نصب
نصب پکیج whl embrace-4.2.0:
pip install embrace-4.2.0.whl
نصب پکیج tar.gz embrace-4.2.0:
pip install embrace-4.2.0.tar.gz