### Example Database Setup and Query

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Provides an example of importing and populating a sample database (`estore`) and then running a query to find the country with the most customers. It also points to further examples.

```python
>>> from pony.orm.examples.estore import *

>>> populate_database()

>>> select((customer.country, count(customer))
...        for customer in Customer).order_by(-2).first()

SELECT "customer"."country", COUNT(DISTINCT "customer"."id")
FROM "Customer" "customer"
GROUP BY "customer"."country"
ORDER BY 2 DESC
LIMIT 1
```

--------------------------------

### Install Pony ORM

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Command to install the Pony ORM library using pip. This is the first step to start using Pony ORM.

```text
pip install pony
```

--------------------------------

### Pony ORM Example Setup and Entity Definitions

Source: https://github.com/ponyorm/pony-doc/blob/master/queries.rst

This snippet shows the setup for using Pony ORM examples, including importing necessary modules and defining the data model entities for an e-commerce store.

```python
from decimal import Decimal
from datetime import datetime

from pony.converting import str2datetime
from pony.orm import *

db = Database()

class Customer(db.Entity):
    email = Required(str, unique=True)
    password = Required(str)
    name = Required(str)
    country = Required(str)
    address = Required(str)
    cart_items = Set('CartItem')
    orders = Set('Order')

class Product(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    categories = Set('Category')
    description = Optional(str)
    picture = Optional(buffer)
    price = Required(Decimal)
    quantity = Required(int)
    cart_items = Set('CartItem')
    order_items = Set('OrderItem')

class CartItem(db.Entity):
    quantity = Required(int)
    customer = Required(Customer)
    product = Required(Product)

class OrderItem(db.Entity):
    quantity = Required(int)
    price = Required(Decimal)
    order = Required('Order')
    product = Required(Product)
    PrimaryKey(order, product)

class Order(db.Entity):
    id = PrimaryKey(int, auto=True)
    state = Required(str)
    date_created = Required(datetime)
    date_shipped = Optional(datetime)
    date_delivered = Optional(datetime)
    total_price = Required(Decimal)
    customer = Required(Customer)
    items = Set(OrderItem)

class Category(db.Entity):
    name = Required(str, unique=True)
    products = Set(Product)

set_sql_debug(True)
db.bind('sqlite', 'estore.sqlite', create_db=True)
db.generate_mapping(create_tables=True)
```

--------------------------------

### Database Binding Examples

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Demonstrates how to bind the Pony ORM database object to different database providers, including in-memory SQLite, file-based SQLite, PostgreSQL, MySQL, Oracle, and CockroachDB. It shows the necessary parameters for each provider.

```python
>>> db.bind(provider='sqlite', filename=':memory:')
>>> db.bind(provider='sqlite', filename='database.sqlite', create_db=True)

# PostgreSQL
db.bind(provider='postgres', user='', password='', host='', database='')

# MySQL
db.bind(provider='mysql', host='', user='', passwd='', db='')

# Oracle
db.bind(provider='oracle', user='', password='', dsn='')

# CockroachDB
db.bind(provider='cockroach', user='', password='', host='', database='', )
```

--------------------------------

### FastAPI Setup with PonyORM

Source: https://github.com/ponyorm/pony-doc/blob/master/integration_with_fastapi.rst

Initializes a FastAPI application and configures the PonyORM database connection using Pydantic settings. This snippet demonstrates the basic setup required to start using PonyORM with FastAPI.

```python
import fastapi
from src.config import Settings
from src.models import db

settings = Settings()
api = fastapi.FastAPI()
db.bind(**settings.get_connection)
db.generate_mapping(create_tables=True)
```

--------------------------------

### Import Pony ORM Essentials

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Imports all necessary classes and functions from the Pony ORM library into the global namespace for easy access. Recommended for beginners.

```python
>>> from pony.orm import *

```

--------------------------------

### Raw SQL Queries

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Demonstrates executing raw SQL queries directly against the database, both for selecting entities and for direct data retrieval without using ORM entities.

```python
>>> x = 25
>>> Person.select_by_sql('SELECT * FROM Person p WHERE p.age < $x')

SELECT * FROM Person p WHERE p.age < ?
[25]

[Person[1], Person[2]]
```

