# Basic Usage

## API Reference

* [**Connection API**](https://mariadb.com/docs/connectors/mariadb-connector-python/api/connection) - Connection parameters, methods, and attributes
* [**Cursor API**](https://mariadb.com/docs/connectors/mariadb-connector-python/api/cursor) - Cursor parameters, methods, and attributes
* [**Connection Pooling API**](https://mariadb.com/docs/connectors/mariadb-connector-python/pooling) - Pool configuration and usage

## Connecting

The basic usage of MariaDB Connector/Python is similar to other database drivers which implement DB API 2.0 ([PEP-249](https://peps.python.org/pep-249)).

*Since version 2.0:* Connections can be established using URI strings or keyword arguments.

Below is a simple example of a typical use of MariaDB Connector/Python

```python
import mariadb

# Establish a connection using URI
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test") as conn:
    with conn.cursor() as cursor:
        # Populate countries table with some data
        cursor.execute("INSERT INTO countries(name, country_code, capital) VALUES (?,?,?)",
            ("Germany", "GER", "Berlin"))

        # retrieve data
        cursor.execute("SELECT name, country_code, capital FROM countries")

        # print content
        row = cursor.fetchone()
        print(*row, sep=' ')
```

**Alternative - Keyword Arguments:**

```python
import mariadb

# connection parameters
conn_params = {
    "user": "example_user",
    "password": "GHbe_Su3B8",
    "host": "localhost",
    "database": "test"
}

# Establish a connection
with mariadb.connect(**conn_params) as conn:
    with conn.cursor() as cursor:
        cursor.execute("INSERT INTO countries(name, country_code, capital) VALUES (?,?,?)",
            ("Germany", "GER", "Berlin"))
        cursor.execute("SELECT name, country_code, capital FROM countries")
        row = cursor.fetchone()
        print(*row, sep=' ')
```

*Output*:

```none
Germany GER Berlin
```

Before MariaDB Connector/Python can be used, the MariaDB Connector/Python module must be imported. Once the mariadb module is loaded, a connection to a database server will be established using the method [`connect()`](https://mariadb.com/docs/connectors/api/module#mariadb.connect).

In order to be able to communicate with the database server in the form of SQL statements, a cursor object must be created first.

The method name cursor may be a little misleading: unlike a cursor in MariaDB that can only read and return data, a cursor in Python can be used for all types of SQL statements.

After creating the table mytest, everything is ready to insert some data: Column values that are to be inserted in the database are identified by place holders, the data is then passed in the form of a tuple as a second parameter.

After creating and populating the table mytest the cursor will be used to retrieve the data.

At the end we free resources and close cursor and connection.

## Passing parameters to SQL statements

As shown in previous example, passing parameters to SQL statements happens by using placeholders in the statement. By default MariaDB Connector/Python uses a question mark as a placeholder, for compatibility reason also %s placeholders are supported. Passing parameters is supported in methods `execute()` and `executemany()` of the cursor class.

*Since version 2.0:* By default, the text protocol is used for parameter binding. For binary protocol (prepared statements), set `binary=True` at connection or cursor level. Parameter escaping is handled automatically by the connector.

```python
import mariadb

# Establish a connection using URI
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test") as conn:
    with conn.cursor() as cursor:
        sql = "INSERT INTO countries (name, country_code, capital) VALUES (?,?,?)"
        data = ("Germany", "GER", "Berlin")
        cursor.execute(sql, data)

        conn.commit()

        # delete last entry
        sql = "DELETE FROM countries WHERE country_code=?"
        data = ("GER",)
        cursor.execute(sql, data)

        conn.commit()
```

**Using Binary Protocol (Prepared Statements):**

```python
import mariadb

# Enable binary protocol at connection level
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test?binary=true") as conn:
    with conn.cursor() as cursor:
        # All queries use binary protocol (prepared statements)
        sql = "INSERT INTO countries (name, country_code, capital) VALUES (?,?,?)"
        data = ("Germany", "GER", "Berlin")
        cursor.execute(sql, data)
        conn.commit()
```

Often there is a requirement to update, delete or insert multiple records. This could be done be using `execute()` in a loop, but much more effective is using the `executemany()` method. The executemany() works similar to execute(), but accepts data as a list of tuples:

```python
import mariadb

# Establish a connection using URI
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test") as connection:
    with connection.cursor() as cursor:
        sql = "INSERT INTO countries (name, country_code, capital) VALUES (?,?,?)"

        data = [("Ireland", "IE", "Dublin"),
               ("Italy", "IT", "Rome"),
               ("Malaysia", "MY", "Kuala Lumpur"),
               ("France", "FR", "Paris"),
               ("Iceland", "IS", "Reykjavik"),
               ("Nepal", "NP", "Kathmandu")]

        # insert data
        cursor.executemany(sql, data)

        # Since autocommit is off by default, we need to commit last transaction
        connection.commit()

        # Instead of 3 letter country-code, we inserted 2 letter country code, so
        # let's fix this mistake by updating data
        sql = "UPDATE countries SET country_code=? WHERE name=?"
        data = [("IRL", "Ireland"),
               ("ITA", "Italy"),
               ("MYS", "Malaysia"),
               ("FRA", "France"),
               ("ISL", "Iceland"),
               ("NPL", "Nepal")]
        cursor.executemany(sql, data)

        # Now let's delete all non European countries
        sql = "DELETE FROM countries WHERE name=?"
        data = [("Malaysia",), ("Nepal",)]
        cursor.executemany(sql, data)

        # by default autocommit is off, so we need to commit
        # our transactions
        connection.commit()
```

When using executemany(), there are a few restrictions:

* All tuples must have the same types as in first tuple. E.g. the parameter \[(1),(1.0)] or \[(1),(None)] are invalid.
* Special values like None or column default value needs to be indicated by an indicator.

### Using indicators

In certain situations, for example when inserting default values or NULL, special indicators must be used.

```python
import mariadb
from mariadb.constants import INDICATOR

# Establish a connection using URI
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test") as connection:
    with connection.cursor() as cursor:
        cursor.execute("DROP TABLE IF EXISTS cakes")
        cursor.execute("CREATE TABLE cakes(id int, cake varchar(100), price decimal(10,2) default 1.99)")

        sql = "INSERT INTO cakes (id, cake, price) VALUES (?,?,?)"
        data = [(1, "Cherry Cake", 2.10), (2, "Apple Cake", INDICATOR.DEFAULT)]
        cursor.executemany(sql, data)
        connection.commit()
```

Beside the default indicator which inserts the default value of 1.99, the following indicators are supported: : \* INDICATOR.IGNORE: Ignores the value (only update commands)

* INDICATOR.NULL: Value is NULL
* INDICATOR.IGNORE\_ROW: Don’t update or insert row

#### NOTE

* Mixing different parameter styles is not supported and will raise an exception
* The Python string operator % must not be used. The `execute()` method accepts a tuple or list as second parameter.
* Placeholders between quotation marks are interpreted as a string.
* Parameters for `execute()` needs to be passed as a tuple. If only one parameter will be passed, tuple needs to contain a comma at the end.
* Parameters for `executemany()` need to be passed as a list of tuples.

#### Supported Data types

Several standard python types are converted into SQL types and returned as Python objects when a statement is executed.

#### Supported Data Types

| Python type      | SQL type                             |
| ---------------- | ------------------------------------ |
| None             | NULL                                 |
| Bool             | TINYINT                              |
| Float, Double    | DOUBLE                               |
| Decimal          | DECIMAL                              |
| Long             | TINYINT, SMALLINT, INT, BIGINT       |
| String           | VARCHAR, VARSTRING, TEXT             |
| ByteArray, Bytes | TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB |
| DateTime         | DATETIME                             |
| Date             | DATE                                 |
| Time             | TIME                                 |
| Timestamp        | TIMESTAMP                            |

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/connectors/mariadb-connector-python/usage.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.