Python-related Mysql instance code

This article introduces various code instances and details such as Python operations on MYSQL, execution of SQL statements, obtaining result sets, traversing result sets, obtaining a field, obtaining the table field name, inserting images into the database, and executing transactions. introduction, code is a big, rich and beautiful code dinner. Instance 1. Obtain the MYSQL version. Install the mysql module in windows for pyt.

This article introduces various code instances and details such as Python operations on MYSQL, execution of SQL statements, obtaining result sets, traversing result sets, obtaining a field, obtaining the table field name, inserting images into the database, and executing transactions. introduction, code is a big, rich and beautiful code dinner. Instance 1. Obtain the MYSQL version. Install the mysql module in windows for pyt.

This article introduces various code instances and details such as Python operations on MYSQL, execution of SQL statements, obtaining result sets, traversing result sets, obtaining a field, obtaining the table field name, inserting images into the database, and executing transactions. introduction, code is a big, rich and beautiful code dinner.

Instance 1. Obtain the MYSQL version

Install the mysql module in a windows environment for python development. Please refer to another article:

Download the EXE Installation File in MySQL-python Windows

#-*-Coding: UTF-8-*-# Install mysql db for pythonimport MySQLdb as mdbcon = Nonetry: # method to connect to mysql: connect ('IP', 'user ', 'Password', 'dbname') con = mdb. connect ('localhost', 'root', 'root', 'test'); # All queries are run on the cursor of a module connected to con. cursor () # execute a query cur.exe cute ("select version ()") # obtain the result of the previous query, which is a single result data = cur. fetchone () print "Database version: % s" % datafinally: if con: # In any case, remember to close con. close ()

Execution result:

Database version: 5.5.25

Instance 2. Create a table and insert data

The execute method is mainly executed on the cursor. See the source code:

#-*-Coding: UTF-8-*-# From www.crazyant.net summarized and sorted import MySQLdb as mdbimport sys # Set con to global connection con = mdb. connect ('localhost', 'root', 'root', 'test'); with con: # Get the connected cursor, only the obtained cursor, we can perform various operations cur = con. cursor () # CREATE a data TABLE writers (id, name) cur.exe cute ("create table if not exists \ Writers (Id int primary key AUTO_INCREMENT, Name VARCHAR (25 ))") # The following five data records are inserted: cur.exe cute ("insert into Writers (Name) VALUES ('Jack London ')") cur.exe cute ("insert into Writers (Name) VALUES ('honore de balzac') ") cur.exe cute (" insert into Writers (Name) VALUES ('lion Feuchtwanger ') ") cur.exe cute (" insert into Writers (Name) VALUES ('emile Zola ') ") cur.exe cute (" insert into Writers (Name) VALUES ('Truman Capote ')")

Run the following command ):

Instance 3. python uses slect to obtain mysql Data and traverse it

I am afraid this is the most used. Please read the code as soon as possible:

#-*-Coding: UTF-8-*-# Source: www.crazyant.net finishing summary import MySQLdb as mdbimport sys # connect to mysql and get the connection object con = mdb. connect ('localhost', 'root', 'root', 'test'); with con: # Still, the first step is to obtain the connected cursor object, used to execute the query cur = con. cursor () # similar to query functions in other languages, execute is the execution query function in python. cur.exe cute ("SELECT * FROM Writers") # Use the fetchall function to convert the result set (multi-dimensional tuples) store rows in rows = cur. fetchall () # traverse the result set in sequence and find that each element is a record in the table. A single tuple is used to display for row in rows: print row.

Running result:

(1L, 'Jack London ')
(2L, 'honore de balzac ')
(3L, 'on' Feuchtwanger ')
(4L, 'emile Zola ')
(5L, 'Truman Capote ')

The above code is used to retrieve all the results, but each row is printed by one ancestor. Now we use the method to retrieve a single data:

#-*-Coding: UTF-8-*-# Source: crazy ant blog www.crazyant.net summary finishing import MySQLdb as mdbimport sys # Get mysql link object con = mdb. connect ('localhost', 'root', 'root', 'test'); with con: # obtain the query execution object cur = con. cursor () # Run the query. Here the select statement cur.exe cute ("SELECT * FROM Writers") # Use cur. rowcount: obtain the number of result sets numrows = int (cur. rowcount) # cyclically numrows. Each time a row of data is taken out for I in range (numrows): # Each time a row is taken out and put into the row, This Is A tuples (id, name) row = cur. fetchone () # print row [0], row [1]

Running result:

1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

• Numrows = int (cur. rowcount) is used to obtain the number of result sets.
• Row = cur. fetchone () each time a row of data is retrieved, and the record set pointer executes the next row

Instance 4. Use the dictionary cursor to obtain the result set (you can use the table field name to access the value)

#-*-Coding: UTF-8-*-# Source: crazy ant blog www.crazyant.net summary finishing import MySQLdb as mdbimport sys # Get mysql query link object con = mdb. connect ('localhost', 'root', 'root', 'test') with con: # Get the dictionary cursor on the connection. Pay attention to the obtained method, # Each cursor is actually a subclass of cursor cur = con. cursor (mdb. cursors. dictCursor) # The execution statement remains unchanged. cur.exe cute ("SELECT * FROM Writers") # The method for obtaining data remains unchanged. rows = cur. fetchall () # The traversal data remains unchanged (more direct than the previous one) for row in rows: # Here, you can use the key-Value Pair method, print "% s" % (row ["Id"], row ["Name"])

Instance 5. How to obtain the field names and information of a single table

#-*-Coding: UTF-8-*-# Source: crazy ant blog www.crazyant.net summary finishing import MySQLdb as mdbimport sys # Get the database link object con = mdb. connect ('localhost', 'root', 'root', 'test') with con: # Get the normal query cursor cur = con. cursor () cur.exe cute ("SELECT * FROM Writers") rows = cur. fetchall () # Get the description of the connection object desc = cur. description print 'cur. description: ', desc # print the header, that is, field name print "% s % 3 s" % (desc [0] [0], desc [1] [0]) for row in rows: # print the result print "% 2 s % 3 s" % row

Running result:

Cur. description: ('id', 3, 1, 11, 11, 0, 0), ('name', 253, 17, 25, 25, 0, 1 ))
Id Name
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

