Python connects mysql for CRUD (including exception handling)

Preface

Learn python exception handling


Code

#!/usr/bin/env python
# _*_ coding: utf-8 _*_
# @Time : 2021/6/5 20:52
# @Author : zhaocunwei
# @Version:V 0.1
# @File : example_1.py
# @desc :

import mysql.connector

try:
    con = mysql.connector.connect(
        host="localhost",
        port=3306,
        user="root",
        password="root",
        database="demo"
    )
    # 开启事务
    con.start_transaction()
    # 创建游标
    cursor = con.cursor()
    sql = "INSERT INTO t_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) " \
          "VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
    cursor.execute(sql, (9600, "赵娜", "SALESMAN", None, "1985-12-1", 2500, None, 10))
    con.commit()
except Exception as e:
    con.rollback()
    print(e)
finally:
    if "con" in dir():
        con.close

  • Run, view the results
Insert picture description here


Insert picture description here

Syntax of database connection pool

Insert picture description here
#!/usr/bin/env python
# _*_ coding: utf-8 _*_
# @Time : 2021/6/5 21:25
# @Author : zhaocunwei
# @Version:V 0.1
# @File : example_2.py
# @desc :

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "root",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "UPDATE t_emp SET sql=sql+%s WHERE deptno=%s"
    cursor.execute(sql, (200, 20))
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

  • Report an error after running:
(movie_project-7781sJXo) (movie_project) D:\code\pytest>python example_2.py
1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql=sql+200 WHERE deptn
o=20' at line 1

Insert picture description here


The places marked in red above are caused by carelessness. The modified code is:

#!/usr/bin/env python
# _*_ coding: utf-8 _*_
# @Time : 2021/6/5 21:25
# @Author : zhaocunwei
# @Version:V 0.1
# @File : example_2.py
# @desc :

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "root",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "UPDATE t_emp SET sal=sal+%s WHERE deptno=%s"
    cursor.execute(sql, (200, 20))
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

Insert picture description here


The result after running is:

Insert picture description here

delete data

Insert picture description here


Insert picture description here


When writing a demo, you must be humble and not sloppy.

#!/usr/bin/env python
# _*_ coding: utf-8 _*_
# @Time : 2021/6/5 21:40
# @Author : zhaocunwei
# @Version:V 0.1
# @File : example_3.py
# @desc :

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "root",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "DELETE e,d FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno " \
          "WHERE d.deptno=20"
    cursor.execute(sql)
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

Insert picture description here


Insert picture description here

Execute SQL statements in a loop

Insert picture description here
#!/usr/bin/env python
# _*_ coding: utf-8 _*_
# @Time : 2021/6/5 22:04
# @Author : zhaocunwei
# @Version:V 0.1
# @File : example_4.py
# @desc :

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "root",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "INSERT INTO t_dept(deptno,dname,loc)VALUES(%S,%S,%S) "
    data = [
        [100, "A部门", "北京"], [110, "B部门", "上海"]
    ]
    cursor.executemany(sql, data)
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

After executing the above demo, the following error occurred:

(movie_project-7781sJXo) (movie_project) D:\code\pytest>python example_4.py
Not all parameters were used in the SQL statement

It's still a SQL statement, it

Insert picture description here


turned out to be a placeholder, "

Insert picture description here
#!/usr/bin/env python
# _*_ coding: utf-8 _*_
# @Time : 2021/6/5 22:04
# @Author : zhaocunwei
# @Version:V 0.1
# @File : example_4.py
# @desc :

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "root",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "INSERT INTO t_dept(deptno,dname,loc)VALUES(%s,%s,%s) "
    data = [
        [100, "A部门", "北京"], [110, "B部门", "上海"]
    ]
    cursor.executemany(sql, data)
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

After successful execution:

Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here
  • batch deletion:
Insert picture description here


Insert picture description here