Python 操作 MariaDB 数据

1. 安装 MariaDB 的 Python 模块

sudo apt-get install python-pip python-dev libmysqlclient-dev
pip install MySQL-python

我们需要导入 MariaDB 的 Python 模块，即 import MySQLdb 才能使用 python 对 MariaDB进行数据的增删减查等操作。

2. Python 操作 MariaDB 的入门例子

#!/usr/bin/python

import MySQLdb

# Open database connection
conn = MySQLdb.connect(host='127.0.0.1'  
                       ,user='your username'  
                       ,passwd='your password'  
                       ,db='information_schema')  

# prepare a cursor object using cursor() method
cursor = conn.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print "Database version : %s " % data

# disconnect from server
conn.close()

输出 MariaDB 的版本信息，表示成功通过 python 对 MariaDB 进行查询操作。

那么，该怎么用 Python 对 MariaDB 数据库进行增删查等功能呢？下面是一些例子：

3. 创建Table

#!/usr/bin/python

import MySQLdb

# Open database connection
conn = MySQLdb.connect(host='127.0.0.1'  
                       ,user='your username'  
                       ,passwd='your password'  
                       ,db='TEST')  

# prepare a cursor object using cursor() method
cursor = conn.cursor()

# create a table
cursor.execute("DROP TABLE IF EXISTS MENU")
sql = """CREATE TABLE MENU (ORDERS  CHAR(20) NOT NULL)"""
cursor.execute(sql)

# disconnect from server
conn.close()

4. 插入记录

#!/usr/bin/python

import MySQLdb

# Open database connection
conn = MySQLdb.connect(host='127.0.0.1'  
                       ,user='your username'  
                       ,passwd='your password'  
                       ,db='TEST')  

# prepare a cursor object using cursor() method
cursor = conn.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO MENU(ORDERS) VALUES ('O1')"""

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   conn.commit()
except:
   # Rollback in case there is any error
   conn.rollback()

# disconnect from server
conn.close()

5. 查看数据

#!/usr/bin/python

import MySQLdb

# Open database connection
conn = MySQLdb.connect(host='127.0.0.1'  
                       ,user='your username'  
                       ,passwd='your password'  
                       ,db='TEST')  

# prepare a cursor object using cursor() method
cursor = conn.cursor()


sql = "SELECT * FROM MENU"

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      orders = row[0]
      # Now print fetched result
      print "%s" % (orders)

except:
   # Rollback in case there is any error
   print 'unable to fetch data'

# disconnect from server
conn.close()

参考
https://www.tutorialspoint.com/python/python_database_access.htm