Submit Malware Report

https://pypi.org/project/pymongosql/
Admin
A guide providing instructions and information for submitting malware reports.
Tokens:12,003
Snippets:172
Trust Score:10
Update:3 months ago
Show doc for...
Tokens:
Raw
### Install PyMongoSQL from Source

Source: https://pypi.org/project/pymongosql/0.7.0

Clone the repository and install PyMongoSQL in editable mode.

```bash
git clone https://github.com/passren/PyMongoSQL.git
cd PyMongoSQL
pip install -e .
```

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

### Install PyMongoSQL

Source: https://pypi.org/project/pymongosql

Install PyMongoSQL using pip. For source installation, clone the repository and use pip install -e.

```bash
pip install pymongosql
```

```bash
git clone https://github.com/passren/PyMongoSQL.git
cd PyMongoSQL
pip install -e .
```

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

### Install PyMongoSQL

Source: https://pypi.org/project/pymongosql

Install PyMongoSQL using pip. This command should be run on the Superset app server.

```bash
pip install pymongosql
```

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

### Nested Field and Array Access Examples

Source: https://pypi.org/project/pymongosql/0.7.0

Demonstrates accessing single-level, multi-level, and array elements within nested document structures for querying.

```SQL
profile.name
```

```SQL
settings.theme
```

```SQL
account.profile.name
```

```SQL
config.database.host
```

```SQL
items[0].name
```

```SQL
orders[1].total
```

```SQL
WHERE customer.profile.age > 18 AND orders[0].status = 'paid'
```

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

### Aggregate with OR Conditions

Source: https://pypi.org/project/pymongosql

Use aggregate functions with OR conditions in the WHERE clause to count records that meet either of the specified criteria. This example counts users based on age ranges.

```python
cursor.execute("SELECT COUNT(*) AS cnt FROM users WHERE age < 26 OR age > 40")
```

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

### Aggregate with WHERE Clause

Source: https://pypi.org/project/pymongosql

Combine aggregate functions with a WHERE clause to filter records before aggregation. This example counts users based on active status and age.

```python
cursor.execute("SELECT COUNT(*) AS total FROM users WHERE active = true AND age > 30")
```

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

### Count Total Records with COUNT(*)

Source: https://pypi.org/project/pymongosql

Use COUNT(*) to get the total number of records in a collection. The result is aliased as 'total'.

```python
cursor.execute("SELECT COUNT(*) AS total FROM users")
row = cursor.fetchone()
print(f"Total users: {row[0]}")
```

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

### Basic SELECT Statements

Source: https://pypi.org/project/pymongosql/0.7.0

Demonstrates basic field selection, wildcards, aliases, nested fields, and array access in SELECT statements.

```SQL
SELECT name, age FROM users
```

```SQL
SELECT * FROM products
```

```SQL
SELECT name AS user_name, age AS user_age FROM users
```

```SQL
SELECT profile.name, profile.age FROM users
```

```SQL
SELECT items[0], items[1].name FROM orders
```

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

### Explain MongoDB Query Plans

Source: https://pypi.org/project/pymongosql

Prefix `SELECT` statements with `EXPLAIN` to inspect MongoDB query plans. Supported verbosities include `queryPlanner`, `executionStats`, and `allPlansExecution`. The output is flattened into `stage` and `details` columns.

```python
# Default verbosity: queryPlanner
cursor.execute("EXPLAIN SELECT * FROM users WHERE age > 21")
for stage, details in cursor.fetchall():
    print(stage, details)

# Request executionStats (actual timing, docs/keys examined) or allPlansExecution
cursor.execute("EXPLAIN (verbosity executionStats) SELECT * FROM users WHERE age > 21")
```

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

### Basic PyMongoSQL Connection and Query

Source: https://pypi.org/project/pymongosql

Connect to a MongoDB instance and execute a simple SELECT query using the default cursor.

```python
from pymongosql import connect

# Connect to MongoDB
connection = connect(
    host="mongodb://localhost:27017",
    database="database"
)

cursor = connection.cursor()
cursor.execute('SELECT name, email FROM users WHERE age > 25')
print(cursor.fetchall())
```

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

### CREATE VIEW with Lookup Pipeline

Source: https://pypi.org/project/pymongosql/0.7.0

Create a MongoDB view with a $lookup (join) pipeline using SQL syntax. The pipeline must be a valid JSON array string enclosed in single quotes.

