MySQL Python Class

中文文档

Description

Python MySQL class helps the python developers to connect with MySQL and use queries like: SELECT (one and more conditions), INSERT, UPDATE, DELETE in an easy and fast way.

mysql_python.py for MySQLdb

mysql_connect.py for mysql.connector

Usage

To start using the Python MySQL class you need to import the class and initialize with 4 required parameters: host, user, password, database

from mysql_connect import MysqlConnect

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

Select one condition

If you want to obtain information from one table and use one condition, you could use select function where args argument is for referencing the columns you need to obtain.

  conditional_query = 'car_make = %s '

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

Result: The function return a list or empty list in case of not find nothing.

---

Select more than one condition (mysql_python.py)

In case you have need to obtain information with more than one condition, you could use the select_advancedfunction, where the columns you need to obtain are referenced by args and the conditionals are referenced by tuples

  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'))

Note: Inside the sql_advanced function the order of the parameters matters, so the tuples should have the order of the query.

Result: The function return a list or empty list in case of not find nothing.

---

Complex select (mysql_connect.py)

Just compose your SQL statement.

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: The function return a list or empty list in case of not find nothing.

---

Insert data

Inserting data is really simple and intuitive, where we are going to reference the column and the values

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

Result: The function return the last row id was inserted.

---

Insert data in bulk(mysql_connect.py)

Inserting data in bulk. mysql.connect will automatically splicing statements.

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)

---

Update data

To update data just needs the table, conditional query and specify the columns you want update

conditional_query = 'car_make = %s'

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

Result: This function return the amount of rows updated.

---

Complex Update data(mysql_connect.py)

Just compose your SQL statement.

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

    result = connect_mysql.update_advanced(stmt)

Result: This function return the amount of rows updated.

---

Delete data

Delete data is really simple like insert, just reference the column as condition and table.

  conditional_query = 'car_make = %s'

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

Result: This function return the amount of rows deleted.

Requirements

MySQL-python >= 1.2.5 or mysql-connector >= 2.1.6