Python operation MySQL Instance code tutorial online version (enquiry manual) _python

Example 1, get MySQL version

Installing the MySQL module in a Windows environment for Python development

Mysql-python windows under EXE installation file download

Copy CodeThe code is as follows:
#-*-Coding:utf-8-*-

#安装MYSQL DB for Python
Import MySQLdb as MDB

con = None

Try
#连接mysql的方法: Connect (' IP ', ' user ', ' password ', ' dbname ')
con = mdb.connect (' localhost ', ' root ',
' Root ', ' test ');

#所有的查询, all running on the cursor of a module connected to con
cur = con.cursor ()

#执行一个查询
Cur.execute ("Select VERSION ()")

#取得上个查询的结果, is a single result
data = Cur.fetchone ()
Print "Database version:%s"% data
Finally
If con:
#无论如何, connect remember to close
Con.close ()

Execution Result:

Copy CodeThe code is as follows:
Database version:5.5.25

Example 2, creating a table and inserting data

The main or the cursor above the Execute method to do, see the Source:

Copy CodeThe code is as follows:
#-*-Coding:utf-8-*-
# from Summary Finishing
Import MySQLdb as MDB
Import Sys

#将con设定为全局连接
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

With Con:

#获取连接的cursor, we can do all kinds of things only if we get the cursor.
cur = con.cursor ()
#创建一个数据表 Writers (id,name)
Cur.execute ("CREATE TABLE IF not EXISTS \
Writers (Id INT PRIMARY KEY auto_increment, Name VARCHAR (25)) ")
#以下插入了5条数据
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, the results are the same on the MySQL command line):



Example 3, Python uses slect to get MySQL data and traverse

This is probably the most used, please read the code quickly:

Copy CodeThe code is as follows:
#-*-Coding:utf-8-*-
# Source: Finishing Summary
Import MySQLdb as MDB
Import Sys

#连接mysql, gets the connected object
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

With Con:
#仍然是, the first step is to get a connected cursor object for executing the query
cur = con.cursor ()
#类似于其他语言的query函数, execute is the Execute query function in Python
Cur.execute ("SELECT * From writers")

#使用fetchall函数, Save the result set (multidimensional tuples) inside the rows
rows = Cur.fetchall ()

#依次遍历结果集, each element is found to be a record in the table, with a tuple to display
For row in rows:
Print row

Operation Result:

Copy CodeThe code is as follows:
(1L, ' Jack London ')
(2L, ' Honore de Balzac ')
(3L, ' Lion Feuchtwanger ')
(4L, ' Emile Zola ')
(5L, ' Truman Capote ')

The above code is used to get all the results out, but when printing is a meta-ancestor print per line, now we use the method to take out the individual data:

Copy CodeThe code is as follows:
#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog Summary Finishing

Import MySQLdb as MDB
Import Sys

#获取mysql的链接对象
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ');

With Con:
#获取执行查询的对象
cur = con.cursor ()

#执行那个查询, here's a SELECT statement.
Cur.execute ("SELECT * From writers")

#使用cur. RowCount gets the number of result set bars
numrows = Int (cur.rowcount)

#循环numrows次, fetch one row of data at a time
For I in Range (NumRows):
#每次取出一行, put it in the row, this is a tuple (id,name)
row = Cur.fetchone ()
#直接输出两个元素
Print row[0], row[1]

Operation 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) to get the number of result sets
row = Cur.fetchone () extracts one row of data at a time, while the recordset's pointer executes the next line

Example 4, using the dictionary cursor to get the result set (you can use the table field name to access the value)

Copy CodeThe code is as follows:
#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog Summary Finishing

Import MySQLdb as MDB
Import Sys

#获得mysql查询的链接对象
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ')

With Con:
#获取连接上的字典cursor, pay attention to the method obtained,
#每一个cursor其实都是cursor的子类
cur = con.cursor (mdb.cursors.DictCursor)

#执行语句不变
Cur.execute ("SELECT * From writers")

#获取数据方法不变
rows = Cur.fetchall ()

#遍历数据也不变 (a little more directly than the previous one)
For row in rows:
#这里, you can use the method of key-value pairs to get the data by the key name
Print "%s%s"% (row["Id"], row["Name"])

Example 5, methods for obtaining field names and information for a single table

Copy CodeThe code is as follows:
#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog Summary Finishing

Import MySQLdb as MDB
Import Sys

#获取数据库的链接对象
con = mdb.connect (' localhost ', ' root ', ' root ', ' test ')

With Con:
#获取普通的查询cursor
cur = con.cursor ()
Cur.execute ("SELECT * From writers")

rows = Cur.fetchall ()

#获取连接对象的描述信息
desc = cur.description
print ' cur.description: ', desc

#打印表头, that's the name of the field
Print "%s%3s"% (Desc[0][0], desc[1][0])

