mysql-connector-python

安装

pip3 install mysql-connector-python

使用

简单连接

import mysql.connector
from mysql.connector import Error


def connect():
    """ Connect to MySQL database """
    conn = None
    try:
        conn = mysql.connector.connect(host='localhost',
                                       database='dice',
                                       user='root',
                                       password='root')
        if conn.is_connected():
            print('Connected to MySQL database')

    except Error as e:
        print(e)

    finally:
        if conn is not None and conn.is_connected():
            conn.close()


if __name__ == '__main__':
    connect()

通过配置文件连接数据库

创建配置文件config.ini，并定义4个变量：

[mysql]
host = localhost
database = python_mysql
user = root
password =SecurePass1!

创建python_mysql_dbconfig.py用来读取config.ini中的内容

from configparser import ConfigParser


def read_db_config(filename='config.ini', section='mysql'):
    """ Read database configuration file and return a dictionary object
    :param filename: name of the configuration file
    :param section: section of database configuration
    :return: a dictionary of database parameters
    """
    # create parser and read ini configuration file
    parser = ConfigParser()
    parser.read(filename)

    # get section, default to mysql
    db = {}
    if parser.has_section(section):
        items = parser.items(section)
        for item in items:
            db[item[0]] = item[1]
    else:
        raise Exception('{0} not found in the {1} file'.format(section, filename))

    return db

创建connect.py并使用MySQLConnection来连接数据库

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
 
def connect():
    """ Connect to MySQL database """
 
    db_config = read_db_config()
    conn = None
    try:
        print('Connecting to MySQL database...')
        conn = MySQLConnection(**db_config)
 
        if conn.is_connected():
            print('Connection established.')
        else:
            print('Connection failed.')
 
    except Error as error:
        print(error)
 
    finally:
        if conn is not None and conn.is_connected():
            conn.close()
            print('Connection closed.')
 
 
if __name__ == '__main__':
    connect()

封装

项目地址：https://github.com/bihell/mysql-python-class

mysql_connect.py对应的是mysql.connectormysql_python.py对应的是MySQLdb，这个我直接从nestordeharo fork过来的。

你需要import类并初始化host,user,password,database四个参数来开始使用

from mysql_connect import MysqlConnect

connect_mysql = MysqlPython('host.ip.address', 'user', 'password', 'database')

Select语句带一个条件

如果你查询语句只涉及一个表和一个条件，那你可以使用select函数，args参数写你要获取的列名。

  conditional_query = 'car_make = %s '

  result = connect_mysql.select('car', conditional_query, 'id_car', 'car_text', car_make='nissan')

返回结果: 函数返回一个列表，若未获取到数据则返回空列表

Select语句带多个条件(mysql_python.py)

如果你的where条件超过一个，请使用select_advanced函数，args参数会转为tuple。

  sql_query = 'SELECT C.cylinder FROM car C WHERE C.car_make = %s AND C.car_model = %s'

  result = connect_mysql.select_advanced(sql_query, ('car_make', 'nissan'),('car_model','altima'))

注意:在sql_advanced函数中tuple要按顺序传

返回结果: 函数返回一个列表，若未获取到数据则返回空列表

复杂Select语句(mysql_connect.py)

复杂语句就直接用这个吧。

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

result = connect_mysql.select_advanced(query, (hire_start, hire_end))

返回结果: 函数返回一个列表，若未获取到数据则返回空列表

插入数据

插入数据非常简单，指定列名和值即可

  result = connect_msyql.insert('car', car_make='ford', car_model='escort', car_year='2005')

返回结果: 该函数返回最后一个插入数据的row id

批量插入(mysql_connect.py)

批量插入数据，mysql.connect会自动拼接语句

data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"
connect_mysql.insert_bulk(stmt, data)

更新数据

要更新数据，指定表名，条件和字段就可以了。

conditional_query = 'car_make = %s'

result = connect_mysql.update('car_table', conditional_query, 'nissan', car_model='escort', car_year='2005')

返回结果: 该函数返回被修改的数量

复杂更新数据(mysql_connect.py)

复杂语句就直接用这个吧。

stmt = '''
    update bussiness_table
    set businesstype=20 
    where id in (''' + ','.join(id_list) + ''')
    '''

    result = connect_mysql.update_advanced(stmt)

返回结果: 该函数返回被修改的数量

删除数据

删除数据很简单，表名，字段名和条件即可

  conditional_query = 'car_make = %s'

  result = connect_mysql.delete('car', conditional_query, 'nissan')

返回结果: 返回删除的行数