Python programming language to operate MySQL database practice exercises

The following articles mainly introduce the actual operation process of operating MySQL databases using the Python programming language. previous articles mistakenly treated mssql as mysql. We all know that mssql is a Microsoft SQL Server database Server. The following describes some minor knowledge about Python database manipulation and Django database manipulation.

To operate a data library in python, install a package named mysql-python-1.2.2.win32-py2.5.exe In the python and data library. Then we can use the package MySQLdb to perform database operations.

Follow these steps to operate MySQL:

1. Establish a database connection

Import MySQLdb

Conn = MySQLdb. connect (host = "localhost", user = "root", passwd = "sa", db = "mytable ")

Cursor = conn. cursor ()

2. perform database operations

N=cursor.exe cute (SQL, param)

We need to use the connection object to obtain a cursor object. Next, we will use the method provided by cursor to work.

These methods include: 1. execute commands; 2. Receive returned values.

Cursor is used to execute commands:

Callproc (self, procname, args): used to execute a stored procedure. The received parameters are the stored procedure name and parameter list. The returned values are the number of affected rows.

Execute (self, query, args): executes a single SQL statement. The received parameters are the SQL statement itself and the list of parameters used. The returned values are the affected rows.

Executemany (self, query, args): executes a single-pick SQL statement, but repeats the parameters in the parameter list. The returned value is the number of affected rows.

Nextset (self): Move to the next result set

Cursor is used to receive the returned value:

Fetchall (self): receives all returned result rows.

Fetchmany (self, size = None): receives the size of returned results rows. If the size value is greater than the number of returned results rows, the returned cursor. arraysize data is returned.

Fetchone (self): returns a result line.

Scroll (self, value, mode = 'relative '): Move the pointer to a row. if mode = 'relative ', the value bar is moved from the current row. If mode = 'absolute', the value bar is moved from the first row of the result set.

The following code is a complete example.

# When using SQL statements, the parameters to be received here use the % s placeholder. Note that no matter what type of data you want to insert, The placeholder will always use % s

SQL = "insert into cdinfo values (% s, % s )"

# Param should be tuple or list

Param = (title, singer, imgurl, url, alpha)

# Execution. If the execution succeeds, the value of n is 1.

N=cursor.exe cute (SQL, param)

# Execute another query operation MySQL

Cursor.exe cute ("select * from cdinfo ")

# We use the fetchall method. In this way, all results returned by the query will be saved in the cds. Each result is a tuple-type data, which forms a tuple.

Cds = cursor. fetchall ()

# Because it is a tuple, you can use the result set in this way.

Print cds [0] [3]

# Or directly display the result set to see how it looks.

Print cds

# If you want to insert data in batches, do this.

SQL = "insert into cdinfo values (0, % s )"

# The set of each value is a tuple, and the entire parameter set is a tuple or list

Param = (title, singer, imgurl, url, alpha), (title2, singer2, imgurl2, url2, alpha2 ))

# Use the executeplugin method to insert data in batches. This is a really cool method!

N=cursor.exe cute.pdf (SQL, param)

Note that (or I am surprised) after executing the insert, delete, or modify MySQL operation, you need to call conn. commit using the commit () method. in this way, the data is actually stored in the database. I don't know whether it is my mysql settings. In short, if I didn't use commit at the very beginning today, the data will not be retained in the database. However, the data is indeed in the database. because the automatic number is accumulated, and the number of affected rows returned is not 0.

3. Close database connection

You need to close the pointer object and connection object respectively. They have the same name.

Cursor. close ()

Conn. close ()

Django operation Database

Django is an excellent web framework for python. Django connects APIs that operate MySQL databases and is very simple to use. Configure the database to be connected in settings. py, and write the business logic in modules, view, and urls respectively.