Explanation on the usage of Python Database MYSQL

The main content of this article is to explain the usage of Python database MYSQL. Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "Python database MYSQL usage explanation" bar!

1. Create database connection import mysql.connectorconfig = {'host':' localhost', 'port':' 3306, 'user':' root', 'password':', 'database':' python'} con = mysql.connector.connect (* * config) cursor = con.cursor () # cursor, which is used to execute sql statement 2 and create data table create_table_sql = "CREATE TABLE `browser` (" >

3. Create an index

_ index1 = "ALTER TABLE `browser` ADD UNIQUE INDEX name (name)" # unique index _ index2 = "CREATE INDEX url ON `browser` (url)" # General index for sql in [_ index1, _ index2]: cursor.execute (sql)

Create a table structure and add a field index personally recommend the client to operate manually, one line of code operation efficiency is too low. The official account here replies "mysql" to get related resources.

4. Add, delete, check and modify data

# insert single data insert_sql = "INSERT INTO `browser` (name, url) VALUES (% s,% s)" values = ('Chrome', "http://www.google.cn/chrome/")cursor.execute(insert_sql, values) # insert multiple data values = [(' Chrome'," http://www.google.cn/chrome/"), ('Firefox', "http://www.firefox.com/"), (' Safari2') "https://www.apple.com.cn/safari/")]cursor.executemany(insert_sql, values) # query data select_sql =" SELECT * FROM `browser` "cursor.execute (select_sql) print (cursor.fetchone ()) # get a single data print (cursor.fetchall ()) # get all data # Update data update_sql =" UPDATE `browser` SET `url` = 'http://www.firefox.com.cn' WHERE `name` =' Firefox' "cursor.execute (update_sql) # Delete data delete_sql =" DELETE FROM `browser` WHERE `name` =% s "cursor.execute (delete_sql, ['Safari'])

5. About resisting injection attacks

Because the Sql statement is an interpretive language, it is easy to inject malicious Sql statements when splicing Sql statements.

During the compilation of sql statements, keywords are parsed, so the parameters passed to the compiled sql statements are treated as strings, and the database does not parse the injected sql statements.

6. Transaction control and exception handling

Try: con.start_transaction () cursor = con.cursor () delete_sql = "DELETE FROM `browser` WHERE `name` =% s" cursor.execute (delete_sql, ['Firefox']) except Exception as e: con.rollback () # rollback else: con.commit () # commit

7. Implement database connection pool

If you make a connection request before each operation, it is very resource-consuming, especially when considering the concurrency problem.

The database connection pool creates some database connections in advance and then caches them to avoid the expensive cost of repeatedly creating and destroying connections and solve this problem well.

Import mysql.connector.poolingconfig = {...} pool = mysql.connector.pooling.MySQLConnectionPool (* * config, pool_size=10) con_pool = pool.get_connection () so far, I believe you have a deeper understanding of "explaining the usage of MYSQL in Python database". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!