在 python 裡連到 mariadb 方法同 mysql,
請先使用指令安裝必要的 driver:
sudo yum install mysql-connector-python
詳全文：
https://dev.mysql.com/doc/connector-python/en/connector-python-installation-binary.html
使用範例：
The basic Python code
To connect to MariaDB using the MySQL Python module in your program, you have to import it first, just as you would any other module. For clarity and ease of use, import the connector class only under the name mariadb: import mysql.connector as mariadb. I’ll use the class under the mariadb name in the following examples.
Next, establish a database connection with code like mariadb_connection = mariadb.connect(user='python_user', password='some_pass', database='employees'), where you assign real values for user, password, and database.
Finally, to start interacting with the database and running queries, you need to instantiate the cursor object with the code cursor = mariadb_connection.cursor(). So far your initial code should look like this:
#!/usr/bin/python import mysql.connector as mariadb mariadb_connection = mariadb.connect(user='python_user', password='some_pass', database='employees') cursor = mariadb_connection.cursor()
Retrieving information
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:
cursor.execute("SELECT first_name,last_name FROM employees WHERE first_name=%s", (some_name,))
This code uses a variable string (%s) which is assigned from the some_name variable that follows in parentheses. You should have assigned the variable already to a name. Use exactly this syntax with the same number of arguments to ensure that your code works.
The result of the query is stored in a list called “cursor.” To test the result you can print it with a simple for loop, but for better formatting use Python’s string formatting method:
for first_name, last_name in cursor:
print("First name: {}, Last name: {}").format(first_name,last_name)
Inserting rows
You can insert rows into a table in a way similar to retrieving it by using the cursor.executemethod: cursor.execute("INSERT INTO employees (first_name,last_name) VALUES (%s,%s)", (first_name, last_name)). Here you should have already assigned the first_nameand last_name variables. By default AUTOCOMMIT is disabled, meaning queries are not committed, so no data will be saved until you manually commit with the connection commitmethod: mariadb_connection.commit().
You should commit as soon as you are certain that the data is correct and should be recorded. This allows you to continue with a new transaction if needed. MariaDB allows you to run multiple concurrent transaction on the same table without locking it when you use XtraDB (InnoDB) engine.
Just as in SQL, the opposite method to commit is rollback. Thus, if you wish to discard the changes from the last queries, you can use the rollback() method: mariadb_connection.rollback().
While inserting rows you may wish to find the ID of the last inserted row when it is automatically generated, as with autoincrement values. You can acquire this useful information with the insert_id() method of the connection class: mariadb_connection.insert_id().
Updating and deleting rows is done similarly to inserting them. The only difference is in the query used.
Trapping errors
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:
try:
cursor.execute("some MariaDB query"))
except mariadb.Error as error:
print("Error: {}".format(error))
If the query in the try clause of the above code fails, MariaDB will raise an SQL exception and you will see it printed, properly formatted. This programming best practice for trapping errors 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: mariadb_connection.close()
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:
#!/usr/bin/python
import mysql.connector as mariadb
mariadb_connection = mariadb.connect(user='python_user', password='some_pass', database='employees')
cursor = mariadb_connection.cursor()
#retrieving information
some_name = 'Georgi'
cursor.execute("SELECT first_name,last_name FROM employees WHERE first_name=%s", (some_name,))
for first_name, last_name in cursor:
print("First name: {}, Last name: {}").format(first_name,last_name)
#insert information
try:
cursor.execute("INSERT INTO employees (first_name,last_name) VALUES (%s,%s)", ('Maria','DB'))
except mariadb.Error as error:
print("Error: {}".format(error))
mariadb_connection.commit()
print "The last inserted id was: ", cursor.lastrowid
mariadb_connection.close()
相關文章：
python mysql.connector.errors.InterfaceError: Use multi=True when executing multiple statements
https://stackoverflow.max-everyday.com/2017/09/python-mysql-connector-errors-interfaceerror-use-multitrue-when-executing-multiple-statements/
Python mysql.connector InternalError: Unread result found
https://stackoverflow.max-everyday.com/2017/09/python-mysql-connector-internalerror-unread-result-found/