```python
import json

# Create a view with a $lookup (join) pipeline
pipeline = json.dumps([
    {"\$lookup": {"from": "orders", "localField": "_id", "foreignField": "user_id", "as": "user_orders"}}
])
cursor.execute(f"CREATE VIEW users_with_orders ON users AS '{pipeline}'")

# Query the view like any collection
cursor.execute("SELECT * FROM active_users")
```

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

### PyMongoSQL Cursor vs DictCursor Access

Source: https://pypi.org/project/pymongosql

Demonstrates accessing data from default Cursor (tuple) and DictCursor (dictionary).

```python
cursor = connection.cursor()
cursor.execute('SELECT name, email FROM users')
row = cursor.fetchone()
print(row[0])  # Access by index
```

```python
from pymongosql.cursor import DictCursor

cursor = connection.cursor(DictCursor)
cursor.execute('SELECT name, email FROM users')
row = cursor.fetchone()
print(row['name'])  # Access by column name
```

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

### Create MongoDB Views with SQL

Source: https://pypi.org/project/pymongosql

Use `CREATE VIEW` with a JSON pipeline to define MongoDB views. The pipeline must be a valid JSON array string enclosed in single quotes. This maps to `db.command({"create": view_name, "viewOn": collection, "pipeline": [...]})`.

```python
import json

# Create a view with a filter pipeline
pipeline = json.dumps([{" $match": {"active": True}}])
cursor.execute(f"CREATE VIEW active_users ON users AS '{pipeline}'")

# Create a view with a $lookup (join) pipeline
pipeline = json.dumps([
    {" $lookup": {"from": "orders", "localField": "_id", "foreignField": "user_id", "as": "user_orders"}}
])
cursor.execute(f"CREATE VIEW users_with_orders ON users AS '{pipeline}'")

# Query the view like any collection
cursor.execute("SELECT * FROM active_users")
```

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

### Connect to MongoDB with Superset Mode

Source: https://pypi.org/project/pymongosql/0.7.0

Connect to your MongoDB instance using the connection URI with superset mode. This is used for integrating with Apache Superset.

```text
mongodb://username:password@host:port/database?mode=superset
```

```text
mongodb+srv://username:password@host/database?mode=superset
```

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

### SQL Operations to MongoDB Commands

Source: https://pypi.org/project/pymongosql/0.7.0

This table outlines the direct translation of common SQL operations into their corresponding MongoDB commands and PyMongo methods.

```SQL
SELECT ... FROM col
```

```MongoDB
{find: col, projection: {...}}
```

```SQL
SELECT ... FROM col WHERE ...
```

```MongoDB
{find: col, filter: {...}}
```

```SQL
SELECT ... ORDER BY col ASC/DESC
```

```MongoDB
{find: ..., sort: {col: 1/-1}}
```

```SQL
SELECT ... LIMIT n
```

```MongoDB
{find: ..., limit: n}
```

```SQL
SELECT ... OFFSET n
```

```MongoDB
{find: ..., skip: n}
```

```SQL
SELECT * FROM col.aggregate(...)
```

```MongoDB
collection.aggregate(pipeline)
```

```SQL
INSERT INTO col ...
```

```MongoDB
{insert: col, documents: [...]}
```

```SQL
UPDATE col SET ... WHERE ...
```

```MongoDB
{update: col, updates: [{q: filter, u: {$set: {...}}, multi: true}]}
```

```SQL
DELETE FROM col WHERE ...
```

```MongoDB
{delete: col, deletes: [{q: filter, limit: 0}]}
```

```SQL
CREATE VIEW v ON col AS '[...]'
```

```MongoDB
{create: v, viewOn: col, pipeline: [...]}
```

```SQL
DROP VIEW v
```

```MongoDB
{drop: v}
```

```SQL
EXPLAIN <select>
```

```MongoDB
{explain: <find|aggregate cmd>, verbosity: "queryPlanner"}
```

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

### Accessing Tuple Cursor Results

Source: https://pypi.org/project/pymongosql/0.7.0

Demonstrates how to access data from the default Cursor type, which returns results as tuples.

```python
cursor = connection.cursor()
cursor.execute('SELECT name, email FROM users')
row = cursor.fetchone()
print(row[0])  # Access by index
```

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

### PyMongoSQL Retry Configuration

Source: https://pypi.org/project/pymongosql

Configure retry mechanisms for transient system errors in PyMongoSQL operations. Requires the 'tenacity' package.

```python
connection = connect(
    host="mongodb://localhost:27017/database",
    retry_enabled=False,    # default: False
    retry_attempts=3,       # default: 3
    retry_wait_min=0.1,     # default: 0.1 seconds
    retry_wait_max=1.0,     # default: 1.0 seconds
)
```

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

