Python operates on nine MySQL database instances,

Python operates on nine MySQL database instances,

Install the mysql module on Windows for Python Development

When using python to connect to mysql, you need to use the installation version. The source code version is prone to error prompts. The 32 and 64 versions are packaged below.
MySQL-python-1.2.3.win32-py2.7.exe
MySQL-python-1.2.3.win-amd64-py2.7.exe
Instance 1. Obtain the MYSQL version

#-*-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 ()

Instance 2. Create a table and insert data

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,name1_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 ')" Export cur.exe cute ("insert into Writers (Name) VALUES ('honore de balzac') "Export cur.exe cute (" insert into Writers (Name) VALUES ('lion Feuchtwanger ') "Export cur.exe cute (" insert into Writers (Name) VALUES ('emile Zola ') "Export cur.exe cute (" insert into Writers (Name) VALUES ('Truman Capote ')")

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

Import MySQLdb as mdbimport sys # connect to mysql and obtain 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. execute 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:

Import MySQLdb as mdbimport sys # obtain the 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
Instance 4. Use the dictionary cursor to obtain the result set (you can use the table field name to access the value)

Import MySQLdb as mdbimport sys # obtain the link object con = mdb for mysql query. connect ('localhost', 'root', 'root', 'test') with con: # Get the dictionary cursor on the connection. Pay attention to the obtained method, # Every cursor is actually a sub-class of cursor. cur = con.cursor(mdb.cursors.dictcursor) curcurcur.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

Import MySQLdb as mdbimport sys # obtain the database link object con = mdb. connect ('localhost', 'root', 'root', 'test') with con: # obtain the common query cursorcur = con.cursor()cur.exe cute ("SELECT * FROM Writers ") rows = cur. fetchall () # Get the description of the connection object desc = cur. descriptionprint '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)

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 sentence that can be assembled to enter 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 correspondingly has a special field BLOB (Binary Large Object), that is, a Large Binary Object field. See 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:

Copy codeThe Code is as follows: create table Images (Id int primary key AUTO_INCREMENT, Data MEDIUMBLOB );

Then run the following PYTHON code:

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 cursorcursor = conn.cursor()# directly concatenates Data INTO the Data 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)

Instance 8. Read images from the database

Import MySQLdb as mdbimport systry: # connect to mysql and obtain the connection object conn = mdb. connect ('localhost', 'root', 'root', 'test'); cursor = conn. cursor () # execute SQLcursor.exe cute ("SELECT Data FROM Images LIMIT 1") to query the image field # open an image file using the binary write method, 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)

Import MySQLdb as mdbimport systry: # connect to mysql and obtain the connection 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, therefore, the transaction is automatically started (if MYISM engine is used, cursor.exe cute ("UPDATE Writers SET Name = % s WHERE Id = % s", ("Leo Tolstoy ", "1" initialize cursor.exe cute ("UPDATE Writers SET Name = % s WHERE Id = % s", ("Boris Pasternak ", "2" initialize 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), the following 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.

The preceding nine practical Python operations for MySQL database instances are shared with you. I hope this will help you with your learning.

Articles you may be interested in:

• Python Mysql instance code tutorial online (query manual)
• Connect python Django to the MySQL database for addition, deletion, modification, and query
• Example of connecting python to mysql database (add, delete, and modify operations)
• Install Mysql module MySQLdb in Python
• How to troubleshoot Chinese display garbled characters in mysql using python
• Example of MySQLdb module usage in python
• How to configure mysql database in Django1.7 + python 2.78 + pycharm
• Install python2.7 + pip + mysqld in centos
• MySQL-Python Installation notes