Basic usage

Connecting

The basic usage of MariaDB Connector/Python is similar to other database drivers which implement DB API 2.0 (PEP-249).

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

import mariadb

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

# Establish a connection
connection= mariadb.connect(**conn_params)

cursor= connection.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=' ')

# free resources
cursor.close()
connection.close()

Output:

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().

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 MariaDB Connector/Python uses binary protocol, escaping strings or binary data like in other database drivers is not required.

import mariadb

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

# Establish a connection
connection= mariadb.connect(**conn_params)

cursor= connection.cursor()

sql= "INSERT INTO countries (name, country_code, capital) VALUES (?,?,?)"
data= ("Germany", "GER", "Berlin")
cursor.execute(sql, data)

connection.commit()

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

connection.commit()

cursor.close()
connection.close()

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, especially when using a MariaDB database server 10.2 and above, which supports a special “bulk” protocol. The executemany() works similar to execute(), but accepts data as a list of tuples:

import mariadb

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

# Establish a connection
connection= mariadb.connect(**conn_params)

cursor= connection.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= [("Ireland", "IRL"),
       ("Italy", "ITA"),
       ("Malaysia", "MYS"),
       ("France", "FRA"),
       ("Iceland", "ISL"),
       ("Nepal", "NPL")]
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()

# free resources
cursor.close()
connection.close()

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.

import mariadb
from mariadb.constants import *

import mariadb

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

# Establish a connection
connection= mariadb.connect(**conn_params)

cursor= connection.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)

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