### CREATE VIEW with Filter Pipeline

Source: https://pypi.org/project/pymongosql/0.7.0

Create a MongoDB view with a filter pipeline using SQL syntax. The pipeline must be a valid JSON array string enclosed in single quotes.

```python
import json

# Create a view with a filter pipeline
pipeline = json.dumps([{"\$match": {"active": True}}])
cursor.execute(f"CREATE VIEW active_users ON users AS '{pipeline}'")
```

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

### Manage ACID Transactions with PyMongoSQL

Source: https://pypi.org/project/pymongosql

Utilize `begin()`, `commit()`, and `rollback()` for ACID-compliant transactions. Ensure MongoDB is running as a replica set or sharded cluster, as standalone servers do not support transactions.

```python
from pymongosql import connect

connection = connect(host="mongodb://localhost:27017/database")

try:
    connection.begin()  # Start transaction
    
    cursor = connection.cursor()
    cursor.execute('UPDATE accounts SET balance = 100 WHERE id = ?', [1])
    cursor.execute('UPDATE accounts SET balance = 200 WHERE id = ?', [2])
    
    connection.commit()  # Commit all changes
    print("Transaction committed successfully")
except Exception as e:
    connection.rollback()  # Rollback on error
    print(f"Transaction failed: {e}")
finally:
    connection.close()
```

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

### Parameterized INSERT with PartiQL

Source: https://pypi.org/project/pymongosql/0.7.0

Use positional parameters with '?' placeholders for parameterized INSERT statements in PartiQL.

```sql
# Positional parameters using ? placeholders
cursor.execute(
    "INSERT INTO Music {'title': '?', 'artist': '?', 'year': '?'}",
    ["Song D", "Diana", 2020]
)
```

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

### Connect to MongoDB with Superset Mode

Source: https://pypi.org/project/pymongosql

Use this connection URI to connect to your MongoDB instance when using PyMongoSQL with Apache Superset. Ensure the 'mode=superset' parameter is included.

```text
mongodb://username:password@host:port/database?mode=superset
```

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

### Connect to MongoDB Atlas with Superset Mode

Source: https://pypi.org/project/pymongosql

Use this connection URI for MongoDB Atlas instances when integrating with Apache Superset. The 'mode=superset' parameter is essential.

```text
mongodb+srv://username:password@host/database?mode=superset
```

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

### Parameterized INSERT (PartiQL-Style)

Source: https://pypi.org/project/pymongosql

Performs a parameterized INSERT operation using PartiQL-style object literals and positional parameters (?). This is recommended for security and performance when inserting dynamic data.

```python
# Positional parameters using ? placeholders
cursor.execute(
    "INSERT INTO Music {'title': '?', 'artist': '?', 'year': '?'}",
    ["Song D", "Diana", 2020]
)

```

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

### INSERT Multiple Documents with PartiQL Bag Syntax

Source: https://pypi.org/project/pymongosql/0.7.0

Insert multiple documents into a MongoDB collection using PartiQL's bag syntax.

```sql
cursor.execute(
    "INSERT INTO Music << {'title': 'Song B', 'artist': 'Bob'}, {'title': 'Song C', 'artist': 'Charlie'} >>"
)
```

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

### WHERE Clause Filtering

Source: https://pypi.org/project/pymongosql/0.7.0

Illustrates various filtering conditions in WHERE clauses, including equality, comparisons, logical operators, nested fields, and array access.

```SQL
WHERE name = 'John'
```

```SQL
WHERE age > 25
```

```SQL
WHERE price <= 100.0
```

```SQL
WHERE age > 18 AND status = 'active'
```

```SQL
WHERE age < 30 OR role = 'admin'
```

```SQL
WHERE profile.status = 'active'
```

```SQL
WHERE items[0].price > 100
```

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

### Parameterized Query with Positional Parameters

Source: https://pypi.org/project/pymongosql/0.7.0

Execute a query using positional parameters (?) for safe value substitution, protecting against injection attacks.

```python
from pymongosql import connect

connection = connect(host="mongodb://localhost:27017/database")
cursor = connection.cursor()

cursor.execute(
    'SELECT name, email FROM users WHERE age > ? AND status = ?',
    [25, 'active']
)
```

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

### PyMongoSQL Connection with Authentication and Parameterized Query

Source: https://pypi.org/project/pymongosql

Connect to MongoDB using a connection string with authentication and execute a query with positional parameters.

```python
from pymongosql import connect

# Connect with authentication
connection = connect(
    host="mongodb://username:password@localhost:27017/database?authSource=admin"
)

cursor = connection.cursor()
cursor.execute('SELECT * FROM products WHERE category = ?', ['Electronics'])

for row in cursor:
    print(row)
```

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

