DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Mixing SQL and NoSQL With MariaDB and MongoDB
  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • Distributed SQL: An Alternative to Database Sharding
  • Using JSON in MariaDB

Trending

  • How Trustworthy Is Big Data?
  • Beyond Code Coverage: A Risk-Driven Revolution in Software Testing With Machine Learning
  • FIPS 140-3: The Security Standard That Protects Our Federal Data
  • Developers Beware: Slopsquatting and Vibe Coding Can Increase Risk of AI-Powered Attacks
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Connect Python Programs to MariaDB

How to Connect Python Programs to MariaDB

By 
Rob Hedgpeth user avatar
Rob Hedgpeth
·
Oct. 06, 20 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
24.9K Views

Join the DZone community and get the full member experience.

Join For Free
We previously blogged about using Python to connect to MariaDB servers using the MySQL Python package. In June 2020, MariaDB made the first generally available release of Connector/Python. We've refreshed this blog to reflect the latest technology. If you need the old blog, you can find it here.

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.

OR

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:

Shell
xxxxxxxxxx
1
 
1
$ pip3 install mariadb

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: import mariadb

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.

Python
xxxxxxxxxx
1
17
 
1
# Module Imports
2
import mariadb
3
import sys 
4
5
# Connect to MariaDB Platform
6
try: conn = mariadb.connect( 
7
    user="db_user", 
8
    password="db_user_passwd", 
9
    host="192.0.2.1", 
10
    port=3306, 
11
    database="employees" 
12
) except mariadb.Error as e: 
13
    print(f"Error connecting to MariaDB Platform: {e}") 
14
    sys.exit(1) 
15
16
# Get Cursor 
17
cur = conn.cursor()

Retrieving Data

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:

Python
xxxxxxxxxx
1
 
1
cur.execute(
2
    "SELECT first_name,last_name FROM employees WHERE first_name=?", 
3
    (some_name,))


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.

Python
xxxxxxxxxx
1
 
1
# Print Result-set
2
for (first_name, last_name) in cur: 
3
    print(f"First Name: {first_name}, Last Name: {last_name}")


Each row is passed from the cursor as a tuple containing the columns in the SELECT statement.

Adding Data

Using the same execute() method with an INSERT statement, you can add rows to the table.

Python
xxxxxxxxxx
1
 
1
cursor.execute(
2
    "INSERT INTO employees (first_name,last_name) VALUES (?, ?)", 
3
    (first_name, last_name))


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.

Python
xxxxxxxxxx
1
 
1
# Disable Auto-Commit
2
conn.autocommit = False


Once this is done, you can commit and rollback transactions using the commit() and 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.

Catching Exceptions

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:

Python
xxxxxxxxxx
1
 
1
try: 
2
    cursor.execute("some MariaDB query"))
3
except mariadb.Error as e: 
4
    print(f"Error: {e}")


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 close() method:

Python
xxxxxxxxxx
1
 
1
# Close Connection
2
conn.close()


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:

Python
xxxxxxxxxx
1
29
 
1
#!/usr/bin/python 
2
import mariadb 
3
4
conn = mariadb.connect( 
5
    user="db_user", 
6
    password="db_user_passwd", 
7
    host="localhost", 
8
    database="employees"
9
) 
10
  
11
cur = conn.cursor() 
12
  
13
#retrieving information 
14
some_name = "Georgi" 
15
cur.execute("SELECT first_name,last_name FROM employees WHERE first_name=?", (some_name,))
16
17
for first_name, last_name in cur: 
18
    print(f"First name: {first_name}, Last name: {last_name}") 
19
20
#insert information
21
try: 
22
    cur.execute("INSERT INTO employees (first_name,last_name) VALUES (?, ?)", ("Maria","DB"))
23
except mariadb.Error as e: 
24
    print(f"Error: {e}") 
25
26
conn.commit() 
27
print(f"Last Inserted ID: {cur.lastrowid}") 
28
      
29
conn.close()
MariaDB Database connection Python (language) sql Relational database

Published at DZone with permission of Rob Hedgpeth. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Mixing SQL and NoSQL With MariaDB and MongoDB
  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • Distributed SQL: An Alternative to Database Sharding
  • Using JSON in MariaDB

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!