MySQL：Execute SQL By Programming

Besides the Command Line, we can use the programming language to execute the SQL command. Such as Python, PHP, Java and so on.

This blog will introduce how to use Python to execute the SQL command.

Download PyMySQL

In the Pythons terminal, you can use the following: pip install PyMySQL to install PyMySQL.
Then we can operational the Database by Python.

If you want know more about the PyMySQL, please click here:PyMySQL – API Reference

Prepare for Query

Connect to MySQL Server:

As same as the terminal, you should connect to the MySQL server.

db = pymysql.connect(host='localhost', user='root', port=3306,
                     password='123123', database='test')

Use the cursor:

All operations that we use should go through the cursor, so we create a cursor.

# 使用cursor()方法获取操作游标
cursor = db.cursor()

Start Query

execute() method

execute() method can only execute one query at a time. Just like this:

sql = "SELECT * FROM Product;"
cursor.execute(sql)  # 执行单条sql语句
result = cursor.fetchall()  # 用游标获取查询到的信息

fetch to get result

fetch is the basic operation in cursor, here we have three different methods about fetch operation:

1. fetchone()
   1. Fetch the next row.
2. fetchmany(size=None)
   1. Fetch several rows.
3. fetchall()
   1. Fetch all the rows.

Each method ensuring that each time you obtained is different row.
In short, it like a iterator.

result = cursor.fetchone()
print(result)

for i in range(5):
    result = cursor.fetchmany(2)
    print(result)

executemany()

This method improves performance on multiple-row INSERT and REPLACE. Otherwise it is equivalent to looping over args with execute().

If you want insert so many rows, you can use this method, and you should pass two arguments:

1. query (str) – Query to execute.
2. args (tuple or list) – Sequence of sequences or mappings. It is used as parameter.

sql = "INSERT INTO Product (maker, model, type) VALUES (%s, %s, %s);"
list1 = [("F", 1021, "Test"), ("F", 1022, "Test")]
cursor.executemany(sql, list1)  

An Easy Sample

import pymysql

db = pymysql.connect(host='localhost', user='root', port=3306,
                     password='123123', database='test')
cursor = db.cursor()

try:
    sql = "SELECT * FROM Product;"
    cursor.execute(sql)  # 执行单条sql语句
    result = cursor.fetchall()  # 用游标获取查询到的信息
    db.commit()  # 提交事务，保存修改
    for row in result:
        print(row)
except Exception:
    db.rollback()  # 发生错误时回滚
    print("error")

cursor.close()  # 关闭游标
db.close()  # 关闭数据库连接