How to operate MySQL Database in Python

It is believed that many inexperienced people have no idea about how to operate MySQL database in Python. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. MySQL Connector

Create a connection import mysql.connector config= {"host": "localhost", "port": "3306", "user": "root", "password": "password", "database": "demo"} con=mysql.connector.connect (* * config) import mysql.connector config= {"host": "localhost", "port": "3306", "user": "root", "password": "password" "database": "demo"} con=mysql.connector.connect (* * config) Click to add a group to get Python materials for free. Wait in the group file for you to get 1.2Cursor import mysql.connector con=mysql.connector.connect (host= "localhost", port= "3306", user= "root", password= "password", database= "demo") cursor=con.cursor () sql= "SELECT empno,job,sal FROM t_bonus. "cursor.execute (sql) print (type (cursor)) for i in cursor: print (I) con.close () Result: (7369, 'CLERK', Decimal (' 8000.00')) (7499, 'SALESMAN', Decimal (' 1600.00')) (7521, 'SALESMAN', Decimal (' 1250.00')) (7566, 'MANAGER', Decimal (' 2975.00')) (7654, 'SALESMAN' Decimal ('1250.00') (7698,' MANAGER', Decimal ('2850.00')) (7782,' MANAGER', Decimal ('2450.00')) (7788,' ANALYST', Decimal ('3000.00')) (7839,' PRESIDENT', Decimal ('5000.00')) (7844,' SALESMAN', Decimal ('1500.00')) (7900,' CLERK' Decimal ('950.00') (7902,' ANALYST', Decimal ('3000.00')) (7934,' CLERK', Decimal ('1300.00')) 1.3 SQL injection attack

Username=1 OR 1 1 password=1 OR 1 1

When using direct concatenation of strings, the combination with OR is true, regardless of whether it is right or wrong.

Solution-precompile (can also improve speed)

Username= "1 OR 1" password= "1 OR 1" 1 "sql=" SELECT COUNT (*) FROM t_user WHERE username=%s AND AES_DECRYPT (UNHEX (password), 'helloWorld') =% s "cursor.execute (sql, (username,password)) print (cursor.fetchone () [0]) 1.4 transaction management and exception handling

Sql connects and uses exceptions to handle exceptions

Import mysql.connector try: con=mysql.connector.connect (host= "localhost", port= "3306", user= "root", password= "password", database= "demo") con.start_transaction () cursor=con.cursor () sql= "INSERT INTO t_dept (deptno,dname,loc) VALUES (% s) "cursor.execute (sql, (60," SALES "," HUBAI ")) con.commit () except Exception as e: if" con "in dir (): con.rollback () print (e) finally: if" con "in dir (): con.close () 1.5 Delete data import mysql.connector,mysql.connector.pooling config= {" host ":" localhost "," port ":" 3306 " "user": "root", "password": "password", "database": "demo"} try: pool=mysql.connector.pooling.MySQLConnectionPool (* * config,pool_size=5) con=pool.get_connection () con.start_transaction () cursor = con.cursor () sql = "DELETE FROM t_dept WHERE deptno=%s" cursor.execute (sql, ) con.commit () except Exception as e: if "con" in dir (): con.rollback () print (e) # do not need to close con

Executemany () repeatedly executes a SQL statement

Import mysql.connector,mysql.connector.pooling config= {"host": "localhost", "port": "3306", "user": "root", "password": "password", "database": "demo"} try: pool=mysql.connector.pooling.MySQLConnectionPool (* * config,pool_size=5) con=pool.get_connection () con.start_transaction () con.start_transaction () sql = "INSERT INTO t_dept (deptno,dname) Loc) VALUES (% SJM% SJM% s) "date= [[70," SALES "," BEIJING "], [80," ACTOR "," SHANGHAI "]] cursor.executemany (sql, date) con.commit () except Exception as e: if" con "in dir (): con.rollback () print (e) # do not need to close con2. Database connection pool

Database connections are expensive. A connection goes through three TCP handshakes and four waves, and the maximum number of threads on a computer is limited.

Database connection pool technology is to create a connection first, and then take it out and use it directly.

Import mysql.connector,mysql.connector.pooling config= {"host": "localhost", "port": "3306", "user": "root", "password": "password", "database": "demo"} try: pool=mysql.connector.pooling.MySQLConnectionPool (* * config,pool_size=5) con=pool.get_connection () con.start_transaction () con.start_transaction () sql = "INSERT INTO t_dept (deptno,dname) Loc) VALUES (% SJM% SJM% s) "cursor.execute (sql, (70," SALES "," HUBAI ")) con.commit () except Exception as e: if" con "in dir (): con.rollback () print (e) # do not need to close con after reading the above, have you mastered how to operate the MySQL database in Python? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!