How to Connect Python Programs to MariaDB
Join the DZone community and get the full member experience.Join For Free
You can use the popular programming language Python to manage data stored in MariaDB Platform, including MariaDB Server, MariaDB MaxScale and MariaDB SkySQL. Here is everything you need to know about connecting to MariaDB Platform from Python for retrieving, updating and inserting information.
Preparing and Installing
You will need access to MariaDB Server. We recommend either of these two methods:
1. Download MariaDB Server on your own hardware. See the Deployment Guide for step-by-step instructions in our documentation.
2. Deploy MariaDB Platform, which includes MariaDB Enterprise Server, using MariaDB SkySQL. See our documentation to launch a database service in minutes.
If you want to try out Python integration with MariaDB but you don't have a database to play with, you can use the popular employees example database.
MariaDB provides Python support through the MariaDB Connector/Python, which is available through the Python Package Index. To install, use PIP:
Connecting to MariaDB Server
1. To connect to MariaDB Server using MariaDB Connector/Python, you have to import it first, just as you would any other module:
2. Next, establish a database connection with the connect() function. The function takes a series of named arguments specifying your client credentials, such as user name, host, password. If you are using a database instance on SkySQL, this information is provided in the Service Details page for your database instance.
The connection provides you with an interface for configuring your application's connection to the MariaDB Server.
3. Lastly, call the
cursor() method on the connection to retrieve the cursor.
The cursor provides you with an interface for interacting with the Server, such as running SQL queries and managing transactions.
Once you have the initial code in place you can start working with the data. The first thing you should do is try to retrieve information from the database. Here is code for a query against the employees database:
MariaDB Connector/Python uses prepared statements, sanitizing and inserting the values from the tuple into the position of the question marks (?). This is safer than inserting through f-strings or format specifiers when working with user provided information.
The query results are stored in a list in the cursor object. To view the results, you can loop over the cursor.
Each row is passed from the cursor as a tuple containing the columns in the
Using the same
execute() method with an
INSERT statement, you can add rows to the table.
By default, MariaDB Connector/Python enables auto-commit. If you would like to manually manage your transactions, only committing when you are ready, you can disable it by setting the
autocommit attribute on the connection to False.
Once this is done, you can commit and rollback transactions using the
rollback() methods. MariaDB Server allows you to run multiple concurrent transactions on the same table without locking it when you use the InnoDB storage engine.
While inserting rows, you may want to find the Primary Key of the last inserted row when it is generated, as with auto-incremented values. You can retrieve this using the
lastrowid() method on the cursor.
Updating and deleting rows is done similarly to inserting them. The only difference is in the query used.
For any of your SQL actions (querying, updating, deleting, or inserting records) you should try to trap errors, so you can verify that your actions are being executed as expected and you know about any problems as they occur. To trap errors, use the Error class:
If the query in the
try clause of the above code fails, MariaDB Server returns an SQL exception, which is caught in the except and printed to stdout. This programming best practice for catching exceptions is especially important when you're working with a database, because you need to ensure the integrity of the information.
Once you finish working with the database make sure that you close this connection to avoid keeping unused connections open and thus wasting resources. You can close the connection with the
If you used SkySQL to experiment, you may want to delete your database service to stop incurring charges.
The Complete Script
This is how easy and straightforward it is to connect your Python code to a MariaDB database. Here is how a complete script should look like:
Published at DZone with permission of Rob Hedgpeth. See the original article here.
Opinions expressed by DZone contributors are their own.