### Accessing DictCursor Results

Source: https://pypi.org/project/pymongosql/0.7.0

Demonstrates how to access data from DictCursor, which returns results as dictionaries.

```python
from pymongosql.cursor import DictCursor

cursor = connection.cursor(DictCursor)
cursor.execute('SELECT name, email FROM users')
row = cursor.fetchone()
print(row['name'])  # Access by column name
```

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

### Parameterized INSERT VALUES (SQL Standard)

Source: https://pypi.org/project/pymongosql

Performs parameterized INSERT operations using standard SQL INSERT VALUES syntax with both positional (?) and named (:name) parameters. This is the recommended approach for inserting dynamic data securely.

```python
# Positional parameters (?)
cursor.execute(
    "INSERT INTO Music (title, artist, year) VALUES (?, ?, ?)",
    ["Song H", "Henry", 2025]
)

# Named parameters (:name)
cursor.execute(
    "INSERT INTO Music (title, artist) VALUES (:title, :artist)",
    {"title": "Song I", "artist": "Iris"}
)

```

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

### Insert Multiple Documents (PartiQL-Style Bag Syntax)

Source: https://pypi.org/project/pymongosql

Inserts multiple documents into a MongoDB collection using PartiQL-style bag syntax. This allows for efficient insertion of several documents at once.

```python
cursor.execute(
    "INSERT INTO Music << {'title': 'Song B', 'artist': 'Bob'}, {'title': 'Song C', 'artist': 'Charlie'} >>"
)

```

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

### Parameterized DELETE

Source: https://pypi.org/project/pymongosql/0.7.0

Use positional parameters for parameterized DELETE statements.

```sql
# Positional parameters using ? placeholders
cursor.execute(
    "DELETE FROM Music WHERE artist = ? AND year < ?",
    ["Charlie", 2021]
)
```

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

### PyMongoSQL Context Manager Usage

Source: https://pypi.org/project/pymongosql

Utilize context managers for automatic connection and cursor management, ensuring resources are properly closed.

```python
from pymongosql import connect

with connect(host="mongodb://localhost:27017/database") as conn:
    with conn.cursor() as cursor:
        cursor.execute('SELECT COUNT(*) as total FROM users')
        result = cursor.fetchone()
        print(f"Total users: {result[0]}")
```

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

### Parameterized INSERT VALUES with SQL

Source: https://pypi.org/project/pymongosql/0.7.0

Use positional or named parameters for parameterized INSERT VALUES statements in SQL.

```sql
# Positional parameters (?)
cursor.execute(
    "INSERT INTO Music (title, artist, year) VALUES (?, ?, ?)",
    ["Song H", "Henry", 2025]
)

# Named parameters (:name)
cursor.execute(
    "INSERT INTO Music (title, artist) VALUES (:title, :artist)",
    {"title": "Song I", "artist": "Iris"}
)
```

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

### Execute Unqualified Aggregation Pipeline

Source: https://pypi.org/project/pymongosql/0.7.0

Executes a MongoDB aggregation pipeline at the database level, allowing for options like 'allowDiskUse'. The pipeline and options must be valid JSON strings.

```Python
cursor.execute(
    "SELECT * FROM aggregate('[{\"$\"match\": {\"status\": \"active\"}}]', '{\"allowDiskUse\": true}')"
)
results = cursor.fetchall()
```

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

### PyMongoSQL with DictCursor for Dictionary Results

Source: https://pypi.org/project/pymongosql

Use DictCursor to fetch results as dictionaries, allowing access to columns by name.

```python
from pymongosql import connect
from pymongosql.cursor import DictCursor

with connect(host="mongodb://localhost:27017/database") as conn:
    with conn.cursor(DictCursor) as cursor:
        cursor.execute('SELECT COUNT(*) as total FROM users')
        result = cursor.fetchone()
        print(f"Total users: {result['total']}")
```

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

### Drop MongoDB Views with SQL

Source: https://pypi.org/project/pymongosql

Use `DROP VIEW` to remove a MongoDB view. This maps to `db.command({"drop": view_name})`.

```python
cursor.execute("DROP VIEW active_users")
```

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

### PyMongoSQL Query with Named Parameters

Source: https://pypi.org/project/pymongosql

Execute queries using named parameters for safer and more readable value substitution.

```python
from pymongosql import connect

connection = connect(host="mongodb://localhost:27017/database")
cursor = connection.cursor()

cursor.execute(
    'SELECT name, email FROM users WHERE age > :age AND status = :status',
    {'age': 25, 'status': 'active'}
)
```

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

