Shulou(Shulou.com)06/01 Report--

In this article, the editor introduces in detail "how to connect the Python program to the MariaDB", the content is detailed, the steps are clear, and the details are handled properly. I hope this "how to connect the Python program to the MariaDB" article can help you solve your doubts.

You can use the popular programming language Python to manage data stored in the MariaDB platform, including MariaDB Server, MariaDB MaxScale, and MariaDB SkySQL.

Preparation and installation

You will need to access the MariaDB server. We recommend one of the following two methods:

1. Download the MariaDB server on your own hardware. Refer to the deployment guide for step-by-step instructions in the documentation.

two。 Deploy the MariaDB platform using MariaDB SkySQL, including MariaDB Enterprise Server. Please refer to our documentation to start the database service in a few minutes.

If you want to try the integration of Python and MariaDB, but there is no database available, you can use the popular employee sample database.

MariaDB provides Python support through MariaDB Connector/Python and is available through Python Package Index. To install, use PIP:

Shell:

$pip3 install mariadb connects to the MariaDB server

1. To connect to the MariaDB server using MariaDB Connector/Python, you must first import it, just like any other module: import mariadb

two。 Next, use the connect () function to establish a database connection. This function takes a series of named parameters to specify your client credentials, such as user name, host, and password. If you use a database instance on SkySQL, this information is provided on the service details page of your database instance.

This connection provides you with an interface for configuring your application's connection to the MariaDB server.

3. Finally, the method on the cursor () connection is called to retrieve the cursor.

Cursors provide you with an interface to interact with the server, such as running SQL queries and managing transactions.

Python:

# Module Importsimport mariadbimport sys # Connect to MariaDB Platformtry: conn = mariadb.connect (user= "db_user", password= "db_user_passwd", host= "192.0.2.1", port=3306, database= "employees") except mariadb.Error ase: print (f "Error connecting to MariaDB Platform: {e}") sys.exit (1) # Get Cursor cur = conn.cursor ()

With the initial code, you can start working with the data. The first thing you should do is try to retrieve information from the database. The following is the query code for the employee database:

Python:

Cur.execute ("SELECT first_name,last_name FROM employees WHERE first_name=?", (some_name,))

MariaDB Connector / Python uses prepared statements to clean up the values in the tuple and insert them into the question mark (?) The location of. This is safer than inserting through an f string or format specifier when dealing with user-supplied information.

The query results are stored in a list of cursor objects. To see the results, you can cycle over the cursor.

Python:

# Print Result-setfor (first_name, last_name) in cur: 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.

Add data

Execute () uses the same method for the INSERT statement, and you can add rows to the table.

Python:

Cursor.execute ("INSERT INTO employees (first_name,last_name) VALUES", (first_name,last_name))

By default, MariaDB Connector/Python enables autocommit. If you want to manage your transaction manually and commit only when you are ready, you can disable it by setting the property on the autocommit connection to False.

Python:

# Disable Auto-Commitconn.autocommit = False

When this is done, you can commit and roll back the transaction using the commit () and rollback () methods. MariaDB Server allows you to run multiple concurrent transactions on the same table without locking it when using the InnoDB storage engine.

When inserting a row, you may want to find its primary key when the last inserted row is generated, just like an automatically incremented value. You can retrieve it using the method on the lastrowid () cursor.

Updating and deleting rows is similar to inserting rows. The only difference is the query used.

Catch exception

For any of your SQL operations (query, update, delete, or insert records), you should try to catch errors so that you can verify that your actions are performing as expected and that you know when any problems occur. To catch errors, use the Error class:

Python:

Try: cursor.execute ("some MariaDB query") except mariadb.Error as e: print (f "Error: {e}")

If the query in the clause of the above try code fails, MariaDB Server returns a SQL exception that is caught in except and printed to stdout. This programming best practice of catching exceptions is particularly important when you use a database because you need to ensure the integrity of the information.

When you are finished using the database, be sure to close this connection to avoid wasting resources by keeping unused connections open. You can close the connection using the following close () method:

Python:

# Close Connectionconn.close ()

If you are experimenting with SkySQL, you may need to delete your database service to stop generating fees.

A complete script

This is how simple and straightforward it is to connect Python code to an MariaDB database. Here is the code for the complete script:

Python:

#! / usr/bin/python import mariadb conn = mariadb.connect (user= "db_user", password= "db_user_passwd", host= "localhost", database= "employees") cur = conn.cursor () # retrieving information some_name = "Georgi" cur.execute ("SELECT first_name,last_name FROM employees WHERE first_name=?", (some_name,)) for first_name,last_name in cur: print (f "First name: {first_name} Last name: {last_name} ") # insert informationtry: cur.execute (" INSERT INTO employees (first_name,last_name) VALUES (?,) ", (" Maria "," DB ")) except mariadb.Error as e: print (f" Error: {e} ") conn.commit () print (f" Last Inserted ID: {cur.lastrowid} ") conn.close () here This article "how to connect the Python program to MariaDB" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about the article, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.