```python
>>> x = 20
>>> db.select('name FROM Person WHERE age > $x')
SELECT name FROM Person WHERE age > ?
[20]

[u'Mary', u'Bob']
```

--------------------------------

### Query with Ordering and Limit

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Example of ordering query results by a specific attribute and limiting the number of returned objects using slicing. Includes the generated SQL.

```python
>>> select(p for p in Person).order_by(Person.name)[:2]

    SELECT "p"."id", "p"."name", "p"."age"
    FROM "Person" "p"
    ORDER BY "p"."name"
    LIMIT 2

    [Person[3], Person[1]]
```

--------------------------------

### Create Database Object

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Initializes a new Database object, which serves as the connection point for all entities to the database.

```python
>>> db = Database()

```

--------------------------------

### Query with .show() for Related Objects

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Demonstrates the `.show()` method's behavior with 'to-one' relationships, displaying the related entity's identifier. Also shows an example of showing all 'Car' objects.

```python
>>> Car.select().show()
    id|make  |model   |owner    
    --+------+--------+---------
    1 |Toyota|Prius   |Person[2]
    2 |Ford  |Explorer|Person[3]
```

--------------------------------

### Object Retrieval by Primary Key and Attributes

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Illustrates how to fetch an object using its primary key and the `Entity.get` method for other attributes. It also explains the role of database session caching.

```python
>>> p1 = Person[1]
>>> print p1.name
John
```

```python
>>> mary = Person.get(name='Mary')

SELECT "id", "name", "age"
FROM "Person"
WHERE "name" = ?
[u'Mary']

>>> print mary.age
22
```

```python
>>> show(mary)
instance of Person
id|name|age
--+----+---
2 |Mary|22
```

--------------------------------

### PonyORM get() method example

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Demonstrates how to use the `get()` method to retrieve a single value or a tuple of values from a database query.

```python
id = 1
age = db.get("select age from Person where id = $id")

name, age = db.get("select name, age from Person where id = $id")
```

--------------------------------

### Running PonyORM Flask Example

Source: https://github.com/ponyorm/pony-doc/blob/master/integration_with_flask.rst

Provides the command to run an example application demonstrating PonyORM's Flask integration. This is useful for testing and understanding the integration in practice.

```sh
python -m pony.flask.example
```

--------------------------------

### Generating Database Mapping

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Shows how to generate the database schema based on the defined entities. The `create_tables=True` parameter ensures that tables are created if they do not already exist.

```python
>>> db.generate_mapping(create_tables=True)
```

--------------------------------

### Query Execution with Slice and SQL Output

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Demonstrates executing a query and retrieving all results using the slice operator `[:]`. It also shows the generated SQL query and the returned entity objects.

```python
>>> select(p for p in Person if p.age > 20)[:](

    SELECT "p"."id", "p"."name", "p"."age"
    FROM "Person" "p"
    WHERE "p"."age" > 20

    [Person[2], Person[3]]
```

--------------------------------

### Database Session Context Manager

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Illustrates using the db_session as a context manager for database operations. This approach also ensures automatic transaction handling and session cleanup.

```python
with db_session:
    p = Person(name='Kate', age=33)
    Car(make='Audi', model='R8', owner=p)
    # commit() will be done automatically
    # database session cache will be cleared automatically
    # database connection will be returned to the pool
```

--------------------------------

### Import Pony ORM Package

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Imports the Pony ORM package itself, requiring the use of 'orm.' prefix for all Pony functions and decorators. Useful for avoiding namespace pollution.

```python
>>> from pony import orm

```

--------------------------------

### Basic Query and Aggregation

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Demonstrates a simple SELECT DISTINCT query and a more complex query involving aggregation (COUNT) and grouping. It also shows how to retrieve the maximum value of an attribute.

```python
>>> select((p, count(p.cars)) for p in Person)[:]

SELECT "p"."id", COUNT(DISTINCT "car-1"."id")
FROM "Person" "p"
  LEFT JOIN "Car" "car-1"
    ON "p"."id" = "car-1"."owner"
GROUP BY "p"."id"

[(Person[1], 0), (Person[2], 1), (Person[3], 1)]
```

```python
>>> print max(p.age for p in Person)
SELECT MAX("p"."age")
FROM "Person" "p"

30
```

--------------------------------

### Query Iteration with For Loop

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Shows how to iterate over query results using a standard `for` loop without fetching all results at once. This is useful for processing large result sets.