Instance 6: Use Prepared statements to perform queries (more secure and convenient)

#-*-Coding: UTF-8-*-# Source: crazy ant blog www.crazyant.net summary finishing import MySQLdb as mdbimport syscon = mdb. connect ('localhost', 'root', 'root', 'test') with con: cur = con. cursor () # We can see that you can write an SQL statement that can be assembled to perform cur.exe cute ("UPDATE Writers SET Name = % s WHERE Id = % s ", ("Guy de Maupasant", "4") # Use cur. how many rows affected by rowcount print "Number of rows updated: % d" % cur. rowcount

Result:

Number of rows updated: 1

Instance 7. store images in binary format to MYSQL

Some people like to store images in MYSQL (this kind of practice seems to be rare). I think most of the programs and pictures are files stored on the server. The database only stores the image address, however, MYSQL supports storing images in the database, and there is also a dedicated field.BLOB? (Binary Large Object), which is a Large Binary Object field. Please refer to the following program. Note that you can find one of the test images and the address must be correct:

First, create a table in the database to store images:

CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);

Then run the following PYTHON code:

#-*-Coding: UTF-8-*-# Source: crazy ant blog www.crazyant.net summary finishing import MySQLdb as mdbimport systry: # open the image fin = open (".. /web.jpg ") # Read the text into the img object img = fin. read () # close the file fin. close () handle T IOError, e: # print the Error message print "Error % d: % s" % (e. args [0], e. args [1]) sys. exit (1) try: # link to mysql to obtain the object conn = mdb. connect (host = 'localhost', user = 'root', passwd = 'root', db = 'test') # obtain and execute cursor = conn. cursor () # INSERT Data INTO the database cursor.exe cute ("insert into Images SET Data = '% S'" % mdb. escape_string (img) # submit data conn. commit () # Close cursor and link cursor after submission. close () conn. close () handle T mdb. error, e: # print "Error % d: % s" % (e. args [0], e. args [1]) sys. exit (1)

Result:

• The escape_string function escapes the strings inserted into the database, which indicates some SQL injection attacks.

Instance 8. Read images from the database

#-*-Coding: UTF-8-*-# Source: crazy ant blog www.crazyant.net summary finishing import MySQLdb as mdbimport systry: # connect to mysql, get connected object conn = mdb. connect ('localhost', 'root', 'root', 'test'); cursor = conn. cursor () # Execute SQL cursor.exe cute ("SELECT Data FROM Images LIMIT 1") to query the image field # open an image file by writing binary Data, if it does not exist, the fout = open('image.png ', 'wb') is automatically created. # The data is directly stored in the fout file. write (cursor. fetchone () [0]) # disable the written file fout. close () # Release the resource cursor for data query. close () conn. close () handle T IOError, e: # Catch IO exceptions, mainly because the file write Error print "Error % d: % s" % (e. args [0], e. args [1]) sys. exit (1)

Instance 9. Use Transaction as a Transaction (manual submission and automatic rollback)

#-*-Coding: UTF-8-*-# Source: crazy ant blog www.crazyant.net summary finishing import MySQLdb as mdbimport systry: # connect to mysql, get connected object conn = mdb. connect ('localhost', 'root', 'root', 'test'); cursor = conn. cursor () # If a database supports transactions, it will be automatically started # MYSQL is used here, so the transaction will be automatically started (if it is the MYISM engine) cursor.exe cute ("UPDATE Writers SET Name = % s WHERE Id = % s", ("Leo Tolstoy", "1 ")) cursor.exe cute ("UPDATE Writers SET Name = % s WHERE Id = % s", ("Boris Pasternak", "2 ")) cursor.exe cute ("UPDATE Writer SET Name = % s WHERE Id = % s", ("Leonid Leonov", "3 ")) # features of transactions 1. Atomic manual commit conn. commit () cursor. close () conn. close () handle T mdb. error, e: # if an Error occurs, you can roll back, that is, the preceding three statements are either executed or not executed. rollback () print "Error % d: % s" % (e. args [0], e. args [1])

Result:

1. Because no writer table exists (the third SQL statement), an error occurs:

Error 1146: Table 'test. write' doesn' t exist

2. When an error occurs, the first two statements of the three statements are automatically not executed and the results remain unchanged.

3. If this code is placed in a MyISAM engine table, the first two statements will be executed, and the third statement will not. If it is an INNDB engine, no execution will be performed.