Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
MariaDB Connector/Python enables python programs to access MariaDB and MySQL databases, using an API which is compliant with the Python DB API 2.0 (PEP-249). It is written in C and uses MariaDB Connector/C client library for client server communication.
MariaDB Connector/Python can be obtained from central python repository:
$ pip3 install mariadbSources are hosted on
This page is by the .
MariaDB Connector/Python enables python programs to access MariaDB and MySQL databases, using an API which is compliant with the Python DB API 2.0 (PEP-249). It is written in C and Python and uses MariaDB Connector/C client library for client server communication.
The current MariaDB Connector/Python implementation supports
Python versions from 3.7 to 3.11
MariaDB server versions from version 10.3 or MySQL server versions from version 5.7.
MariaDB client library (MariaDB Connector/C) from version 3.3.1.
To install MariaDB Connector/Python on Microsoft Windows, you first have to install a recent version of MariaDB Connector/C. MSI installer for both 32-bit and 64-bit operating systems are available from .
After installation of MariaDB Connector/C download and install MariaDB Connector/Python with the following command:
On success, you should see a message at the end “Successfully installed mariadb-x.y.z”, where x.y.z is the recent version of MariaDB Connector/Python.
The following build prerequisites are required to install or build MariaDB Connector/Python from source code, github or from pypi.org.
To install MariaDB Connector/Python from sources you will need:
C compiler
Python development files (Usually they are installed with package python3-dev). The minimum supported version of Python is 3.7.
MariaDB Connector/C libraries and header files (Either from MariaDB server package or from MariaDB Connector/C package). Minimum required version for MariaDB Connector/Python < 1.1.0 is 3.1.5, for later versions 3.3.1. If your distribution doesn’t provide a recent version of MariaDB Connector/C you can either download binary packages from or build the package from source.
On Posix systems make sure that the path environment variable contains the directory which contains the mariadb_config utility.
Once everything is in place, run
or if you downloaded the source package
For troubleshooting please also check the chapter from the FAQ page.
If you have installed the sources, after successful build you can run the test suite from the source directory.
You can configure the connection parameters by using the following environment variables
TEST_DB_USER (default root)
TEST_DB_PASSWORD
TEST_DB_DATABASE (default ‘testp’)
TEST_DB_HOST (default ‘localhost’)
If you think that you have found a bug in MariaDB Software, please report it at Jira issue tracker and file it under Project CONPY (abbreviation for Connector/Python).
Always search the bug database first. Especially if you are using an older version of MariaDB Connector/Python it could be reported already by someone else or it was already fixed in a more recent version.
We need to know what you did, what happened and what you wanted to happen. A report stating that method xyz() hangs, will not allow us to provide you with an advice or fix, since we just don’t know what the method is doing. Beside versions, a good bug report contains a short script which reproduces the problem. Sometimes it is also necessary to provide the definition (and data) of used tables.
MariaDB Connector/Python interacts with two other components: The database server and MariaDB Connector/C. The latter one is responsible for client/server communication. An error does not necessarily have to exist in Connector / Python; it can also be an error in the database server or in Connector/C. In this case, we will reclassify the bug (MDEV or CONC).
Use copy and paste instead. Screenshots create a lot more data volume and are often difficult to read on mobile devices. Typing program code from a screenshot is also an unnecessary effort.
Scripts which are longer than 10 lines often contain code which is not relevant to the problem and increases the time to figure out the real problem. So try to keep it simple and focus on the real problem.
The sane applies for database related components like tables, views, and stored procedures. Avoid table definitions with hundreds of columns if the problem can be reproduced with only 4 columns.
If you have encountered two or more bugs which are not related, please file an issue for each of them.
If your application crashes, please also provide if possible a backtrace and output of the exception.
For Posix systems: TLS libraries, e.g. GnuTLS or OpenSSL (default)
Since MariaDB Connector/Python 1.1.5: Python’s “packaging” module.
MariaDB Connector/Python is licensed under the GNU Lesser General Public License v2.1
The documentation for MariaDB Connector/Python is covered by the .
You are free, to : - Share, copy and redistribute the material in any medium or format
Adapt, remix, transform, and build upon the material for any purpose, even commercially.
under the following terms : - Attribution – You must give appropriate credit, provide a link to the license, and indicate if changes were made. You may do so in any reasonable manner, but not in any way that suggests the licensor endorses you or your use.
No additional restrictions —- You may not apply legal terms or technological measures that legally restrict others from doing anything the license permits.
A connection pool is a cache of connections to a database server where connections can be reused for future requests. Since establishing a connection is resource-expensive and time-consuming, especially when used inside a middle tier environment which maintains multiple connections and requires connections to be immediately available on the fly.
Especially for server-side web applications, a connection pool is the standard way to maintain a pool of database connections which are reused across requests.
The typical way for creating and using a connection pool is
Create (and configure) a connection pool
Obtain a connection from connection pool
Perform database operation(s)
Close the connection instance and return it to the connection pool.
When creating a connection pool, the following parameters have to be provided:
Connection pool specific parameters
`pool_name`: The name of the pool, if not specified MariaDB Connector/Python will raise an exception.
`pool_size`: The size of the pool, if not specified a default of 5 will be set.
`pool_reset_session`: If set to True, the connection will be reset before returned to the pool
`pool_invalidation_interval`: specifies the validation interval in milliseconds after which the status of a connection requested from the pool is checked. The default values is 500 milliseconds, a value of 0 means that the status will always be checked. Since 1.1.0
Connection parameters
In addition to the connection pool specific parameters initialization method of ConnectionPool Class accepts the same parameters as the connect() method of mariadb module.
Example:
Output:
Class defining a pool of database connections
MariaDB Connector/Python supports simple connection pooling. A connection pool holds a number of open connections and handles thread safety when providing connections to threads.
The size of a connection pool is configurable at creation time, but cannot be changed afterward. The maximum size of a connection pool is limited to 64 connections.
Keyword Arguments:
`pool_name` (str) - Name of connection pool
`pool_size` (int) - Size of pool. The Maximum allowed number is 64. Default to 5
`pool_reset_connection` (bool) - Will reset the connection before returning it to the pool. Default to True.
`pool_validation_interval` (int) - Specifies the validation interval in milliseconds after which the status of a connection requested from the pool is checked. A value of 0 means that the status will always be checked. Default to 500 (Added in version 1.1.6)
**kwargs - Optional additional connection arguments, as described in mariadb.connect() method.
Adds a connection object to the connection pool.
In case that the pool doesn’t have a free slot or is not configured, a PoolError exception will be raised.
Closes connection pool and all connections.
Returns a connection from the connection pool or raises a PoolError exception if a connection is not available.
Sets the connection configuration for the connection pool. For valid connection arguments, check the mariadb.connect() method.
Note: This method doesn’t create connections in the pool. To fill the pool, one has to use add_connection() ḿethod.
Returns the number of connections in connection pool.
Since version 1.1.0
Returns the maximum size for connection pools.
Returns the size of the connection pool.
Returns the name of the connection pool.
The examples in this MariaDB Connector/Python documentation depend on a database test and tables contacts and accounts.
Create a test database if one does not exist with the statement:
Create tables in the test database for testing basic and advanced operations with statements:
Create a user account to test connectivity with the statement:
Ensure that the user account has privileges to access the tables with the statement:
pip3 install mariadbCollecting mariadb
Downloading mariadb-1.1.5-cp310-cp310-win_amd64.whl (190 kB)
---------------------------------------- 190.9/190.9 kB 2.9 MB/s eta 0:00:00
Installing collected packages: mariadb
Successfully installed mariadb-1.1.5pip3 install mariadbcd source_package_dir
python3 -m pip install .cd testing
python3 -m unittest discover -vThe header files and libraries of the Python development package weren’t properly installed. Use your package manager to install them system-wide:
Alpine (using apk):
Ubuntu/Debian (using apt):
CentOS/RHEL (using yum):
Fedora (using dnf):
MacOSX (using homebrew):
OpenSuse (using zypper):
Note: The python3 development packages of your distribution might not cover all minor versions of python3. If you are using python3.10 you may need to install python3.10-dev.
With deprecation of distutils (see PEP-632) version functions of distutils module were replaced in MariaDB Connector/Python 1.1.5 by packaging version functions.
Before you can install MariaDB Connector/Python you have to install the packaging module:
The previously installed version of MariaDB Connector/C is too old and cannot be used for the MariaDB Connector/Python version you are trying to install.
To determine the installed version of MariaDB Connector/C, execute the command:
Check if your distribution can be upgraded to a more recent version of MariaDB Connector/C, which fits the requirements.
If your distribution doesn’t provide a recent version of MariaDB Connector/C, check the MariaDB Connector Download page, which provides latest versions for the major distributions.
If none of the above will work for you, build and install MariaDB Connector/C from source.
The mariadb_config program is used to retrieve configuration information (such as the location of header files and libraries, installed version, etc.) from MariaDB Connector/C.
This error indicates that MariaDB Connector/C, an important dependency for client/server communication that needs to be preinstalled, either was not installed or could not be found.
If MariaDB Connector/C was previously installed, the installation script cannot detect the location of mariadb_config. Locate the directory where mariadb_config was installed and add this directory to your PATH.
If MariaDB Connector/C was not installed and the location of mariadb_config couldn’t be detected, please install MariaDB Connector/C.
Even if the correct version of MariaDB Connector/C was installed, there are multiple mysql.h include files installed on your system, either from libmysql or an older MariaDB Connector/C installation. This can be checked by executing:
Open output.txt in your favourite editor and search for “search starts here” where you can see the include files and paths used for the build.
If your distribution doesn’t provide a recent version of MariaDB Connector/C (required version is 3.3.1) you either can download a version of MariaDB Connector/C from the MariaDB Connector Download page or build the package from source:
No. If an issue was fixed, the fix will be available in the next release via Python’s package manager repository (pypi.org).
To build MariaDB Connector/Python from github sources, checkout latest sources from github:
and build and install it with:
Check if MariaDB server has been started.
Check if the MariaDB server was correctly configured and uses the right socket file:
If the socket is different and cannot be changed, you can specify the socket in your connection parameters.
Another option is setting the environment variable MYSQL_UNIX_PORT.
MariaDB Connector/Python uses MariaDB Connector/C for client-server communication. That means all authentication plugins shipped together with MariaDB Connector/C can be used for user authentication.
Since MariaDB Connector/Python uses binary protocol for client-server communication, this feature is not supported yet.
Python versions which reached their end of life are not officially supported. While MariaDB Connector/Python might still work with older Python 3.x versions, it doesn’t work with Python version 2.x.
No, MariaDB Connector/Python Python uses binary protocol for client/server communication. Before a statement will be executed it will be parsed and parameter markers which are different than question marks will be replaced by question marks. Afterwards the statement will be sent together with data to the server. The transformed statement can be obtained by cursor.statement attribute.
Example:
Please note, that there is no need to escape ‘%s’ by ‘%%s’ for the time conversion in DATE_FORMAT() function.
The default paramstyle (see PEP-249) is qmark (question mark) for parameter markers. For compatibility with other drivers MariaDB Connector/Python also supports (and automatically recognizes) the format and pyformat parameter styles.
Mixing different paramstyles within the same query is not supported and will raise an exception.
Default for autocommit in MariaDB Connector/Python is off, which means every transaction must be committed. Uncommitted pending transactions are rolled back automatically when the connection is closed.
import mariadb
# connection parameters
conn_params= {
"user" : "example_user",
"password" : "GHbe_Su3B8",
"database" : "test"
}
# create new pool
with mariadb.ConnectionPool(pool_name="myfirstpool", pool_size=5, **conn_params) as pool:
print("Pool size of '%s': %s" % (pool.pool_name, pool.pool_size))
# get a connection from pool
with pool.get_connection() as conn:
# print the default database for connection
print("Current database: %s" % conn.database)Pool size of 'myfirstpool': 5
Current database: test# locate mariadb_config
sudo find / -name "mariadb_config"mysqld --help --verbose | grep socketconnection = mariadb.connect(unix_socket="/path_socket/mysql.sock", ....)export MYSQL_UNIX_PORT=/path_to/mysql.socksudo apk add python3-devsudo apt-get install python3-devsudo yum install python3-develsudo dnf install python3-develbrew install mariadb-connector-csudo zypper in python3-develpip3 install packagingmariadb_config --cc_versionexport CFLAGS="-V -E"
pip3 install mariadb > output.txtmkdir bld
cd bld
cmake ..
make
make installgit clone https://github.com/mariadb-corporation/mariadb-connector-python.gitpython3 setup.py build
python3 -m pip install .data = ("Future", 2000)
statement = """SELECT DATE_FORMAT(creation_time, '%h:%m:%s') as time, topic, amount
FROM mytable WHERE topic=%s and id > %s"""
cursor.execute(statement, data)
print(cursor.statement)SELECT DATE_FORMAT(creation_time, '%h:%m:%s') as time, topic, amount FROM mytable WHERE topic=? and id > ?.. code-block:: python
with mariadb.connect(**conn_params) as conn:
with conn.cursor() as cursor:
cursor.execute("CREATE TABLE t1 (id int, name varchar(20))")
# insert
data = [(1, "Andy"), (2, "George"), (3, "Betty")]
cursor.executemany("INSERT INTO t1 VALUES (?,?)", data)
# commit pending transactions
connection.commit()CREATE DATABASE IF NOT EXISTS test;CREATE USER 'db_user'@'192.0.2.1'
IDENTIFIED BY 'db_user_password';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
Output:
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.
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.
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:
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.
In certain situations, for example when inserting default values or NULL, special indicators must be used.
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
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()
Several standard python types are converted into SQL types and returned as Python objects when a statement is executed.
Constants are declared in mariadb.constants module.
For using constants of various types, they have to be imported first:
MariaDB capability flags.
These flags are used to check the capabilities both of a MariaDB server or the client applicaion.
Capability flags are defined in module mariadb.constants.CAPABILIY
Since version 1.1.4
Output:
MariaDB capability flags.
These flags are used to check the capabilities both of a MariaDB server or the client applicaion.
Capability flags are defined in module mariadb.constants.CLIENT
Since version 1.1.0, deprecated in 1.1.4
Cursor constants are used for server side cursors. Currently only read only cursor is supported.
Cursor constants are defined in module mariadb.constants.CURSOR.
Since version 1.1.0
This is the default setting (no cursor)
Will create a server side read only cursor. The cursor is a forward cursor, which means it is not possible to scroll back.
Using ERR constants instead of error numbers make the code more readable. Error constants are defined in constants.ERR module
Since version 1.1.2
Output:
MariaDB FIELD_FLAG Constants
These constants represent the various field flags. As an addition to the DBAPI 2.0 standard (PEP-249) these flags are returned as eighth element of the cursor description attribute.
Field flags are defined in module mariadb.constants.FIELD_FLAG
Since version 1.1.0
column is defined as not NULL
column is (part of) a primary key
column is (part of) a unique key
column is (part of) a key
column contains a binary object
numeric column is defined as unsigned
column has zerofill attribute
column is a binary
column is defined as enum
column is an auto_increment column
column is defined as time stamp
column is defined as SET
column hasn’t a default value
column will be set to current timestamp on UPDATE
column contains numeric value
column is part of a key
MariaDB FIELD_TYPE Constants
These constants represent the field types supported by MariaDB. The field type is returned as second element of cursor description attribute.
Field types are defined in module mariadb.constants.FIELD_TYPE
column type is TINYINT (1-byte integer)
column type is SMALLINT (2-byte integer)
column tyoe is INT (4-byte integer)
column type is FLOAT (4-byte single precision)
column type is DOUBLE (8-byte double precision)
column type is NULL
column tyoe is TIMESTAMP
column tyoe is BIGINT (8-byte Integer)
column type is MEDIUMINT (3-byte Integer)
column type is DATE
column type is TIME
column type is YEAR
column type is YEAR
column type is BIT
column type is JSON
column type is DECIMAL
column type is ENUM
column type is SET
column type is TINYBLOB (max. length of 255 bytes)
column type is MEDIUMBLOB (max. length of 16,777,215 bytes)
column type is LONGBLOB (max. length 4GB bytes)
column type is BLOB (max. length of 65.535 bytes)
column type is VARCHAR (variable length)
column type is CHAR (fixed length)
column type is GEOMETRY
Indicator values are used in executemany() method of cursor class to indicate special values when connected to a MariaDB server 10.2 or newer.
indicates a NULL value
indicates to use default value of column
indicates to ignore value for column for UPDATE statements. If set, the column will not be updated.
indicates not to update the entire row.
For internal use only
For internal use only
The STATUS constants are used to check the server status of the current connection.
Since version 1.1.0
Example:
if (connection.server_status & STATUS.SP_OUT_PARAMS): print("retrieving output parameters from store procedure") ... else: print("retrieving data from stored procedure") ....
Pending transaction
Server operates in autocommit mode
The result from last executed statement contained two or more result sets which can be retrieved by cursors nextset() method.
The last executed statement didn’t use a good index.
The last executed statement didn’t use an index.
The last executed statement opened a server side cursor.
For server side cursors this flag indicates end of a result set.
The current database in use was dropped and there is no default database for the connection anymore.
Indicates that SQL mode NO_BACKSLASH_ESCAPE is active, which means that the backslash character ‘' becomes an ordinary character.
The previously executed statement was slow (and needs to be optimized).
The current result set contains output parameters of a stored procedure.
The session status has been changed.
SQL mode ANSI_QUOTES is active,
Application development with MariaDB Connector/Python
CREATE TABLE test.contacts (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(25),
last_name VARCHAR(25),
email VARCHAR(100)
) ENGINE=InnoDB;
CREATE TABLE test.accounts (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(25),
last_name VARCHAR(25),
email VARCHAR(100),
amount DECIMAL(15,2) CHECK (amount >= 0.0),
UNIQUE (email)
) ENGINE=InnoDB;GRANT SELECT, INSERT, UPDATE, DELETE, DROP
ON test.contacts
TO 'db_user'@'192.0.2.1';
GRANT SELECT, INSERT, UPDATE, DELETE, DROP
ON test.accounts
TO 'db_user'@'192.0.2.1';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:
# 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=' ')Germany GER Berlinfrom mariadb.constants import *Parameters for executemany() need to be passed as a list of tuples.
ByteArray, Bytes
TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB
DateTime
DATETIME
Date
DATE
Time
TIME
Timestamp
TIMESTAMP
None
NULL
Bool
TINYINT
Float, Double
DOUBLE
Decimal
DECIMAL
Long
TINYINT, SMALLINT, INT, BIGINT
String
VARCHAR, VARSTRING, TEXT
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:
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()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 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= [("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()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
with mariadb.connect(**conn_params) 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)import mariadb
from mariadb.constants import *
# connection parameters
conn_params= {
"user" : "example_user",
"password" : "GHbe_Su3B8",
"host" : "localhost"
}
with mariadb.connect(**conn_params) as connection:
# test if LOAD DATA LOCAL INFILE is supported
if connection.server_capabilities & CAPABILITY.LOCAL_FILES:
print("Server supports LOCAL INFILE")Server supports LOCAL INFILEimport mariadb
from mariadb.constants import *
# connection parameters
conn_params= {
"user" : "example_user",
"password" : "wrong_password",
"host" : "localhost"
}
# try to establish a connection
try:
connection= mariadb.connect(**conn_params)
except mariadb.OperationalError as Err:
if Err.errno == ERR.ER_ACCESS_DENIED_ERROR:
print("Access denied. Wrong password!")Access denied. Wrong password!cursor.callproc("my_storedprocedure", (1,"foo"))To retrieve field information for query results:
Import MariaDB Connector/Python:
Define a select_contacts() function that retrieves all contacts from the table:
Define a get_field_info() function that prints the field information associated with the cursor:
Call these functions, then print the field information:
The results should look like this:
To retrieve field information for all tables:
Import MariaDB Connector/Python:
Define a show_tables() function that executes the statement:
Define a get_field_info() function that prints the field information associated with the cursor:
Define a get_table_field_info() function that prints the field information associated with a table:
Call these functions, and then print the field information for each table:
A database transaction is a single unit of logic. A transaction can consist of one or more database operations. Transactions are useful and sometimes essential in several types of data operations. For example, many applications require that a set of SQL statements either complete, or fail, as a single unit.
The common characteristics of transactions are atomicity, consistency, isolation, and durability, what is termed as ACID (atomic, consistent, isolated, durable) transactions. MariaDB transactions are ACID compliant.
You can enable auto-committed transactions using the autocommit connection attribute.
By default, MariaDB Connector/Python disables auto-commit. With auto-commit disabled transactions must be committed explicitly.
You may want to use explicit transactions so that either all statements are committed together or all statements are completely rolled back. For example, explicit transactions are almost always necessary for financial transactions. Otherwise, a situation could occur where, money is removed from the payer's account, but it is not properly moved to the payee's account.
To use explicit transactions, MariaDB's standard transaction related statements can be executed with MariaDB Connector/Python using a cursor:
Additionally, instances of the Connection class can use the commit() and rollback() methods instead.
The following example shows how to update the example table accounts created in . The email data is updated from the format firstnamelastname@example.com to the new format firstname.lastname@example.com. Call the functions to update data in a transaction. Because the updates are made within a transaction, either all contacts' emails are updated to the new format, or none are.
The functions to add and update account data must be defined before being called with regards to their ordering in the script.
The add_account() function adds a new account to the table.
The execute() method is called on the cursor in the add_account() method, which executes an statement to insert a new row into the table.
Confirm the test.accounts table was properly updated by using to execute a statement:
MariaDB Connector/Python disables auto-committing transactions by default, following the PEP-249 DBAPI 2.0 specification.
To auto-commit transactions, enable auto-commit either when initializing a connection or by manually setting the autocommit connection attribute.
To enable auto-commit using connect():
To enable auto-commit using autocommit connection attribute:
With auto-commit enabled, MariaDB Connector/Python commits a transaction after each statement executes.
# Module Import
import mariadb# Print List of Contacts
def select_contacts(cur):
"""Retrieves the list of contacts from the database"""
# Retrieve Contacts
cur.execute("SELECT first_name, last_name, email FROM test.contacts")# Get field info from cursor
def get_field_info(cur):
"""Retrieves the field info associated with a cursor"""
field_info = mariadb.fieldinfo()
field_info_text = []
# Retrieve Column Information
for column in cur.description:
column_name = column[0]
column_type = field_info.type(column)
column_flags = field_info.flag(column)
field_info_text.append(f"{column_name}: {column_type} {column_flags}")
return field_info_text try:
conn = mariadb.connect(
host="192.0.2.1",
port=3306,
user="db_user",
password="USER_PASSWORD")
cur = conn.cursor()
select_contacts(cur)
field_info_text = get_field_info(cur)
print("Columns in query results:")
print("\n".join(field_info_text))
conn.close()
except Exception as e:
print(f"Error: {e}")Columns in query results:
first_name: VAR_STRING
last_name: VAR_STRING
email: VAR_STRING# Module Import
import mariadb# Get list of tables
def show_tables(cur):
"""Retrieves the list of tables from the database"""
table_list = []
# Retrieve Contacts
cur.execute("SHOW TABLES")
for (table,) in cur.fetchall():
table_list.append(table)
return table_list# Get field info from cursor
def get_field_info(cur):
"""Retrieves the field info associated with a cursor"""
field_info = mariadb.fieldinfo()
field_info_text = []
# Retrieve Column Information
for column in cur.description:
column_name = column[0]
column_type = field_info.type(column)
column_flags = field_info.flag(column)
field_info_text.append(f"{column_name}: {column_type} {column_flags}")
return field_info_texttry:
conn = mariadb.connect(
host="192.0.2.1",
port=3306,
user="db_user",
password="USER_PASSWORD",
database="test")
cur = conn.cursor()
tables = show_tables(cur)
for table in tables:
field_info_text = get_table_field_info(cur, table)
print(f"Columns in table {table}:")
print("\n".join(field_info_text))
print("\n")
conn.close()
except Exception as e:
print(f"Error: {e}")The update_account_amount() function updates the amount in an account associated with the given email.
The execute() method is called on the cursor in the update_account_amount() method, which executes an statement to update a row in the table.
In each of these functions, the query string is the first value specified to the execute() method.
In each of these functions, the new values for the row, and the values for the where clause if present, are specified to the execute() method using a tuple.
In each of these functions, the values in the tuple are substituted for the question marks (?) in the query string.
MariaDB Connector/Python module enables python programs to access MariaDB and MySQL databases, using an API which is compliant with the Python DB API 2.0 (PEP-249).
Creates a MariaDB Connection object.
By default, the standard connectionclass mariadb.connections.Connection will be created.
Parameter connectionclass specifies a subclass of mariadb.Connection object. If not specified, default will be used. This optional parameter was added in version 1.1.0.
Connection parameters are provided as a set of keyword arguments:
`host` - The host name or IP address of the database server. If MariaDB Connector/Python was built with MariaDB Connector/C 3.3, it is also possible to provide a comma separated list of hosts for simple fail over in case of one or more hosts are not available.
`user`, `username` - The username used to authenticate with the database server
`password`, `passwd` - The password of the given user
`database`, `db`
For a description of configuration file handling and settings please read the chapter of the MariaDB Connector/C documentation.
Example:
Output:
Class defining a pool of database connections
MariaDB Connector/Python supports simple connection pooling. A connection pool holds a number of open connections and handles thread safety when providing connections to threads.
The size of a connection pool is configurable at creation time, but cannot be changed afterward. The maximum size of a connection pool is limited to 64 connections.
Keyword Arguments:
`pool_name` (str) - Name of connection pool
`pool_size` (int) - Size of pool. The Maximum allowed number is 64. Default to 5
`pool_reset_connection` (bool) - Will reset the connection before returning it to the pool. Default to True.
Constructs an object capable of holding a binary value.
Constructs an object holding a date value.
Constructs an object holding a date value from the given ticks value (number of seconds since the epoch). For more information see the documentation of the standard Python time module.
Constructs an object holding a time value.
Constructs an object holding a time value from the given ticks value (number of seconds since the epoch). For more information see the documentation of the standard Python time module.
Constructs an object holding a datetime value.
Constructs an object holding a datetime value from the given ticks value (number of seconds since the epoch). For more information see the documentation of the standard Python time module.
String constant stating the supported DB API level. The value for mariadb is 2.0.
Integer constant stating the level of thread safety. For mariadb the value is 1, which means threads can share the module but not the connection.
String constant stating the type of parameter marker. For mariadb the value is qmark. For compatibility reasons mariadb also supports the format and pyformat paramstyles with the limitation that they can’t be mixed inside a SQL statement.
String constant stating the version of the used MariaDB Connector/C library.
Since version 1.1.0
Returns the version of MariaDB Connector/C library in use as an integer. The number has the following format: MAJOR_VERSION * 10000 + MINOR_VERSION * 1000 + PATCH_VERSION
Since version 1.1.0 Returns the version of MariaDB Connector/C library as a tuple in the following format: (MAJOR_VERSION, MINOR_VERSION, PATCH_VERSION)
Compliant to DB API 2.0 MariaDB Connector/C provides information about errors through the following exceptions:
Exception raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc.
Exception raised for errors that are related to the database
Exception raised for errors that are related to the database interface rather than the database itself
Exception raised for important warnings like data truncations while inserting, etc
Exception raised for errors related to ConnectionPool class.
Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer.
Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails
Exception raised when the database encounters an internal error, e.g. the cursor is not valid anymore
Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement
Exception raised in case a method or database API was used which is not supported by the database
MariaDB Connector/Python type objects are immutable sets for type settings and defined in DBAPI 2.0 (PEP-249).
Example:
Output:
This type object is used to describe columns in a database that are string-based (e.g. CHAR1).
This type object is used to describe (long) binary columns in a database (e.g. LONG, RAW, BLOBs).
This type object is used to describe numeric columns in a database.
This type object is used to describe date/time columns in a database.
This type object is not supported in MariaDB Connector/Python and represents an empty set.
MariaDB Connector/Python Cursor Object
Executes a stored procedure sp. The data sequence must contain an entry for each parameter the procedure expects.
Input/Output or Output parameters have to be retrieved by .fetch methods, the .sp_outparams attribute indicates if the result set contains output parameters.
Arguments: : - sp: Name of stored procedure.
data: Optional sequence containing data for placeholder : substitution.
Example:
Prepare and execute a SQL statement.
Parameters may be provided as sequence or mapping and will be bound to variables in the operation. Variables are specified as question marks (paramstyle =’qmark’), however for compatibility reasons MariaDB Connector/Python also supports the ‘format’ and ‘pyformat’ paramstyles with the restriction, that different paramstyles can’t be mixed within a statement.
A reference to the operation will be retained by the cursor. If the cursor was created with attribute prepared =True the statement string for following execute operations will be ignored. This is most effective for algorithms where the same operation is used, but different parameters are bound to it (many times).
By default execute() method generates an buffered result unless the optional parameter buffered was set to False or the cursor was generated as an unbuffered cursor.
Prepare a database operation (INSERT,UPDATE,REPLACE or DELETE statement) and execute it against all parameter found in sequence.
Exactly behaves like .execute() but accepts a list of tuples, where each tuple represents data of a row within a table. .executemany() only supports DML (insert, update, delete) statements.
If the SQL statement contains a RETURNING clause, executemany() returns a result set containing the values for columns listed in the RETURNING clause.
Example:
The following example will insert 3 rows:
To insert special values like NULL or a column default, you need to specify indicators:
INDICATOR.NULL is used for NULL values
INDICATOR.IGNORE is used to skip update of a column.
INDICATOR.DEFAULT is used for a default value (insert/update)
INDICATOR.ROW is used to skip update/insert of the entire row.
All values for a column must have the same data type.
Indicators can only be used when connecting to a MariaDB Server 10.2 or newer. MySQL servers don’t support this feature.
Fetch all remaining rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).
An exception will be raised if the previous call to execute() didn’t produce a result set or execute() wasn’t called before.
Fetch the next set of rows of a query result, returning a sequence of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available.
The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor’s arraysize determines the number of rows to be fetched. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned.
An exception will be raised if the previous call to execute() didn’t produce a result set or execute() wasn’t called before.
Fetch the next row of a query result set, returning a single sequence, or None if no more data is available.
An exception will be raised if the previous call to execute() didn’t produce a result set or execute() wasn’t called before.
Return the next row from the currently executed SQL statement using the same semantics as .fetchone().
Will make the cursor skip to the next available result set, discarding any remaining rows from the current set.
Scroll the cursor in the result set to a new position according to mode.
If mode is “relative” (default), value is taken as offset to the current position in the result set, if set to absolute, value states an absolute target position.
Required by PEP-249. Does nothing in MariaDB Connector/Python
Required by PEP-249. Does nothing in MariaDB Connector/Python
(read/write)
the number of rows to fetch
This read/write attribute specifies the number of rows to fetch at a time with .fetchmany(). It defaults to 1 meaning to fetch a single row at a time
When True all result sets are immediately transferred and the connection between client and server is no longer blocked. Since version 1.1.0 default is True, for prior versions default was False.
Indicates if the cursor is closed and can’t be reused
Read-Only attribute which returns the reference to the connection object on which the cursor was created.
This read-only attribute is a sequence of 11-item sequences Each of these sequences contains information describing one result column:
name
type_code
display_size
internal_size
This attribute will be None for operations that do not return rows or if the cursor has not had an operation invoked via the .execute*() method yet.
The 8th parameter ‘field_flags’ is an extension to the PEP-249 DB API standard. In combination with the type element field, it can be determined for example, whether a column is a BLOB or TEXT field:
Since version 1.1.0
The parameter table_name, original_column_name and original_table_name are an extension to the PEP-249 DB API standard.
Returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute or the value for the last usage of LAST_INSERT_ID().
If the last query wasn’t an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute and LAST_INSERT_ID was not used, the returned value will be None
Similar to description property, this property returns a dictionary with complete metadata.
The dictionary contains the following keys:
catalog: catalog (always ‘def’)
schema: current schema
field: alias column name or if no alias was specified column name
org_field: original column name
Since version 1.1.8
Indicates if the current result set contains in out or out parameter from a previous executed stored procedure
(read)
Returns the number of parameter markers present in the executed statement.
Since version 1.1.0
This read-only attribute specifies the number of rows that the last execute*() produced (for DQL statements like SELECT) or affected (for DML statements like UPDATE or INSERT). The return value is -1 in case no .execute*() has been performed on the cursor or the rowcount of the last operation cannot be determined by the interface.
For unbuffered cursors (default) the exact number of rows can only be determined after all rows were fetched.
Example:
(read only)
The last executed statement
Returns the number of warnings from the last executed statement, or zero if there are no warnings.
Warnings can be retrieved by the show_warnings() method of connection class.
# Module Import
import mariadb
import sys
# Adds account
def add_account(cur, first_name, last_name, email, amount):
"""Adds the given account to the accounts table"""
cur.execute("INSERT INTO test.accounts(first_name, last_name, email, amount) VALUES (?, ?, ?, ?)",
(first_name, last_name, email, amount))
# Update Last Name
def update_account_amount(cur, email, change):
"""Updates amount of an account in the table"""
cur.execute("UPDATE test.accounts SET amount=(amount-?) WHERE email=?",
(change, email))
# Instantiate Connection
try:
conn = mariadb.connect(
host="192.0.2.1",
port=3306,
user="db_user",
password="USER_PASSWORD")
cur = conn.cursor()
new_account_fname = "John"
new_account_lname = "Rockefeller"
new_account_email = "john.rockefeller@example.com"
new_account_amount = 418000000000.00
add_account(cur,
new_account_fname,
new_account_lname,
new_account_email,
new_account_amount)
new_account_change = 1000000.00
update_account_amount(cur,
new_account_email,
new_account_change)
conn.commit()
conn.close()
except Exception as e:
print(f"Error committing transaction: {e}")
conn.rollback()SELECT * from test.accounts;+----+------------+-------------+------------------------------+-----------------+
| id | first_name | last_name | email | amount |
+----+------------+-------------+------------------------------+-----------------+
| 1 | John | Rockefeller | john.rockefeller@example.com | 417999000000.00 |
+----+------------+-------------+------------------------------+-----------------+try:
conn = mariadb.connect(
host="192.0.2.1",
port=3306,
user="db_user",
password="USER_PASSWORD",
autocommit=True)
except Exception as e:
print(f"Connection Error: {e}")# Enable Auto-commit
conn.autocommit = True# Get field info from cursor
def get_table_field_info(cur, table):
"""Retrieves the field info associated with a table"""
# Fetch Table Information
cur.execute(f"SELECT * FROM {table} LIMIT 1")
field_info_text = get_field_info(cur)
return field_info_text`unix_socket` - The location of the unix socket file to use instead of using an IP port to connect. If socket authentication is enabled, this can also be used in place of a password.
`port` - Port number of the database server. If not specified, the default value of 3306 will be used.
`connect_timeout` - Connect timeout in seconds
`read_timeout` - Read timeout in seconds
`write_timeout` - Write timeout in seconds
`local_infile` - Enables or disables the use of LOAD DATA LOCAL INFILE statements.
`compress` (default: False) - Uses the compressed protocol for client server communication. If the server doesn’t support compressed protocol, the default protocol will be used.
`init_command` - Command(s) which will be executed when connecting and reconnecting to the database server
`default_file` - Read options from the specified option file. If the file is an empty string, default configuration file(s) will be used
`default_group` - Read options from the specified group
`plugin_dir` - Directory which contains MariaDB client plugins.
`reconnect` - Enables or disables automatic reconnect. Available since version 1.1.4
`ssl_key` - Defines a path to a private key file to use for TLS. This option requires that you use the absolute path, not a relative path. The specified key must be in PEM format
`ssl_cert` - Defines a path to the X509 certificate file to use for TLS. This option requires that you use the absolute path, not a relative path. The X609 certificate must be in PEM format.
`ssl_ca` - Defines a path to a PEM file that should contain one or more X509 certificates for trusted Certificate Authorities (CAs) to use for TLS. This option requires that you use the absolute path, not a relative path.
`ssl_capath` - Defines a path to a directory that contains one or more PEM files that contains one X509 certificate for a trusted Certificate Authority (CA)
`ssl_cipher` - Defines a list of permitted cipher suites to use for TLS
`ssl_crlpath` - Defines a path to a PEM file that should contain one or more revoked X509 certificates to use for TLS. This option requires that you use the absolute path, not a relative path.
`ssl_verify_cert` - Enables server certificate verification.
`ssl` - The connection must use TLS security, or it will fail.
`tls_version` - A comma-separated list (without whitespaces) of TLS versions. Valid versions are TLSv1.0, TLSv1.1,TLSv1.2 and TLSv1.3. Added in version 1.1.7.
`autocommit` (default: False) - Specifies the autocommit settings. True will enable autocommit, False will disable it (default).
`converter` - Specifies a conversion dictionary, where keys are FIELD_TYPE values and values are conversion functions
`pool_validation_interval` (int) - Specifies the validation interval in milliseconds after which the status of a connection requested from the pool is checked. A value of 0 means that the status will always be checked. Default to 500 (Added in version 1.1.6)
**kwargs - Optional additional connection arguments, as described in mariadb.connect() method.
scale
null_ok
field_flags
table_name
original_column_name
original_table_name
org_table: original table name
type: column type
charset: character set (utf8mb4 or binary)
length: The length of the column
max length: The maximum length of the column
decimals: The numer of decimals
flags: Flags (flags are defined in constants.FIELD_FLAG)
ext_type: Extended data type (types are defined in constants.EXT_FIELD_TYPE)
Creates a transaction ID object suitable for passing to the .tpc_*() methods of this connection.
Parameters:
format_id: Format id. Default to value 0.
global_transaction_id: Global transaction qualifier, which must be unique. The maximum length of the global transaction id is limited to 64 characters.
branch_qualifier: Branch qualifier which represents a local transaction identifier. The maximum length of the branch qualifier is limited to 64 characters.
Since version 1.0.1.
Start a new transaction which can be committed by .commit() method, or canceled by .rollback() method.
Since version 1.1.0.
Commit any pending transaction to the database.
Changes the user and default database of the current connection
Parameters: : - user: user name
password: password
database: name of default database
In order to successfully change users a valid username and password parameters must be provided and that user must have sufficient permissions to access the desired database. If for any reason authorization fails an exception will be raised and the current user authentication will remain.
Close the connection now (rather than whenever ._del_() is called).
The connection will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection.
Note that closing a connection without committing the changes first will cause an implicit rollback to be performed.
Returns a new cursor object for the current connection.
If no cursorclass was specified, a cursor with default mariadb.Cursor class will be created.
Optional keyword parameters:
buffered (default: True) - If disabled, the result will be unbuffered, which means before executing another statement with the same connection, the entire result set must be fetched. Please note that the default was False for MariaDB Connector/Python versions < 1.1.0.
dictionary (default: False) - Return fetch values as dictionary when enabled.
named_tuple (default: False) - Return fetch values as named tuple. This feature exists for compatibility reasons and should be avoided due to possible inconsistency.
cursor_type (default: CURSOR.NONE) - If cursor_type is set to CURSOR.READ_ONLY, a cursor is opened for the statement invoked with cursors execute() method.
prepared (default: False) - When enabled, the cursor will remain in prepared state after the first execute() method was called. Further calls to execute() method will ignore the SQL statement.
binary (default: False) - Always execute statement in MariaDB client/server binary protocol.
In versions prior to 1.1.0 results were unbuffered by default, which means before executing another statement with the same connection, the entire result set must be fetched.
fetch* methods of the cursor class by default return result set values as a tuple, unless dictionary or named_tuple was specified. The latter one exists for compatibility reasons and should be avoided due to possible inconsistency in case two or more fields in a result set have the same name.
If cursor_type is set to CURSOR.READ_ONLY, a cursor is opened for the statement invoked with cursors execute() method.
This function is designed to be executed by an user with the SUPER privilege and is used to dump server status information into the log for the MariaDB Server relating to the connection.
Since version 1.1.2.
Returns a tuple representing the version of the connected server in the following format: (MAJOR_VERSION, MINOR_VERSION, PATCH_VERSION)
Parameters: statement: string
This function is used to create a legal SQL string that you can use in an SQL statement. The given string is encoded to an escaped SQL string.
Since version 1.0.5.
Output:
This function is used to ask the server to kill a database connection specified by the processid parameter.
The connection id can be retrieved by SHOW PROCESSLIST SQL command.
A thread_id from other connections can be determined by executing the SQL statement SHOW PROCESSLIST. The thread_id of the current connection is stored in the connection_id attribute.
Checks if the connection to the database server is still available.
If auto reconnect was set to true, an attempt will be made to reconnect to the database server in case the connection was lost
If the connection is not available an InterfaceError will be raised.
tries to reconnect to a server in case the connection died due to timeout or other errors. It uses the same credentials which were specified in connect() method.
Resets the current connection and clears session state and pending results. Open cursors will become invalid and cannot be used anymore.
Causes the database to roll back to the start of any pending transaction
Closing a connection without committing the changes first will cause an implicit rollback to be performed. Note that rollback() will not work as expected if autocommit mode was set to True or the storage engine does not support transactions.”
Gets the default database for the current connection.
The default database can also be obtained or changed by database attribute.
Since version 1.1.0.
Shows error, warning and note messages from last executed command.
Parameter: : xid: xid object which was created by .xid() method of connection : class
Begins a TPC transaction with the given transaction ID xid.
This method should be called outside a transaction (i.e., nothing may have been executed since the last .commit() or .rollback()). Furthermore, it is an error to call .commit() or .rollback() within the TPC transaction. A ProgrammingError is raised if the application calls .commit() or .rollback() during an active TPC transaction.
Optional parameter:
xid : xid object which was created by .xid() method of connection class.
When called with no arguments, .tpc_commit() commits a TPC transaction previously prepared with .tpc_prepare().
If .tpc_commit() is called prior to .tpc_prepare(), a single phase commit is performed. A transaction manager may choose to do this if only a single resource is participating in the global transaction. When called with a transaction ID xid, the database commits the given transaction. If an invalid transaction ID is provided, a ProgrammingError will be raised. This form should be called outside a transaction, and is intended for use in recovery.
Performs the first phase of a transaction started with .tpc_begin(). A ProgrammingError will be raised if this method was called outside a TPC transaction.
After calling .tpc_prepare(), no statements can be executed until .tpc_commit() or .tpc_rollback() have been called.
Returns a list of pending transaction IDs suitable for use with tpc_commit(xid) or .tpc_rollback(xid).
Parameter: : xid: xid object which was created by .xid() method of connection : class
Performs the first phase of a transaction started with .tpc_begin(). A ProgrammingError will be raised if this method outside a TPC transaction.
After calling .tpc_prepare(), no statements can be executed until .tpc_commit() or .tpc_rollback() have been called.
(read/write)
Enable or disable automatic reconnection to the server if the connection is found to have been lost.
When enabled, client tries to reconnect to a database server in case the connection to a database server died due to timeout or other errors.
Toggles autocommit mode on or off for the current database connection.
Autocommit mode only affects operations on transactional table types. Be aware that rollback() will not work if autocommit mode was switched on.
By default, autocommit mode is set to False.
Client character set.
For MariaDB Connector/Python, it is always utf8mb4.
Client capability flags.
Since version 1.1.0.
Client character set collation
Id of current connection
Get the current database of the connection.
Returns true if the connection is alive.
A ping command will be sent to the server for this purpose, which means this function might fail if there are still non-processed pending result sets.
Since version 1.1.0.
Server capability flags.
Since version 1.1.0.
Extended server capability flags (only for MariaDB database servers).
Since version 1.1.0.
Server version in alphanumerical format (str)
Name or IP address of database server.
Database server TCP/IP port. This value will be 0 in case of an unix socket connection.
Return server status flags
Since version 1.1.0.
Server version in numerical format.
The form of the version number is VERSION_MAJOR * 10000 + VERSION_MINOR * 100 + VERSION_PATCH
Returns numeric version of connected database server in tuple format.
TLS cipher suite if a secure connection is used.
Since version 1.0.5.
TLS protocol version if a secure connection is used.
Get peer certificate information.
Since version 1.1.11.
Unix socket name.
Returns the username for the current connection or empty string if it can’t be determined, e.g., when using socket authentication.
Returns the number of warnings from the last executed statement, or zero if there are no warnings.
import mariadb
with mariadb.connect(user="example_user", host="localhost", database="test", password="GHbe_Su3B8") as connection:
print(connection.character_set)utf8mb4import mariadb
from mariadb.constants import FIELD_TYPE
print(FIELD_TYPE.GEOMETRY == mariadb.BINARY)
print(FIELD_TYPE.DATE == mariadb.DATE)
print(FIELD_TYPE.VARCHAR == mariadb.BINARY)True
True
False>>>cursor.execute("CREATE PROCEDURE p1(IN i1 VAR CHAR(20), OUT o2 VARCHAR(40))"
"BEGIN"
" SELECT 'hello'"
" o2:= 'test'"
"END")
>>>cursor.callproc('p1', ('foo', 0))
>>> cursor.sp_outparams
False
>>> cursor.fetchone()
('hello',)
>>> cursor.nextset()
True
>>> cursor.sp_outparams
True
>>> cursor.fetchone()
('test',)data= [
(1, 'Michael', 'Widenius')
(2, 'Diego', 'Dupin')
(3, 'Lawrin', 'Novitsky')
]
cursor.executemany("INSERT INTO colleagues VALUES (?, ?, ?)", data)if cursor.description[0][1] == FIELD_TYPE.BLOB:
if cursor.description[0][7] == FIELD_FLAG.BINARY:
print("column is BLOB")
else:
print("column is TEXT")>>> cursor=conn.cursor()
>>> cursor.execute("SELECT 1")
>>> cursor.rowcount
-1
>>> rows= cursor.fetchall()
>>> cursor.rowcount
1
>>> cursor=conn.cursor(buffered=True)
>>> cursor.execute("SELECT 1")
>>> cursor.rowcount
1# connection parameters
conn_params= {
"user" : "example_user",
"password" : "GHbe_Su3B8",
"host" : "localhost"
}
with mariadb.connect(**conn_params) as connection:
string = 'This string contains the following special characters: \\,"'
print(connection.escape_string(string))This string contains the following special characters: \\,\"This page is: Copyright © 2025 MariaDB. All rights reserved.
This page is: Copyright © 2025 MariaDB. All rights reserved.
This page is: Copyright © 2025 MariaDB. All rights reserved.