```python
>>> persons = select(p for p in Person if 'o' in p.name)
>>> for p in persons:
...     print p.name, p.age
... 
    SELECT "p"."id", "p"."name", "p"."age"
    FROM "Person" "p"
    WHERE "p"."name" LIKE '%o%'

    John 20
    Bob 30
```

--------------------------------

### Entity Hooks: Example Usage

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Demonstrates how to implement and use the `before_insert` entity hook with an example.

```python
class Message(db.Entity):
    title = Required(str)
    content = Required(str)

    def before_insert(self):
        print("Before insert! title=%s" % self.title)

commit()
# Output:
# Before insert! title=First message

# SQL executed:
# INSERT INTO "Message" ("title", "content") VALUES (?, ?)
# [u'First message', u'Hello, world!']
```

--------------------------------

### Enabling SQL Debug Mode

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Illustrates how to enable debug mode to view the SQL commands sent by Pony ORM to the database. This is useful for understanding the generated SQL and troubleshooting.

```python
>>> set_sql_debug(True)
```

--------------------------------

### Basic Query Execution

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Shows how to create and execute a simple query using the select function to retrieve entities based on a condition. The query is translated to SQL and executed upon iteration.

```python
>>> select(p for p in Person if p.age > 20)
<pony.orm.core.Query at 0x105e74d10>
```

--------------------------------

### Bind Database (MySQL Example)

Source: https://github.com/ponyorm/pony-doc/blob/master/database.rst

Connects to a MySQL database. Requires host, user, password, and database name.

```python
db.bind(provider='mysql', host='', user='', passwd='', db='')
```

--------------------------------

### Creating Entity Instances and Committing

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Demonstrates the creation of entity instances (persons and cars) and how to save them to the database using the `commit()` function. Changes are not persisted until `commit()` is called.

```python
>>> p1 = Person(name='John', age=20)
>>> p2 = Person(name='Mary', age=22)
>>> p3 = Person(name='Bob', age=30)
>>> c1 = Car(make='Toyota', model='Prius', owner=p2)
>>> c2 = Car(make='Ford', model='Explorer', owner=p3)
>>> commit()
```

--------------------------------

### Query Returning Specific Attributes

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Example of constructing a query that returns only specific attributes of the entities, rather than the entire entity objects.

```python
>>> select(p.name for p in Person if p.age != 30)[:](

```

--------------------------------

### Bind Database (PostgreSQL Example)

Source: https://github.com/ponyorm/pony-doc/blob/master/database.rst

Connects the Database object to a specific database provider. This example shows binding to a PostgreSQL database, requiring user, password, host, and database name.

```python
db.bind(provider='postgres', user='', password='', host='', database='')
```

--------------------------------

### Multiple Database Setup

Source: https://github.com/ponyorm/pony-doc/blob/master/transactions.rst

Demonstrates how to configure and use multiple databases simultaneously in PonyORM. It shows defining entities for different databases and managing transactions across them.

```python
db1 = Database()

class User(db1.Entity):
    ... # User entity definition

db1.bind('postgres', ...)


db2 = Database()

class Address(db2.Entity):
    ... # Address entity definition

db2.bind('mysql', ...)

@db_session
def do_something(user_id, address_id):
    u = User[user_id]
    a = Address[address_id]
    ...

# PonyORM automatically handles commit/rollback for both databases on exiting do_something()
# Specific commits can be done using db1.commit() or db2.commit()
```

--------------------------------

### Basic Transaction Example

Source: https://github.com/ponyorm/pony-doc/blob/master/transactions.rst

Demonstrates a simple transaction involving two accounts, checking for sufficient funds before debiting one and crediting another.

```python
account1 = Account[account_id1]
account2 = Account[account_id2]
if amount > account1.amount:
    raise ValueError("Not enough funds")
account1.amount -= amount
account2.amount += amount
```

--------------------------------

### SQL Example for `get_sql()`

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

An example of the SQL query generated by the `get_sql()` method for filtering categories by name.

```sql
SELECT "c"."id", "c"."name"
FROM "category" "c"
WHERE "c"."name" LIKE 'a%%'
```

--------------------------------

### Query with .show() Method

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Illustrates using the `.show()` method on a query result to display attributes of entity objects in a tabular format. It handles 'to-one' relationships but omits 'to-many' attributes.

