Python Mysql instance code tutorial online (query manual)

Instance 1. Obtain the MYSQL version

Install mysql in windows for python Development

Download the EXE Installation File in MySQL-python Windows

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-

# Install mysql db for python
Import MySQLdb as mdb

Con = None

Try:
# Method for connecting to mysql: connect ('IP', 'user', 'Password', 'dbname ')
Con = mdb. connect ('localhost', 'root ',
'Root', 'test ');

# All queries are run on the cursor module connected to con.
Cur = 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" % data
Finally:
If con:
# Remember to close the connection anyway
Con. close ()

Execution result:Copy codeThe Code is as follows: 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:

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-
# Source: Summary
Import MySQLdb as mdb
Import sys

# Set con as a global connection
Con = mdb. connect ('localhost', 'root', 'root', 'test ');

With con:

# Obtain the connected cursor. operations can be performed only when the obtained cursor is obtained.
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 ))")
# Five data entries are inserted below
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:

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-
# Source: Summary
Import MySQLdb as mdb
Import sys

# Connect to mysql to obtain the connected object
Con = mdb. connect ('localhost', 'root', 'root', 'test ');

With con:
# Still, the first step is to obtain the connected cursor object for query.
Cur = con. cursor ()
# Like query functions in other languages, execute is the execution query function in python.
Cur.exe cute ("SELECT * FROM Writers ")

# Use the fetchall function to store the result set (multi-dimensional tuples) into rows.
Rows = cur. fetchall ()

# Traverse the result set in sequence and find that each element is a record in the table, which is displayed with a single tuple
For row in rows:
Print row

Running result:Copy codeThe Code is as follows: (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:

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-
# Source: Summary of crazy ant's blog

Import MySQLdb as mdb
Import sys

# Obtain the link object of mysql
Con = mdb. connect ('localhost', 'root', 'root', 'test ');

With con:
# Obtain the query execution object
Cur = con. cursor ()

# Run the select statement.
Cur.exe cute ("SELECT * FROM Writers ")

# Use cur. rowcount to obtain the number of result sets
Numrows = int (cur. rowcount)

# Loop numrows times to retrieve a row of data each time
For I in range (numrows ):
# Extract a row and put it in the row. This is a tuple (id, name)
Row = cur. fetchone ()
# Directly output two elements
Print row [0], row [1]

Running result:Copy codeThe Code is as follows: 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)

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-
# Source: Summary of crazy ant's blog

Import MySQLdb as mdb
Import sys

# Obtain the link object queried by mysql
Con = mdb. connect ('localhost', 'root', 'root', 'test ')

With con:
# Obtain the connected dictionary cursor. 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 ()

# Data traversal remains unchanged (a little more direct than the previous one)
For row in rows:
# Here, you can use the key-Value Pair method to obtain data by the key name.
Print "% s" % (row ["Id"], row ["Name"])

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

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-
# Source: Summary of crazy ant's blog

Import MySQLdb as mdb
Import sys

# Retrieving Database Link objects
Con = mdb. connect ('localhost', 'root', 'root', 'test ')

With con:
# Retrieving 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, the 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:Copy codeThe Code is as follows: 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)

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-
# Source: Summary of crazy ant's blog
Import MySQLdb as mdb
Import sys

Con = 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 execute
Cur.exe cute ("UPDATE Writers SET Name = % s WHERE Id = % s ",
("Guy de Maupasant", "4 "))
# Use cur. rowcount to obtain the number of affected rows
Print "Number of rows updated: % d" % cur. rowcount

Result:

Copy codeThe Code is as follows: 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:

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-
# Source: Summary of crazy ant's blog
Import MySQLdb as mdb
Import sys

Try:
# Open an image in Read File Mode
Fin = open ("../web.jpg ")
# Reading text into an img object
Img = fin. read ()
# Close a file
Fin. close ()

Handle t IOError, e:
# Print the error message if an error occurs
Print "Error % d: % s" % (e. args [0], e. args [1])
Sys. exit (1)

Try:
# Link to mysql to obtain objects
Conn = mdb. connect (host = 'localhost', user = 'root', passwd = 'root', db = 'test ')
# Obtain and execute cursor
Cursor = conn. cursor ()
# Insert data directly as a string into the database
Cursor.exe cute ("insert into Images SET Data = '% S'" % mdb. escape_string (img ))

# Submit data
Conn. commit ()

# Close cursor and link after submission
Cursor. close ()
Conn. close ()

Failed t mdb. Error, e:
# Print information if an exception occurs
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

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-
# Source: Summary of crazy ant's blog
Import MySQLdb as mdb
Import sys

Try:
# Connect to mysql to obtain the connected object
Conn = mdb. connect ('localhost', 'root', 'root', 'test ');

Cursor = conn. cursor ()

# Execute the SQL statement to query the image field
Cursor.exe cute ("SELECT Data FROM Images LIMIT 1 ")

# Use the binary write method to open an image file. If the file does not exist, it is automatically created.
Fout = open('image.png ', 'wb ')
# Directly store data such as files
Fout. write (cursor. fetchone () [0])
# Close the written file
Fout. close ()

# Releasing resources for Data Query
Cursor. close ()
Conn. close ()

Handle t IOError, e:
# Catch IO exceptions, mainly because file write errors occur
Print "Error % d: % s" % (e. args [0], e. args [1])
Sys. exit (1)

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

Copy codeThe Code is as follows: #-*-coding: UTF-8 -*-
# Source: Summary of crazy ant's blog
Import MySQLdb as mdb
Import sys

Try:
# Connect to mysql to obtain the connected object
Conn = mdb. connect ('localhost', 'root', 'root', 'test ');

Cursor = conn. cursor ()
# If a database supports transactions, it will automatically start
# MYSQL is used here, so the transaction will be automatically started (not if the 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 "))

# Features of transactions 1. Atomic manual submission
Conn. commit ()

Cursor. close ()
Conn. close ()

Failed t mdb. Error, e:
# If an error occurs, you can roll back. The preceding three statements are either executed or not executed.
Conn. 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.

Download a pdf version for your local browsing

Full text, this article translated from: http://zetcode.com/databases/mysqlpythontutorial/