### Execute MongoDB Aggregate Function (Collection-Specific)

Source: https://pypi.org/project/pymongosql

Executes a native MongoDB aggregation pipeline on a specific collection using SQL-like syntax. The pipeline and options must be valid JSON strings.

```python
cursor.execute(
    "SELECT * FROM users.aggregate('[{\"$\"$match\": {\"age\": {\"$\"$gt\": 25}}}, {\"$\"$group\": {\"_id\": \"$\"$city\", \"count\": {\"$\"$sum\": 1}}}]', '{}')"
)
results = cursor.fetchall()

```

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

### Execute Collection-Specific Aggregation Pipeline

Source: https://pypi.org/project/pymongosql/0.7.0

Executes a MongoDB aggregation pipeline on a specific collection (e.g., 'users') with match and group stages. The pipeline and options must be valid JSON strings.

```Python
cursor.execute(
    "SELECT * FROM users.aggregate('[{\"$\"match\": {\"age\": {\"$\": \"gt\"}}, 25]}}, {\"$\"group\": {\"_id\": \"$city\", \"count\": {\"$\": \"sum\"}, 1}}}]', '{}')"
)
results = cursor.fetchall()
```

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

### Multiple Aggregate Functions

Source: https://pypi.org/project/pymongosql

Calculate count, average, minimum, and maximum values for a field using multiple aggregate functions in a single query.

```python
cursor.execute("SELECT COUNT(*) AS cnt, AVG(price) AS avg_price, MIN(price) AS cheapest, MAX(price) AS priciest FROM products")
```

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

### INSERT Single Document with PartiQL

Source: https://pypi.org/project/pymongosql/0.7.0

Use PartiQL-style object literals to insert a single document into a MongoDB collection.

```sql
cursor.execute(
    "INSERT INTO Music {'title': 'Song A', 'artist': 'Alice', 'year': 2021}"
)
```

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

### Parameterized UPDATE

Source: https://pypi.org/project/pymongosql

Performs a parameterized UPDATE operation using positional parameters (?). This is the recommended method for updating documents with dynamic values to prevent injection vulnerabilities.

```python
# Positional parameters using ? placeholders
cursor.execute(
    "UPDATE Music SET price = ?, stock = ? WHERE artist = ?",
    [24.99, 50, "Bob"]
)

```

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

### Parameterized DELETE

Source: https://pypi.org/project/pymongosql

Performs a parameterized DELETE operation using positional parameters (?). This is the recommended method for deleting documents with dynamic criteria to ensure security.

```python
# Positional parameters using ? placeholders
cursor.execute(
    "DELETE FROM Music WHERE artist = ? AND year < ?",
    ["Charlie", 2021]
)

```

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

### SQL Clauses to MongoDB Query Components

Source: https://pypi.org/project/pymongosql/0.7.0

This table maps common SQL query clauses to their MongoDB equivalents for constructing queries.

```SQL
SELECT col1, col2
```

```MongoDB
projection: {col1: 1, col2: 1}
```

```SQL
SELECT *
```

```MongoDB
_(no projection)_
```

```SQL
SELECT col AS alias
```

```MongoDB
Column alias applied in result set
```

```SQL
FROM collection
```

```MongoDB
find: "collection"
```

```SQL
ORDER BY col ASC
```

```MongoDB
sort: {col: 1}
```

```SQL
ORDER BY col DESC
```

```MongoDB
sort: {col: -1}
```

```SQL
LIMIT n
```

```MongoDB
limit: n
```

```SQL
OFFSET n
```

```MongoDB
skip: n
```

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

### UPDATE Multiple Fields

Source: https://pypi.org/project/pymongosql/0.7.0

Update multiple fields in documents that match a WHERE clause.

```sql
cursor.execute(
    "UPDATE Music SET price = 19.99, available = true WHERE artist = 'Alice'"
)
```

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

### Project Specific Fields from Aggregation Results

Source: https://pypi.org/project/pymongosql/0.7.0

Selects specific fields (_id, total) from the results of a MongoDB aggregation pipeline. This projection is applied after the aggregation completes.

```Python
# Select specific fields from aggregation results
cursor.execute(
    "SELECT _id, total FROM users.aggregate('[{\"$\"group\": {\"_id\": \"$city\", \"total\": {\"$\": \"sum\"}, 1}}]', '{}')"
)
```

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

### Project Specific Fields from Aggregation Results

Source: https://pypi.org/project/pymongosql