```python
>>> select(p for p in Person).order_by(Person.name)[:2].show()

    SELECT "p"."id", "p"."name", "p"."age"
    FROM "Person" "p"
    ORDER BY "p"."name"
    LIMIT 2

    id|name|age
    --+----+---
    3 |Bob |30 
    1 |John|20
```

--------------------------------

### Bind Database (SQLite File Example)

Source: https://github.com/ponyorm/pony-doc/blob/master/database.rst

Connects to a SQLite database file. If the file does not exist, it will be created. The 'create_db=True' parameter ensures the database file is generated.

```python
db.bind(provider='sqlite', filename='database.sqlite', create_db=True)
```

--------------------------------

### Show Entity Definition

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Uses the 'show' function to display the definition of an entity, including automatically generated attributes like 'id'. Useful for inspecting entity structure.

```python
>>> show(Person)
class Person(Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    age = Required(int)
    cars = Set(Car)

```

--------------------------------

### Bind Database (Oracle Example)

Source: https://github.com/ponyorm/pony-doc/blob/master/database.rst

Connects to an Oracle database. Requires user, password, and a Data Source Name (DSN).

```python
db.bind(provider='oracle', user='', password='', dsn='')
```

--------------------------------

### Using Database Sessions

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Explains the necessity of database sessions for database interactions in Pony ORM applications. The `@db_session` decorator is used to wrap functions that perform database operations, ensuring proper session management.

```python
# Example usage of db_session decorator would go here, but the provided text only describes its purpose.
```

--------------------------------

### Pony ORM Query Examples

Source: https://github.com/ponyorm/pony-doc/blob/master/queries.rst

Demonstrates various Pony ORM query patterns for common data operations such as filtering, aggregation, ordering, and relationship traversal. These examples are designed to illustrate the expressive power of Pony ORM's generator expressions.

```python
# All USA customers
Customer.select(lambda c: c.country == 'USA')
```

```python
# The number of customers for each country
select((c.country, count(c)) for c in Customer)
```

```python
# Max product price
max(p.price for p in Product)
```

```python
# Max SSD price
max(p.price for p in Product
                for cat in p.categories if cat.name == 'Solid State Drives')
```

```python
# Three most expensive products
Product.select().order_by(desc(Product.price))[:3]
```

```python
# Out of stock products
Product.select(lambda p: p.quantity == 0)
```

```python
# Most popular product
Product.select().order_by(lambda p: desc(sum(p.order_items.quantity))).first()
```

```python
# Products that have never been ordered
Product.select(lambda p: not p.order_items)
```

```python
# Customers who made several orders
Customer.select(lambda c: count(c.orders) > 1)
```

```python
# Three most valuable customers
Customer.select().order_by(lambda c: desc(sum(c.orders.total_price)))[:3]
```

```python
# Customers whose orders were shipped
Customer.select(lambda c: SHIPPED in c.orders.state)
```

```python
# Customers with no orders
Customer.select(lambda c: not c.orders)
```

```python
# The same query with the LEFT JOIN instead of NOT EXISTS
left_join(c for c in Customer for o in c.orders if o is None)
```

```python
# Customers which ordered several different tablets
select(c for c in Customer
             for p in c.orders.items.product
             if 'Tablets' in p.categories.name and count(p) > 1)
```

--------------------------------

### PonyORM Database Operations

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Provides examples of executing raw SQL queries, checking for existence, and flushing changes to the database.

```python
cursor = db.execute("""create table Person (
                         id integer primary key autoincrement,
                         name text,
                         age integer
                  )""")

name, age = "Ben", 33
cursor = db.execute("insert into Person (name, age) values ($name, $age)")
```

```python
name = 'John'
if db.exists("select * from Person where name = $name"):
    print "Person exists in the database"
```

```python
db.flush()
```

```python
result = db.get("select name from Person where age = $age")
```

--------------------------------

### PonyORM insert() method example

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Shows an example of using the `insert()` method to add a new row to the 'Person' table and retrieve the generated primary key.

```python
new_id = db.insert("Person", name="Ben", age=33, returning='id')
```

--------------------------------

### db_session Context Manager Example (Python)

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Illustrates using db_session as a context manager in Python for database operations.

```python
def process_request():
    ...
    with db_session:
        u = User.get(username=username)
        ...
```

