Use Python to operate MySQL database instances

Use Python to operate 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

 

The installation process is simple:

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 pieces of data are inserted: cur.exe cute (insert into Writers (Name) VALUES ('Jack london'character INTO cur.exe cute (insert into Writers (Name) VALUES ('honore de INTO cute (insert into Writers (Name) VALUES ('lion INTO cute (insert into Writers (Name) VALUES ('emile zola'{{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 in python using the explain cur.exe cute (SELECT * FROM Writers) # Use the fetchall function to set 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, ‘Lion 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 London2 Honore de Balzac3 Lion Feuchtwanger4 Emile Zola5 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 subclass 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 Name1 Jack London2 Honore de Balzac3 Lion Feuchtwanger4 Emile Zola5 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:

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 file source: crazy ant's blog www.crazyant.net summarizes and organizes 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 the 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))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) # Transaction Features 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.