Selects specific fields from the results of a MongoDB aggregation pipeline. This is achieved by specifying the desired fields in the SELECT clause.

```python
# Select specific fields from aggregation results
cursor.execute(
    "SELECT _id, total FROM users.aggregate('[{\"$\"$group\": {\"_id\": \"$\"$city\", \"total\": {\"$\"$sum\": 1}}}]', '{}')"
)

```

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

### Execute MongoDB Aggregate Function (Database-Level)

Source: https://pypi.org/project/pymongosql

Executes a native MongoDB aggregation pipeline at the database level. This is useful for aggregations that do not directly map to a single collection. The pipeline and options must be valid JSON strings.

```python
cursor.execute(
    "SELECT * FROM aggregate('[{\"$\"$match\": {\"status\": \"active\"}}]', '{\"allowDiskUse\": true}')"
)
results = cursor.fetchall()

```

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

### Parameterized UPDATE

Source: https://pypi.org/project/pymongosql/0.7.0

Use positional parameters for parameterized UPDATE statements.

```sql
# Positional parameters using ? placeholders
cursor.execute(
    "UPDATE Music SET price = ?, stock = ? WHERE artist = ?",
    [24.99, 50, "Bob"]
)
```

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

### Convert String to Datetime with str_to_datetime

Source: https://pypi.org/project/pymongosql

Use str_to_datetime to convert ISO 8601 or custom formatted strings to Python datetime objects. Ensure the datetime values are UTC timezone.

```python
cursor.execute("SELECT * FROM events WHERE created_at >= str_to_datetime('2024-01-15T10:30:00Z')")
```

```python
cursor.execute("SELECT * FROM events WHERE created_at < str_to_datetime('03/15/2024', '%m/%d/%Y')")
```

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

### DML Mapping Details

Source: https://pypi.org/project/pymongosql/0.7.0

This table provides details on how SQL Data Manipulation Language (DML) statements are handled in MongoDB.

```SQL
INSERT INTO col VALUE {...}
```

```MongoDB
Single document insert
```

```SQL
INSERT INTO col VALUE << {...}, {...} >>
```

```MongoDB
Multi-document insert
```

```SQL
INSERT INTO col (c1, c2) VALUES (v1, v2)
```

```MongoDB
Columns and values zipped into document
```

```SQL
UPDATE col SET f1 = v1
```

```MongoDB
{$set: {f1: v1}} with multi: true
```

```SQL
DELETE FROM col
```

```MongoDB
{q: {}, limit: 0}
```

```SQL
DELETE FROM col WHERE ...
```

```MongoDB
{q: filter, limit: 0}
```

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

### Nested Field and Array Access

Source: https://pypi.org/project/pymongosql/0.7.0

This section demonstrates how SQL syntax for accessing nested fields and array elements maps to MongoDB's dot notation.

```SQL
profile.name
```

```MongoDB
profile.name
```

```SQL
account.profile.name
```

```MongoDB
account.profile.name
```

```SQL
items[0].name
```

```MongoDB
items.0.name
```

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

### UPDATE with Logical Operators

Source: https://pypi.org/project/pymongosql/0.7.0

Update documents using logical operators (AND) in the WHERE clause.

```sql
cursor.execute(
    "UPDATE Music SET price = 9.99 WHERE year = 2020 AND stock > 5"
)
```

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

### Update Documents with Logical Operators

Source: https://pypi.org/project/pymongosql

Updates documents based on conditions involving logical operators (AND, OR, etc.) in the WHERE clause. This enables complex filtering for updates.

```python
cursor.execute(
    "UPDATE Music SET price = 9.99 WHERE year = 2020 AND stock > 5"
)

```

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

### Insert Single Document (PartiQL-Style)

Source: https://pypi.org/project/pymongosql

Inserts a single document into a MongoDB collection using PartiQL-style object literals. This is a concise way to insert data with specific fields.

```python
cursor.execute(
    "INSERT INTO Music {'title': 'Song A', 'artist': 'Alice', 'year': 2021}"
)

```

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

### Sorting and Limiting Results

Source: https://pypi.org/project/pymongosql/0.7.0

Applies sorting by one or more fields in ascending or descending order, and limits the number of returned records.

```SQL
ORDER BY name ASC, age DESC
```

```SQL
LIMIT 10
```

```SQL
ORDER BY created_at DESC LIMIT 5
```

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

### DELETE with Logical Operators

Source: https://pypi.org/project/pymongosql/0.7.0

Delete documents using logical operators (AND) in the WHERE clause.

```sql
cursor.execute(
    "DELETE FROM Music WHERE year = 2019 AND available = false"
)
```

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

