Python operating Mysql example code tutorial online version of the of query manual
- 2020-04-02 09:53:37
- OfStack
Example 1, get the version of MYSQL
Install the mysql module in a Windows environment for python development
(link: #)
# -*- coding: UTF-8 -*-
# The installation MYSQL DB for python
import MySQLdb as mdb
con = None
try:
# The connection mysql Method: connect('ip','user','password','dbname')
con = mdb.connect('localhost', 'root',
'root', 'test');
# All queries are connected con A module of cursor up-running
cur = con.cursor()
# Execute a query
cur.execute("SELECT VERSION()")
# The result of the previous query is a single result
data = cur.fetchone()
print "Database version : %s " % data
finally:
if con:
# Anyway, remember to close the connection
con.close()
Execution results:
Database version : 5.5.25
Instance 2, create a table and insert data
Mainly execute method on cursor, please see the source code:
# -*- coding: UTF-8 -*-
# From summary
import MySQLdb as mdb
import sys
# will con Set to global connection
con = mdb.connect('localhost', 'root', 'root', 'test');
with con:
# Get connected cursor Only to obtain cursor Before we can do anything
cur = con.cursor()
# Create a data table writers(id,name)
cur.execute("CREATE TABLE IF NOT EXISTS
Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
# The following is inserted 5 The data
cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
Run the results (viewed in phpmyadmin, and the same on the mysql command line) :
< img border = 0 SRC = "/ / files.jb51.net/file_images/article/201302/2013218162255339.png" >
Example 3. Python USES slect to retrieve mysql data and traverse it
This one is probably the most used, so please take a quick look at the code:
# -*- coding: UTF-8 -*-
# Source: summarize
import MySQLdb as mdb
import sys
# The connection mysql , gets the connected object
con = mdb.connect('localhost', 'root', 'root', 'test');
with con:
# Again, the first step is to get the connection cursor Object to execute a query
cur = con.cursor()
# Similar to other languages query The function, execute is python Execute the query function in
cur.execute("SELECT * FROM Writers")
# use fetchall Function to store the result set (a multidimensional tuple) rows inside
rows = cur.fetchall()
# Walking through the result set in turn, each element, a record in the table, is displayed as a tuple
for row in rows:
print row
Operation results:
(1L, ' Jack London')
(2L, ' Honore de Balzac')
(3L, ' Lion Feuchtwanger')
(4L, ' Emile Zola')
(5L, ' Truman Capote')
The above code is used to fetch all the results, but the printing time is one metagram per line, now we use the method to fetch the individual data:
# -*- coding: UTF-8 -*-
# Source: crazy ants blog summary
import MySQLdb as mdb
import sys
# To obtain mysql Link object of
con = mdb.connect('localhost', 'root', 'root', 'test');
with con:
# Gets the object on which the query is executed
cur = con.cursor()
# To execute that query, we're using select statements
cur.execute("SELECT * FROM Writers")
# use cur.rowcount Gets the number of bars in the result set
numrows = int(cur.rowcount)
# cycle numrows Times, one row at a time
for i in range(numrows):
# Take one row at a time and put it in row , which is a tuple (id,name)
row = cur.fetchone()
# Output two elements directly
print row[0], row[1]
Operation results:
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote
Numrows = int(cur. Rowcount) is used to get the number of result sets
Row = cur. Fetchone () takes one row at a time while the pointer to the recordset executes the next row
Instance 4. Use dictionary cursor to get the result set (values can be accessed using table field names)
# -*- coding: UTF-8 -*-
# Source: crazy ants blog summary
import MySQLdb as mdb
import sys
# To obtain mysql The linked object of the query
con = mdb.connect('localhost', 'root', 'root', 'test')
with con:
# Gets the dictionary on the connection cursor , notice the method of getting,
# each cursor Actually all is cursor A subclass of
cur = con.cursor(mdb.cursors.DictCursor)
# Execute statement unchanged
cur.execute("SELECT * FROM Writers")
# The method of getting data is unchanged
rows = cur.fetchall()
# Traversing the data is also the same (a little more direct than the previous one)
for row in rows:
# Here, you can use the key-value pair method to get the data by the key name
print "%s %s" % (row["Id"], row["Name"])
Example 5. Method to get the field name and information of a single table
# -*- coding: UTF-8 -*-
# Source: crazy ants blog summary
import MySQLdb as mdb
import sys
# Gets the linked object for the database
con = mdb.connect('localhost', 'root', 'root', 'test')
with con:
# Gets a normal query cursor
cur = con.cursor()
cur.execute("SELECT * FROM Writers")
rows = cur.fetchall()
# Gets the description of the connection object
desc = cur.description
print 'cur.description:',desc
# Print the header, which is the name of the field
print "%s %3s" % (desc[0][0], desc[1][0])
for row in rows:
# Print the result
print "%2s %3s" % row
Operation results:
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
Example 6: using Prepared statements to execute queries (more secure and convenient)
# -*- coding: UTF-8 -*-
# Source: crazy ants blog summary
import MySQLdb as mdb
import sys
con = mdb.connect('localhost', 'root', 'root', 'test')
with con:
cur = con.cursor()
# And we see that we can do this by writing an assembly sql Statement to proceed
cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Guy de Maupasant", "4"))
# use cur.rowcount Gets how many rows are affected
print "Number of rows updated: %d" % cur.rowcount
Results:
Number of rows updated: 1
Example 7. Save images in binary to MYSQL
People like to put pictures in MYSQL (it seems to be little), I see most of the programs, images are stored in the file on the server and the database of just pictures of address, but the MYSQL is to support the image database, and the corresponding one special field BLOB (Binary Large Object), namely Large Binary Object fields, please see the following procedures, pay attention to the test images themselves to find a, address to correct:
First, create a table in the database to hold the images:
CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Then run the following PYTHON code:
# -*- coding: UTF-8 -*-
# Source: crazy ants blog summary
import MySQLdb as mdb
import sys
try:
# Open the image in file read mode
fin = open("../web.jpg")
# Read the text in img In the object
img = fin.read()
# Close the file
fin.close()
except IOError, e:
# If there is an error, print the error message
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
try:
# link mysql , get object
conn = mdb.connect(host='localhost',user='root',passwd='root', db='test')
# Access to perform cursor
cursor = conn.cursor()
# Insert the data directly into the database as a string
cursor.execute("INSERT INTO Images SET Data='%s'" % mdb.escape_string(img))
# Submit data
conn.commit()
# After submitting, close again cursor And link
cursor.close()
conn.close()
except mdb.Error, e:
# If an exception occurs, print the message
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
Results:
< img border = 0 SRC = "/ / files.jb51.net/file_images/article/201302/2013218164010460.png" >
The escape_string function escapes strings inserted into the database, which refers to some SQL injection attacks
Read the picture from the database
# -*- coding: UTF-8 -*-
# Source: crazy ants blog summary
import MySQLdb as mdb
import sys
try:
# The connection mysql , gets the connected object
conn = mdb.connect('localhost', 'root', 'root', 'test');
cursor = conn.cursor()
# Execute the query for the image field SQL
cursor.execute("SELECT Data FROM Images LIMIT 1")
# Use binary write file method, open a picture file, if does not exist, automatically create
fout = open('image.png','wb')
# Direct data as files
fout.write(cursor.fetchone()[0])
# Close the written file
fout.close()
# Resources that free query data
cursor.close()
conn.close()
except IOError, e:
# capture IO The abnormal , mainly is the file will write an error
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
real Using Transaction, that is, a Transaction (manually committed, automatically rolled back)
# -*- coding: UTF-8 -*-
# Source: crazy ants blog summary
import MySQLdb as mdb
import sys
try:
# The connection mysql , gets the connected object
conn = mdb.connect('localhost', 'root', 'root', 'test');
cursor = conn.cursor()
# If a database supports a transaction, it is automatically opened
# I'm using theta here MYSQL , so the transaction is automatically opened (if MYISM The engine won't.)
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Leo Tolstoy", "1"))
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Boris Pasternak", "2"))
cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s",
("Leonid Leonov", "3"))
# Characteristics of transactions 1 Atomic manual submission
conn.commit()
cursor.close()
conn.close()
except mdb.Error, e:
# If an error occurs, you can roll back the three statements above and either execute or none of them
conn.rollback()
print "Error %d: %s" % (e.args[0],e.args[1])
Results:
1. There is an error because the writer table (SQL third statement) does not exist:
Error 1146: Table 'test.writer' doesn't exist
2, there is an error, an exception handling, the first two of the three statements will automatically become no execution, the result is unchanged
3. If this code is put into a MyISAM engine table, the first two sentences will be executed, the third sentence will not; If it is a INNDB engine, it will not execute.
For the convenience of your local browsing special provides a (link: #) download
Finish the full text, this article translated from: http://zetcode.com/databases/mysqlpythontutorial/