Executing queries

This page shows how to run SurrealQL statements from the Python SDK and how to read what they return. It starts with query(), which most code uses, and then covers the methods for more specific tasks: calling a function, sending several statements as one transaction, calling a DEFINE API endpoint, and running ISO GQL or GraphQL. To read and write records without writing SurrealQL, see Data manipulation.

API references

Running a query

db.query() takes one or more SurrealQL statements and returns a query builder. The builder sends nothing to the database until you run it:

• •

  .execute() returns a list with one result for each statement. A query with one statement gives a list with one entry.

• •

  .first() returns the result of the first statement, or None when the query has no statements.

On an asynchronous connection, await the builder or one of these methods. await db.query(...) is the same as await db.query(...).execute().

from surrealdb import Surreal

with Surreal("ws://localhost:8000") as db:
    db.signin({"username": "root", "password": "secret"})
    db.use("surrealdb", "docs")

    db.query("CREATE person:tobie SET name = 'Tobie', age = 35").execute()

    print(db.query("SELECT name FROM person").execute())
    print(db.query("SELECT name FROM person").first())

[[{'name': 'Tobie'}]]
[{'name': 'Tobie'}]

On a blocking connection, the builder never runs on its own. Call .execute() or .first() even for a statement whose result you do not need:

db.query("DELETE person WHERE age < 18").execute()

A builder that is never run sends nothing to the server. When Python discards such a builder, the SDK emits a RuntimeWarning that names the call and the line it was made on:

db.query("DELETE person WHERE age < 18")  # No .execute(), so nothing runs

RuntimeWarning: query() was called at app.py:12 but its builder was never executed (.execute(), .rows(), or iterating it), so nothing was sent to the server.

On an asynchronous connection, the warning says that the builder was never awaited. A blocking builder also refuses len() and indexing with a TypeError, so code that uses the builder as if it were the result fails at that line.

Binding variables

Pass a dictionary as the second argument, and refer to each key in the query as $name. The SDK sends the values separately from the query text, so a value cannot change the meaning of the query. Use variables for every value that comes from outside your code.

people = db.query(
    "SELECT name, age FROM person WHERE age > $min_age",
    {"min_age": 30},
).first()

print(people)

[{'age': 35, 'name': 'Tobie'}]

A variable can hold any value the SDK can send, including a RecordID and the other value types:

from surrealdb import RecordID

name = db.query(
    "SELECT VALUE name FROM ONLY $person",
    {"person": RecordID("person", "tobie")},
).first()

print(name)

Tobie

Reading the results of several statements

A query can hold several statements, separated by ;. .execute() returns one result for each statement, in order, so you can unpack the list:

db.query("CREATE person:jaime SET name = 'Jaime', age = 28").execute()

names, counts = db.query("""
    SELECT VALUE name FROM person ORDER BY name;
    SELECT count() FROM person GROUP ALL;
""").execute()

print(names)
print(counts)

['Jaime', 'Tobie']
[{'count': 2}]

A SELECT returns a list of records, also when it names one record. Its result in .execute() therefore has two levels of lists: the outer list has one entry for each statement, and the inner list holds the records. .first() removes the outer list. ONLY makes the server return the record itself instead of a list, and VALUE returns the value of the field instead of an object:

db.query("SELECT name FROM person:tobie").execute()  # [[{'name': 'Tobie'}]]
db.query("SELECT name FROM person:tobie").first()  # [{'name': 'Tobie'}]
db.query("SELECT VALUE name FROM person:tobie").first()  # ['Tobie']
db.query("SELECT name FROM ONLY person:tobie").first()  # {'name': 'Tobie'}
db.query("SELECT VALUE name FROM ONLY person:tobie").first()  # 'Tobie'

When a statement fails, .execute() and .first() raise its error, such as a ThrownError for THROW. The server runs the whole query before the SDK raises, so the changes that the other statements make are applied. To get the status of each statement instead of an exception, use query_raw().

Mapping results onto a class

.into(cls) maps the results of the statements, in order, onto the fields of a dataclass, or onto the parameters of any class that takes keyword arguments. The class needs one field for each statement.

from dataclasses import dataclass

@dataclass
class Summary:
    names: list
    total: int

summary = db.query("""
    SELECT VALUE name FROM person ORDER BY name;
    RETURN count(SELECT * FROM person);
""").into(Summary)

print(summary)

Summary(names=['Jaime', 'Tobie'], total=2)

.into(cls, rows=True) maps each record of the first statement's result onto cls, and returns a list:

@dataclass
class Person:
    name: str
    age: int

people = db.query(
    "SELECT name, age FROM person ORDER BY name"
).into(Person, rows=True)

print(people)

[Person(name='Jaime', age=28), Person(name='Tobie', age=35)]

On a blocking connection, .into() runs the query and returns the mapped result. On an asynchronous connection, it returns a builder to await: await db.query(...).into(Person, rows=True).

A record with a field that the class does not accept raises UnexpectedResponseError, which names the fields on both sides. SELECT * returns the id field as well, so a class for it needs an id field. To map the records that the CRUD methods return, pass into= to them instead, as Data manipulation shows.

Reading the status of each statement

db.query_raw() returns the response to the query as a dictionary, in the form that the server sends it. Its result key holds one entry for each statement, with the status of the statement ("OK" or "ERR"), its time and its result. A statement that fails is reported in the response and is not raised, and its result is the error message.