### Sort and Limit Aggregation Results

Source: https://pypi.org/project/pymongosql

Sorts and limits the results of a MongoDB aggregation pipeline using ORDER BY and LIMIT clauses. Note that these clauses are applied in Python after the aggregation executes on MongoDB.

```python
# Sort and limit aggregation results
cursor.execute(
    "SELECT * FROM products.aggregate('[{\"$\"$match\": {\"category\": \"Electronics\"}}]', '{}') ORDER BY price DESC LIMIT 10"
)

```

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

### UPDATE with WHERE Clause

Source: https://pypi.org/project/pymongosql/0.7.0

Update documents in a MongoDB collection that match a specific WHERE clause.

```sql
cursor.execute("UPDATE Music SET price = 14.99 WHERE year < 2020")
```

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

### Check Updated Row Count

Source: https://pypi.org/project/pymongosql/0.7.0

Execute an UPDATE statement and then check the `cursor.rowcount` attribute to determine how many documents were affected.

```python
cursor.execute("UPDATE Music SET available = false WHERE year = 2020")
print(f"Updated {cursor.rowcount} documents")
```

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

### INSERT Multiple Rows with SQL VALUES

Source: https://pypi.org/project/pymongosql/0.7.0

Insert multiple rows into a MongoDB collection using standard SQL INSERT VALUES syntax.

```sql
cursor.execute(
    "INSERT INTO Music (title, artist, year) VALUES ('Song F', 'Frank', 2023), ('Song G', 'Grace', 2024)"
)
```

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

### UPDATE All Documents

Source: https://pypi.org/project/pymongosql/0.7.0

Update all documents in a MongoDB collection using a simple SQL UPDATE statement.

```sql
cursor.execute("UPDATE Music SET available = false")
```

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

### Convert String to Timestamp with str_to_timestamp

Source: https://pypi.org/project/pymongosql

Use str_to_timestamp to convert ISO 8601 or custom formatted strings to BSON Timestamp objects. Ensure the timestamp values are UTC timezone.

```python
cursor.execute("SELECT * FROM logs WHERE timestamp > str_to_timestamp('2024-01-15T00:00:00Z')")
```

```python
cursor.execute("SELECT * FROM logs WHERE timestamp < str_to_timestamp('01/15/2024', '%m/%d/%Y')")
```

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

### DELETE with WHERE Clause

Source: https://pypi.org/project/pymongosql/0.7.0

Delete documents from a MongoDB collection that match a specific WHERE clause.

```sql
cursor.execute("DELETE FROM Music WHERE year < 2020")
```

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

### Insert Multiple Rows (SQL Standard)

Source: https://pypi.org/project/pymongosql

Inserts multiple rows into a MongoDB collection using standard SQL INSERT VALUES syntax. This allows for efficient insertion of multiple records in a single statement.

```python
cursor.execute(
    "INSERT INTO Music (title, artist, year) VALUES ('Song F', 'Frank', 2023), ('Song G', 'Grace', 2024)"
)

```

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

### Update Documents with WHERE Clause

Source: https://pypi.org/project/pymongosql

Updates documents in a MongoDB collection that match a specified condition in the WHERE clause. This allows for targeted updates.

```python
cursor.execute("UPDATE Music SET price = 14.99 WHERE year < 2020")

```

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

### Insert Single Row with Column List (SQL Standard)

Source: https://pypi.org/project/pymongosql

Inserts a single row into a MongoDB collection using standard SQL INSERT VALUES syntax with a specified column list. This provides explicit control over which fields are inserted.

```python
cursor.execute(
    "INSERT INTO Music (title, artist, year) VALUES ('Song E', 'Eve', 2022)"
)

```

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

### WHERE Operators to MongoDB Filter Operators

Source: https://pypi.org/project/pymongosql/0.7.0

This table shows how SQL WHERE clause operators are translated into MongoDB filter expressions.

```SQL
field = value
```

```MongoDB
{field: value}
```

```SQL
field != value
```

```MongoDB
{field: {$ne: value}}
```

```SQL
field > value
```

```MongoDB
{field: {$gt: value}}
```

```SQL
field >= value
```

```MongoDB
{field: {$gte: value}}
```

```SQL
field < value
```

```MongoDB
{field: {$lt: value}}
```

```SQL
field <= value
```

```MongoDB
{field: {$lte: value}}
```

```SQL
field LIKE 'pat%'
```

```MongoDB
{field: {$regex: "pat.*"}}
```

```SQL
field IN (a, b, c)
```

```MongoDB
{field: {$in: [a, b, c]}}
```