--------------------------------

### Bind Database (CockroachDB Example)

Source: https://github.com/ponyorm/pony-doc/blob/master/database.rst

Connects to a CockroachDB database. Requires user, password, host, database name, and optionally SSL mode.

```python
db.bind(provider='cockroach', user='', password='', host='',
        database='', sslmode='disable')
```

--------------------------------

### db_session Decorator Example (Python)

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Demonstrates how to use the db_session decorator in Python to manage database operations within a session.

```python
@db_session
def check_user(username):
    return User.exists(username=username)
```

--------------------------------

### Entity Options: Table Options

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Provides an example of how to add custom options to the CREATE TABLE command for an entity.

```python
class MyEntity(db.Entity):
    id = PrimaryKey(int)
    foo = Required(str)
    bar = Optional(int)

    _table_options_ = {
        'ENGINE': 'InnoDB',
        'TABLESPACE': 'my_tablespace',
        'ENCRYPTION': "'N'",
        'AUTO_INCREMENT': 10
    }
```

--------------------------------

### PonyORM Entity Definition Example

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Shows the basic structure for defining an entity in PonyORM, including primary keys and required/optional attributes.

```python
from pony.orm import * 

class Person(db.Entity):
    id = PrimaryKey(int, auto=True)
    name = Required(str)
    age = Optional(int)
```

--------------------------------

### PonyORM on_connect Callback

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Shows how to register a function to be called upon establishing a database connection. This example configures case-insensitive LIKE behavior for SQLite.

```python
db = Database()

# entities declaration

@db.on_connect(provider='sqlite')
def sqlite_case_sensitivity(db, connection):
    cursor = connection.cursor()
    cursor.execute('PRAGMA case_sensitive_like = OFF')

db.bind(**options)
db.generate_mapping(create_tables=True)
```

--------------------------------

### Flask-Login Integration with PonyORM

Source: https://github.com/ponyorm/pony-doc/blob/master/integration_with_flask.rst

Shows a comprehensive example of integrating PonyORM with Flask and Flask-Login. It includes setting up the database, defining a User entity inheriting from `UserMixin`, configuring Flask-Login, and loading users.

```python
from flask import Flask, render_template
from flask_login import LoginManager, UserMixin, login_required
from pony.flask import Pony
from pony.orm import Database, Required, Optional
from datetime import datetime

app = Flask(__name__)
app.config.update(dict(
    DEBUG = False,
    SECRET_KEY = 'secret_xxx',
    PONY = {
        'provider': 'sqlite',
        'filename': 'db.db3',
        'create_db': True
    }
))

db = Database()

class User(db.Entity, UserMixin):
    login = Required(str, unique=True)
    password = Required(str)
    last_login = Optional(datetime)

db.bind(**app.config['PONY'])
db.generate_mapping(create_tables=True)

Pony(app)
login_manager = LoginManager(app)
login_manager.login_view = 'login'

@login_manager.user_loader
def load_user(user_id):
    return db.User.get(id=user_id)
```

--------------------------------

### PonyORM select() method example

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Illustrates the usage of the `select()` method to fetch multiple rows and access columns by attribute name.

```python
result = db.select("select * from Person")

for row in db.select("name, age from Person"):
    print(row.name, row.age)
```

--------------------------------

### PonyORM Query Statistics Example

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Provides an example of how to access and process query execution statistics stored in PonyORM's thread-local storage. It sorts queries by total time spent.

```python
query_stats = sorted(db.local_stats.values(),
        reverse=True, key=attrgetter('sum_time'))
for qs in query_stats:
    print(qs.sum_time, qs.db_count, qs.sql)
```

--------------------------------

### SQL GROUP BY Example

Source: https://github.com/ponyorm/pony-doc/blob/master/aggregations.rst

Illustrates a standard SQL SELECT statement with aggregate functions and a placeholder for GROUP BY and HAVING clauses.

```sql
SELECT A, B, C, SUM(D), MAX(E), COUNT(F)
FROM T1
WHERE ...
GROUP BY ...
HAVING ...
```

--------------------------------

### SQL Generated by Pony ORM (DISTINCT Examples)

Source: https://github.com/ponyorm/pony-doc/blob/master/queries.rst