raw = db.query_raw("SELECT VALUE name FROM person:tobie; THROW 'nope'")

for statement in raw["result"]:
    print(statement["status"], statement["result"])

OK ['Tobie']
ERR An error occurred: nope

Use query_raw() when some statements are allowed to fail and you need the results of the others, or when you want the time that each statement took.

Calling a function

db.run() calls a function by its name with a list of arguments, and returns the result of the function. It works for functions defined with DEFINE FUNCTION and for built-in functions. On an asynchronous connection, await it.

db.query("""
    DEFINE FUNCTION fn::greet($name: string) -> string {
        RETURN "Hello, " + $name;
    };
""").execute()

print(db.run("fn::greet", ["Tobie"]))
print(db.run("string::uppercase", ["tobie"]))

Hello, Tobie
TOBIE

Defining session variables

db.let() defines a variable that every later query on the session can use, until db.unset() removes it. db.set() is another name for let(). On a WebSocket connection, the SDK defines the variables again when it reconnects.

db.let("min_age", 30)

older = db.query("SELECT VALUE name FROM person WHERE age > $min_age")
print(older.first())

db.unset("min_age")

['Tobie']

A variable that you pass to query() takes the place of a session variable with the same name, for that query only. Against SurrealDB 2.x over a WebSocket, the session variable is used instead.

Sending statements as one transaction

db.batch() collects statements and sends them in one request, wrapped in BEGIN TRANSACTION and COMMIT TRANSACTION. All of the statements are applied, or none of them. Add statements with .query(), which takes the same arguments as db.query() and returns the batch, and send them with .execute():

db.query("""
    CREATE account:tobie SET balance = 100;
    CREATE account:jaime SET balance = 50;
""").execute()

results = (
    db.batch()
    .query("UPDATE account:tobie SET balance -= $amount", {"amount": 20})
    .query("UPDATE account:jaime SET balance += $amount")
    .execute()
)

print(results)

[
    [{'balance': 80, 'id': RecordID(table_name=account, record_id='tobie')}],
    [{'balance': 70, 'id': RecordID(table_name=account, record_id='jaime')}],
]

.execute() returns one result for each statement that you added. When a statement fails, it raises the error of that statement, and none of the statements are applied. These rules apply to a batch:

• •

  Variables are shared. The statements use one set of variables, so the second statement above uses the $amount from the first. A name that you bind twice must have the same value both times, or .query() raises ValueError.

• •

  Only the last statement can be a RETURN. A RETURN earlier in the batch raises ValueError. Bind the value with LET to use it in a later statement.

• •

  The batch is the transaction. A statement that begins, commits or cancels a transaction raises ValueError.

• •

  A batch needs a statement. .execute() on an empty batch raises ValueError.

Nothing stays open on the server between requests, so a batch works on every connection type, HTTP and embedded connections included, and against every supported server version, SurrealDB 2.x included. Transactions compares a batch with a client-side transaction.

Calling a DEFINE API endpoint

db.api() calls an endpoint defined with DEFINE API and returns an ApiResponse with the status, body and headers of the answer. Its ok property is True for a status from 200 to 299. Needs SurrealDB 3.0 or later.

db.query("""
    DEFINE API "/greet" FOR get THEN {
        { status: 200, body: { hello: "world" } }
    };
""").execute()

response = db.api("/greet")
print(response.status, response.body)

200 {'hello': 'world'}

Pass method, body, headers and query to describe the request, for example db.api("/orders", method="post", body={"item": "book"}). A path with no endpoint gives a response with status 404, not an exception. The SDK sends the call as a RETURN api::invoke(...) statement, so api() also works in sessions and transactions.

Running ISO GQL and GraphQL

db.gql() runs an ISO GQL query, such as a MATCH pattern, and returns a list with one result for each statement. Pass variables as a dictionary, as for query(). Needs SurrealDB 3.3 or later.

print(db.gql("MATCH (p:person) WHERE p.age > $min RETURN p.name", {"min": 30}))

[[{'p.name': 'Tobie'}]]

db.graphql() runs a GraphQL query and returns the answer as a dictionary, with a data key, and an errors key when the query has errors. The database needs a DEFINE CONFIG GRAPHQL statement, and GraphQL reads only the fields that a DEFINE FIELD statement defines. Pass GraphQL variables as the second argument and an operation name as the third.

db.query("""
    DEFINE FIELD name ON person TYPE string;
    DEFINE CONFIG GRAPHQL AUTO;
""").execute()

print(db.graphql("{ persons { name } }"))

{'data': {'persons': [{'name': 'Jaime'}, {'name': 'Tobie'}]}}

The embedded engine is built without GraphQL and ISO GQL, so graphql() and gql() need a connection to a server.

Reading results as they arrive

Against SurrealDB 3.3.0 or later over a WebSocket, the SDK receives the results of query() as a stream and builds the same answer from it, so the code on this page needs no change. To act on each record as it arrives instead of waiting for the whole answer, iterate the builder or call .rows(), as Streaming results shows.

Learn more

• •

  Surreal API reference for the signatures of the methods on this page

• •

  Builders for every method of the query builder and the batch

• •

  Data manipulation for the methods that write the SurrealQL for you

• •

  Transactions for client-side transactions

• •

  Error handling for the errors that a query raises

• •

  SurrealQL for the query language