```SQL
field NOT IN (a, b)
```

```MongoDB
{field: {$nin: [a, b]}}
```

```SQL
field BETWEEN a AND b
```

```MongoDB
{$and: [{field: {$gte: a}}, {field: {$lte: b}}]}
```

```SQL
field IS NULL
```

```MongoDB
{field: {$eq: null}}
```

```SQL
field IS NOT NULL
```

```MongoDB
{field: {$ne: null}}
```

```SQL
cond1 AND cond2
```

```MongoDB
{$and: [filter1, filter2]}
```

```SQL
cond1 OR cond2
```

```MongoDB
{$or: [filter1, filter2]}
```

```SQL
NOT cond
```

```MongoDB
{$not: filter}
```

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

### INSERT Single Row with SQL VALUES

Source: https://pypi.org/project/pymongosql/0.7.0

Insert a single row into a MongoDB collection using standard SQL INSERT VALUES syntax with a column list.

```sql
cursor.execute(
    "INSERT INTO Music (title, artist, year) VALUES ('Song E', 'Eve', 2022)"
)
```

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

### Delete Documents with Logical Operators

Source: https://pypi.org/project/pymongosql

Deletes documents based on conditions involving logical operators (AND, OR, etc.) in the WHERE clause. This enables complex filtering for deletion operations.

```python
cursor.execute(
    "DELETE FROM Music WHERE year = 2019 AND available = false"
)

```

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

### Update All Documents

Source: https://pypi.org/project/pymongosql

Updates all documents in a MongoDB collection by setting a specific field to a new value. Use with caution as it affects all records.

```python
cursor.execute("UPDATE Music SET available = false")

```

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

### Check Deleted Row Count

Source: https://pypi.org/project/pymongosql

Checks the number of documents deleted by the previous DELETE statement using `cursor.rowcount`. This is useful for verifying the impact of a delete operation.

```python
cursor.execute("DELETE FROM Music WHERE available = false")
print(f"Deleted {cursor.rowcount} documents")

```

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

### DELETE All Documents

Source: https://pypi.org/project/pymongosql/0.7.0

Delete all documents from a MongoDB collection using a simple SQL DELETE statement.

```sql
cursor.execute("DELETE FROM Music")
```

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

### Check Updated Row Count

Source: https://pypi.org/project/pymongosql

Checks the number of documents updated by the previous UPDATE statement using `cursor.rowcount`. This is useful for verifying the impact of an update operation.

```python
cursor.execute("UPDATE Music SET available = false WHERE year = 2020")
print(f"Updated {cursor.rowcount} documents")

```

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

### Filter Aggregation Results

Source: https://pypi.org/project/pymongosql

Filters the results of a MongoDB aggregation pipeline using a WHERE clause. Note that post-aggregation filtering is applied in Python after the aggregation executes on MongoDB.

```python
# Filter aggregation results
cursor.execute(
    "SELECT * FROM users.aggregate('[{\"$\"$group\": {\"_id\": \"$\"$city\", \"total\": {\"$\"$sum\": 1}}}]', '{}') WHERE total > 100"
)

```

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

### UPDATE Nested Fields

Source: https://pypi.org/project/pymongosql/0.7.0

Update nested fields within documents using dot notation in the SET clause.

```sql
cursor.execute(
    "UPDATE Music SET details.publisher = 'XYZ Records' WHERE title = 'Song A'"
)
```

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

### Delete All Documents

Source: https://pypi.org/project/pymongosql

Deletes all documents from a MongoDB collection. Use with extreme caution as this operation is irreversible.

```python
cursor.execute("DELETE FROM Music")

```

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

### Delete Documents with WHERE Clause

Source: https://pypi.org/project/pymongosql

Deletes documents from a MongoDB collection that match a specified condition in the WHERE clause. This allows for targeted deletion of records.

```python
cursor.execute("DELETE FROM Music WHERE year < 2020")

```

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

### Update Multiple Fields in Documents

Source: https://pypi.org/project/pymongosql

Updates multiple fields within documents in a MongoDB collection that match a specified condition. This is useful for batch updates of related fields.

```python
cursor.execute(
    "UPDATE Music SET price = 19.99, available = true WHERE artist = 'Alice'"
)

```

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

### Update Nested Fields

Source: https://pypi.org/project/pymongosql

Updates fields within nested documents in a MongoDB collection. This is achieved by specifying the path to the nested field using dot notation.

```python
cursor.execute(
    "UPDATE Music SET details.publisher = 'XYZ Records' WHERE title = 'Song A'"
)

```
Tokens:
Raw