Shows the SQL queries generated by Pony ORM, illustrating the use and absence of the DISTINCT keyword based on the Python query structure.

```sql
SELECT "p"."id", "p"."name", "p"."age"
FROM "Person" "p"
WHERE "p"."age" > 20
  AND "p"."name" = 'John'
```

```sql
SELECT DISTINCT "p"."name"
FROM "Person" "p"
```

```sql
SELECT DISTINCT "p"."id", "p"."name", "p"."age"
FROM "Person" "p", "Car" "c"
WHERE "c"."make" IN ('Toyota', 'Honda')
  AND "p"."id" = "c"."owner"
```

--------------------------------

### SQL Generated by Pony ORM (Function Examples)

Source: https://github.com/ponyorm/pony-doc/blob/master/queries.rst

Illustrates the SQL queries generated by Pony ORM when using functions like AVG and subqueries within the query definition.

```sql
SELECT AVG("order-1"."total_price")
FROM "Customer" "c"
  LEFT JOIN "Order" "order-1"
    ON "c"."id" = "order-1"."customer"
```

```sql
SELECT "o"."id", "o"."state", "o"."date_created", "o"."date_shipped",
       "o"."date_delivered", "o"."total_price", "o"."customer"
FROM "Order" "o"
WHERE "o"."customer" IN (
    SELECT "c"."id"
    FROM "Customer" "c"
    WHERE "c"."name" LIKE 'A%'
    )
```

--------------------------------

### PonyORM select() Function Examples

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Demonstrates the usage of the select() function to retrieve specific attributes or tuples from entities, and how to apply Query methods like order_by and count.

```python
select(p.name for p in Product)

select((p1, p2) for p1 in Product
                for p2 in Product if p1.name == p2.name and p1 != p2)

select((p.name, count(p.orders)) for p in Product)
```

--------------------------------

### Selecting Instances with Offset

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Demonstrates how to select a range of instances from a query using slicing with 'start' and 'end' values. This is equivalent to using LIMIT and OFFSET in SQL.

```python
select(c for c in Customer).order_by(Customer.name)[20:30]
```

--------------------------------

### Limit and Offset Query

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Shows how to limit the number of instances selected from the database and specify an offset using the limit method. Also mentions alternative methods like [start:end] and page.

```APIDOC
limit(limit=None, offset=None)
    Limit the number of instances to be selected from the database.

    Example:
        select(c for c in Customer).limit(10, offset=30)

    Note: Since version 0.7.6 limit can be None.
```

--------------------------------

### Pony ORM Database Binding

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Demonstrates how to bind the Pony ORM Database object to different database providers. Includes examples for SQLite, PostgreSQL, MySQL, Oracle, and CockroachDB.

```python
db.bind(provider='sqlite', filename='db.sqlite', create_db=False)
```

```python
db.bind(provider='sqlite', filename=':memory:')
```

```python
db.bind(provider='sqlite', filename=':sharedmemory:')
```

```python
db.bind(provider='postgres', user='', password='', host='', database='')
```

```python
db.bind(provider='mysql', host='', user='', passwd='', db='')
```

```python
db.bind(provider='oracle', user='', password='', dsn='')
```

```python
db.bind(provider='cockroach', user='', password='', host='', database='', sslmode='disable')
```

```python
db.bind(provider='cockroach', user='', password='', host='', database='',
            port=26257, sslmode='require', sslrootcert='certs/ca.crt',
            sslkey='certs/client.maxroach.key', sslcert='certs/client.maxroach.crt')
```

--------------------------------

### PonyORM Database Methods

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

Documents core database interaction methods including `on_connect`, `execute`, `exists`, `flush`, `generate_mapping`, and `get`.

```APIDOC
on_connect(provider=None)
    Registers a function to be called each time a new connection for a given provider is established. If provider is not specified, the function will be called for every provider.
    The function should be registered before `db.bind(...)` call and should accept two positional arguments: `Database db` and `DBAPIConnection connection`.
    Example:
        @db.on_connect(provider='sqlite')
        def sqlite_case_sensitivity(db, connection):
            cursor = connection.cursor()
            cursor.execute('PRAGMA case_sensitive_like = OFF')
```