For row in rows:
#打印结果
Print "%2s%3s"% row

Operation Result:

Copy CodeThe code is as follows:
Cur.description: (' Id ', 3, 1, one, one, 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 the query (more secure and convenient)

Copy CodeThe code is as follows:
#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog Summary Finishing
Import MySQLdb as MDB
Import Sys

con = mdb.connect (' localhost ', ' root ', ' root ', ' test ')

With Con:

cur = con.cursor ()
#我们看到, this can be done by writing a SQL statement that can be assembled
Cur.execute ("UPDATE writers SET Name =%s WHERE Id =%s",
("Guy de maupasant", "4"))
#使用cur. RowCount gets how many rows are affected
Print "Number of rows updated:%d"% cur.rowcount

Results:

Copy CodeThe code is as follows:
Number of rows Updated:1

Example 7, put the picture binary into MySQL
Some people like to put pictures into MySQL (which seems to be very few), I think most of the programs, pictures are stored on the server file, the database is only the image of the address, but MySQL is to support the image into the database, but also corresponding to a dedicated field blob (Binary Large object), that is, the larger binary objects field, see the following program, pay attention to the test picture yourself to find one, the address should be correct:

First, create a table in the database to hold the picture:

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: Crazy Ant Blog Summary Finishing
Import MySQLdb as MDB
Import Sys

Try
#用读文件模式打开图片
Fin = Open (".. /web.jpg ")
#将文本读入img对象中
img = Fin.read ()
#关闭文件
Fin.close ()

Except IOError, E:
#如果出错, print error message
Print "Error%d:%s"% (E.args[0],e.args[1])
Sys.exit (1)

Try
#链接mysql, gets the object
conn = mdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' test ')
#获取执行cursor
cursor = Conn.cursor ()
#直接将数据作为字符串, inserting a database
Cursor.execute ("INSERT into Images SET data= '%s '"% mdb.escape_string (IMG))

#提交数据
Conn.commit ()

#提交之后, then close the cursor and link
Cursor.close ()
Conn.close ()

Except MDB. Error, E:
#若出现异常, printing information
Print "Error%d:%s"% (E.args[0],e.args[1])
Sys.exit (1)

Results:

The Escape_string function escapes the string that is inserted into the database, which refers to some SQL injection attacks

Example 8. Read the picture from the database

Copy CodeThe code is as follows:
#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog Summary Finishing
Import MySQLdb as MDB
Import Sys

Try
#连接mysql, gets the connected object
conn = Mdb.connect (' localhost ', ' root ', ' root ', ' test ');

cursor = Conn.cursor ()

#执行查询该图片字段的SQL
Cursor.execute ("Select Data from Images LIMIT 1")

#使用二进制写文件的方法, open a picture file and create it automatically if it doesn't exist
Fout = open (' Image.png ', ' WB ')
#直接将数据如文件
Fout.write (Cursor.fetchone () [0])
#关闭写入的文件
Fout.close ()

#释放查询数据的资源
Cursor.close ()
Conn.close ()

Except IOError, E:
#捕获IO的异常, mostly file write errors occur
Print "Error%d:%s"% (E.args[0],e.args[1])
Sys.exit (1)

Example 9, using Transaction as transaction (manual commit, automatic rollback)

Copy CodeThe code is as follows:
#-*-Coding:utf-8-*-
# Source: Crazy Ant Blog Summary Finishing
Import MySQLdb as MDB
Import Sys

Try
#连接mysql, gets the connected object
conn = Mdb.connect (' localhost ', ' root ', ' root ', ' test ');

cursor = Conn.cursor ()
#如果某个数据库支持事务, it will open automatically
#这里用的是MYSQL, so the transaction is turned on automatically (if the Myism engine is not)
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"))

#事务的特性1, Atomic manual submission
Conn.commit ()

Cursor.close ()
Conn.close ()

Except MDB. Error, E:
#如果出现了错误, you can roll back, that is, the above three statements are either executed or not executed
Conn.rollback ()
Print "Error%d:%s"% (E.args[0],e.args[1])

Results:

1, because there is no writer table (SQL third statement), there is an error:
Error 1146:table ' test.writer ' doesn ' t exist
2, error, departure exception handling, the first two of the 3 statements will automatically become no execution, the result is unchanged
3, if the code is placed in a MyISAM engine table, the first two sentences will be executed, the third sentence will not, if it is the INNDB engine, will not be executed.

For the convenience of the local browse special provide a PDF version of the download

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

The above is the cloud Community small series for your carefully prepared content, in the cloud Habitat Community Blog, quiz, public number, people, courses and other related content, welcome to continue to use the upper right corner search button to search python operation MySQL python mysql instance, python instance manual, Python instance manual cedar, python mysql manual, Python operation Redis instance, so you can get more relevant knowledge.

Python operation MySQL Instance code tutorial online version (enquiry manual) _python