# 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" %}