```APIDOC
execute(sql, globals=None, locals=None)
    Executes an SQL statement. Flushes changes before execution.
    Parameters:
      sql: The SQL statement text.
      globals: Dictionary of global variables.
      locals: Dictionary of local variables used within the query.
    Returns: A DBAPI cursor.
    Example:
        cursor = db.execute("insert into Person (name, age) values ($name, $age)")
```

```APIDOC
exists(sql, globals=None, locals=None)
    Checks if at least one row satisfies the query. Flushes changes before execution.
    Parameters:
      sql: The SQL statement text.
      globals: Dictionary of global variables.
      locals: Dictionary of local variables used within the query.
    Returns: bool
    Example:
        if db.exists("select * from Person where name = $name"):
            print "Person exists in the database"
```

```APIDOC
flush()
    Saves changes accumulated in the session cache to the database. Automatically called before certain methods like `get`, `exists`, `execute`, `commit`, `select`.
```

```APIDOC
generate_mapping(check_tables=True, create_tables=False)
    Maps declared entities to database tables. Creates tables, foreign keys, and indexes if necessary.
    Parameters:
      check_tables: If True, checks if table and attribute names in the database correspond to the entity declaration.
      create_tables: If True, creates tables, foreign key references, and indexes if they don't exist.
```

```APIDOC
get(sql, globals=None, locals=None)
    Selects a single row or value from the database.
```

--------------------------------

### Pony ORM Multiset Example

Source: https://github.com/ponyorm/pony-doc/blob/master/working_with_relationships.rst

Demonstrates how to access and print the 'make' attribute of a relationship in Pony ORM, showing the Multiset representation with counts.

```python
>>> print p1.cars.make
Multiset({u'Toyota': 2})
```

--------------------------------

### Pickling Pony ORM Query Results

Source: https://github.com/ponyorm/pony-doc/blob/master/working_with_entity_instances.rst

Demonstrates how to pickle a list of Pony ORM entity instances obtained from a query. The example uses `cPickle` to serialize the query results into a byte string, which can then be stored in a cache.

```python
from pony.orm.examples.estore import *
from pony.orm import db_session
import cPickle

with db_session:
    products = select(p for p in Product if p.price > 100)[:] 
    pickled_data = cPickle.dumps(products)
```

--------------------------------

### Querying Inherited Entities

Source: https://github.com/ponyorm/pony-doc/blob/master/entities.rst

Provides an example of how to query entities in an inheritance hierarchy and check the specific type of each instance using `isinstance()`. This demonstrates PonyORM's ability to return correct entity instances.

```python
for p in Person.select():
    if isinstance(p, Professor):
        print p.name, p.degree
    elif isinstance(p, Student):
        print p.name, p.gpa
    else:  # somebody else
        print p.name
```

--------------------------------

### Database Session Decorator

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Demonstrates the use of the @db_session decorator for managing database transactions and sessions in Pony ORM. It handles commit/rollback and session cache clearing automatically.

```python
@db_session
def print_person_name(person_id):
    p = Person[person_id]
    print p.name
    # database session cache will be cleared automatically
    # database connection will be returned to the pool

@db_session
def add_car(person_id, make, model):
    Car(make=make, model=model, owner=Person[person_id])
    # commit() will be done automatically
    # database session cache will be cleared automatically
    # database connection will be returned to the pool
```

--------------------------------

### Object Update and Commit

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Shows how to modify an object's attribute and persist the changes to the database using the `commit()` function. Pony ORM tracks changes and only saves modified attributes.

```python
>>> mary.age += 1
>>> commit()
```

--------------------------------

### Define Person and Car Entities

Source: https://github.com/ponyorm/pony-doc/blob/master/firststeps.rst

Defines two entity classes, Person and Car, with attributes and a relationship between them. Person has name, age, and a set of Cars. Car has make, model, and a required Person owner.

```python
>>> class Person(db.Entity):
...     name = Required(str)
...     age = Required(int)
...     cars = Set('Car')
... 
>>> class Car(db.Entity):
...     make = Required(str)
...     model = Required(str)
...     owner = Required(Person)
... 
>>> 

```

--------------------------------

### Cascade Delete Example (ConstraintError)

Source: https://github.com/ponyorm/pony-doc/blob/master/working_with_entity_instances.rst

An example demonstrating a scenario where deleting a Group with required, non-cascading relationships to Student objects raises a ConstraintError.

