Some basic methods for using Python to operate MySQL

This article mainly introduces some basic methods for using Python to operate MySQL. Python + MySQL is also an excellent combination solution for rapid development on the server side. For more information, see Prelude

To be able to operate the database, we need to have a database first, so we need to install Mysql first, and then create a test database python_test for future testing.

CREATE DATABASE `python_test` CHARSET UTF8

Import database module

import MySQLdb

Connect to database

con = MySQLdb.connect(host="localhost", user="root", passwd="******",db="python_test",port=3306)

Although we have obtained the python database connection, we cannot directly operate the database on this object. We also need to obtain the corresponding operation cursor to perform database operations, therefore, you need to perform the following operations:

cur = con.cursor()

Create a table

cur.execute('create table stu_info (name char(128) not null default "", age tinyint(3) not null default 0, sex enum("man","femal") not null default "man") engine=innodb charset=utf8')#0L

Cur.exe cute returns the number of rows affected by the executed SQL statement. The 0L row is used because the database is created here.

However, the SQL statement has not been actually executed yet. You must use MySQLdb. commit to complete the execution.

con.commit()

Here, our tables are actually created.

Similarly, writing data to a table is the same operation procedure: execute => commit

However, the execute for writing data is slightly different, as shown below:
Update table data

When writing data to a table, execute either of the following methods: execute (SQL) directly and commit. The SQL statements are written into the SQL statement.

cur.execute("insert into stu_info (name, age, sex) values ('Yi_Zhi_Yu',25,'man')")con.commit()

This will be written directly to the table, but there is another way,
Execute can accept two parameters. The first parameter is an SQL statement, but the values content in this SQL statement is represented by the placeholder % s. The second parameter is the list of actually written values, as shown below:

cur.execute("insert into stu_info (name, age, sex) values (%s,%s,%s)", ("Tony",25, "man"))con.commit()

This method is clearer and more secure than the first method, and can effectively prevent SQL injection.

In addition, cursor also has an executeute parameter, which is the same as execute. However, the second parameter can pass multiple list values to execute a statement multiple times.

cur.executemany("insert into stu_info (name, age, sex) values (%s,%s,%s)",(("LiMei",26,"femal"),("YuanYuan",28,"femal")))con.commit()

The insert operation is actually performed twice.
Data Query

Let's look at the example.

cur.execute("select * from stu_info")stus = cur.fetchall() 

# Stus is already a combination of query results. The format is as follows:

(('Yi_Zhi_Yu', 25, 'man'), ('Tony', 25, 'man'), ('LiMei', 26, 'femal'), ('YuanYuan', 28, 'femal'))

Tuple format. We can use the cyclic output

 for stu in stus:    print "name: %s; age: %d; sex: %s" %(stu[0], stu[1], stu[2])

Output:

name: Yi_Zhi_Yu; age: 25; sex: manname: Tony; age: 25; sex: manname: LiMei; age: 26; sex: femalname: YuanYuan; age: 28; sex: femal

Although the above query obtains data in each row, there is no field name in the result set. To return the field name, perform the following operations:

cur = con.cursor(cursorclass=MySQLdb.cursors.DictCursor)cur.execute("select * from stu_info")cur.fetchall()

Returned result set:

({'age': 25, 'name': 'Yi_Zhi_Yu', 'sex': 'man'}, {'age': 25, 'name': 'Tony', 'sex': 'man'}, {'age': 26, 'name': 'LiMei', 'sex': 'femal'}, {'age': 28, 'name': 'YuanYuan', 'sex': 'femal'})

Each element is a dict, and each field and corresponding value are displayed in the form of key-value.
Summary

Operations on data in Python, including addition, deletion, and modification, must use the connection object commit after the SQL statement is executed on the pointer object. The query results are obtained using the fetch series methods of the pointer object.

PS: All of the above are study notes, and mistakes are inevitable. please correct me.