```python
class Group(db.Entity):
    major = Required(str)
    items = Set("Student", cascade_delete=False)

class Student(db.Entity):
    name = Required(str)
    group = Required(Group)
```

--------------------------------

### Cascade Delete Example (Automatic Deletion)

Source: https://github.com/ponyorm/pony-doc/blob/master/working_with_entity_instances.rst

An example illustrating cascade delete where deleting a Person object also deletes its related Passport object.

```python
class Person(db.Entity):
    name = Required(str)
    passport = Optional("Passport", cascade_delete=True)

class Passport(db.Entity):
    number = Required(str)
    person = Required("Person")
```

--------------------------------

### Using Parameters in Raw SQL Queries

Source: https://github.com/ponyorm/pony-doc/blob/master/database.rst

Demonstrates how to pass parameters into raw SQL queries using the '$' prefix for variables and expressions. PonyORM handles parameter substitution securely to prevent SQL injection.

```python
x = "John"
data = db.select("select * from Person where name = $x")
```

```python
data = db.select("select * from Person where name = $x", {"x" : "Susan"})
```

```python
data = db.select("select * from Person where name = $(x.lower()) and age > $(y + 2)")
```

```python
x = 10
a = 20
b = 30
db.execute("SELECT * FROM Table1 WHERE column1 = $x and column2 = $(a + b)")
```

```python
db.execute("SELECT * FROM Table1 WHERE column1 = $$value")
```

--------------------------------

### Customizing Connection Behavior with db.on_connect

Source: https://github.com/ponyorm/pony-doc/blob/master/database.rst

Shows how to use the `@db.on_connect` decorator to execute custom queries when a new database connection is established. This is useful for setting connection-specific configurations like pragmas.

```python
db = Database()

# entities declaration

@db.on_connect(provider='sqlite')
def sqlite_case_sensitivity(db, connection):
    cursor = connection.cursor()
    cursor.execute('PRAGMA case_sensitive_like = OFF')

db.bind(**options)
db.generate_mapping(create_tables=True)
```

--------------------------------

### Extracting a Single Entity with `get()`

Source: https://github.com/ponyorm/pony-doc/blob/master/api_reference.rst

The `get()` method retrieves a single entity instance based on specified parameters. It returns the object if found, `None` if not, and raises `MultipleObjectsFoundError` if multiple objects match.

```python
select(o for o in Order if o.id == 123).get()
```

--------------------------------

### Pony ORM Database Object Methods

Source: https://github.com/ponyorm/pony-doc/blob/master/database.rst

Provides an overview of key methods and attributes for the Pony ORM Database object, essential for database interaction and schema management.

```APIDOC
Database:
  __init__()
    Creates a new Database object.

  Entity
    Attribute representing the base class for entity definitions.

  bind(provider, **kwargs)
    Binds the Database object to a specific database.
    Parameters:
      provider (str): The name of the database provider (e.g., 'sqlite', 'postgres').
      **kwargs: Provider-specific connection parameters (e.g., filename, user, password, host, database).

  generate_mapping(create_tables=False, check_tables=False)
    Generates the database schema based on defined entities.
    Parameters:
      create_tables (bool): If True, creates tables if they do not exist.
      check_tables (bool): If True, checks for existing tables and raises an error if they are missing.

  select(lambda: ...)
    Executes a database query.

  commit()
    Commits the current transaction.

  rollback()
    Rolls back the current transaction.
```

--------------------------------

### DatabaseSessionIsOver Exception Example

Source: https://github.com/ponyorm/pony-doc/blob/master/transactions.rst

Shows an example of the DatabaseSessionIsOver exception, which occurs when attempting to access object attributes outside the scope of an active database session. This highlights the importance of keeping database operations within a db_session.

```text
DatabaseSessionIsOver: Cannot load attribute Customer[3].name: the database session is over
```

--------------------------------

### Automatic Transaction Commit Example

Source: https://github.com/ponyorm/pony-doc/blob/master/transactions.rst

An example demonstrating how PonyORM automatically commits a transaction when leaving the @db_session scope if no exceptions occur. It also shows that the database connection is returned to the pool and the Identity Map cache is cleared.

```python
from pony.orm import *

@db_session
def func():
    # a new transaction is started
    p = Product[123]
    p.price += 10
    # commit() will be done automatically
    # database session cache will be cleared automatically
    # database connection will be returned to the pool
```