Python operation MySQL

Description

MySQLdb is a Python DB api-2.0-compliant interface; See PEP-249 for details. For up-to-date versions of MYSQLDB, use the homepage link.

Supported Versions:

* MySQL versions from 3.23 to 5.5; 5.0 or newer recommended. MariaDB should also work.
* Python versions 2.4-2.7; Python 3 support coming soon.

Zmysqlda is a Database Adapter for Zope2.

attention, MySQL the database uses the MySQLdb module, but Python third-party libraries used to link MySQL mysqldb do not support python3.x

Installing MYSQLDB

download mysql-python-1.2.4b4.tar.gz

[Email protected] idccheck]# wget http://downloads.sourceforge.net/project/mysql-python/mysql-python-test/1.2.4b4 /mysql-python-1.2.4b4.tar.gz?r=https%3a%2f%2fsourceforge.net%2fprojects%2fmysql-python%2f&ts=1478767096 &use_mirror=jaist. [Email protected] idccheck]# TAR-ZXVF mysql-python-1.2.4b4.tar.gz

Unzip

[Email protected] idccheck]# TAR-ZXVF mysql-python-1.2.4b4.tar.gz

execute [[email protected] idccheck]# CD MYSQL-PYTHON-1.2.4B4

Compile

[[email protected] mysql-python-1.2.4b4]# python setup.py build

Installation

[[email protected] mysql-python-1.2.4b4]# python setup.py install

Test whether the installation is successful, the following code does not error, the representative of success

[Email protected] idccheck]# Pythonpython 2.6.6 (r266:84292, Sep, 14:03:14) [GCC 4.4.5 20110214 (Red Hat 4.4.5-6) ] on Linux2type "help", "copyright", "credits" or "license" for more information.>>> import mysqldb>>> & Gt;>>

Connect to MySQL Database

Import mysqldb# Open database Connection db = MySQLdb.connect (host= "localhost", user= "root", passwd= "sa", db= "MyTable") # Use Cursor () Method gets the operation cursors cursor = Db.cursor ()

To close a database connection

You need to close the pointer object and the connection object separately

Cursor.close () Conn.close ()

Encoding (prevents garbled characters)

Points to note:

1 python file Set encoding utf-8 (file front plus #encoding =utf-8)

2 MySQL Database charset=utf-8

3 python connection mysql is plus parameter Charset=utf8

4 Set Python's default encoding to Utf-8 (sys.setdefaultencoding (Utf-8)

#encoding =utf-8import sysimport mysqldbreload (SYS) sys.setdefaultencoding (' Utf-8 ') db=mysqldb.connect (user= ' root ', charset= ' UTF8 ')

Note:

Performing Database operations

Con=cursor.execute (Sql,param)
We're going to use the connection object to get a cursor object, and then we'll use the method provided by the cursor to do the work.

These methods include two main classes: 1. Execute command, 2. Receive return value

< Br> the cursor method used to execute the command:
 callproc (self, procname, args):         is used to execute a stored procedure, the parameters received are stored procedure names and parameter lists, The return value is the number of rows affected
Execute (self, query, args):                  executes a single SQL statement, receives the arguments for the SQL statement itself and the parameter list used, the return value is the number of rows affected
Executemany (self, Query, args):      executes a heads-up SQL statement, but repeats the parameters in the parameter list, and the return value is the number of rows affected
Nextset (self):                                   move to the next result set

< Span style= "Color:rgb (0,32,96); font-size:24px;" >cursor the method used to receive the return value:
Fetchall (self):                                   receives all the returned result rows. The
Fetchmany (self, size=none):         receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned , the cursor.arraysize data is returned.
Fetchone (self):                                  returns a result row.
Scroll (self, value, mode= ' relative '):     move the pointer to a row. If mode= ' relative ', the value bar is moved from the current row, If mode= ' absolute ', the value bar is moved from the first row of the result set.

a complete example.
#使用sql语句, the parameters to be received here are in the%s placeholder. Note that no matter what type of data you want to insert, the placeholder will always use%s
Sql= "INSERT into cdinfo values (%s,%s,%s,%s,%s)"
#param应该为tuple或者list
Param= (Title,singer,imgurl,url,alpha)
#执行, if successful, the value of n is 1
N=cursor.execute (Sql,param)
#再来执行一个查询的操作
Cursor.execute ("SELECT * from Cdinfo")
#我们使用了fetchall这个方法. In this way, the CDs will be saved as the full result of the query return. Each result is a tuple-type data that consists of a tuple
Cds=cursor.fetchall ()
#因为是tuple, so you can use result sets like this
Print Cds[0][3]
#或者直接显示出来 to see what the result set looks like.
Print CDs
#如果需要批量的插入数据, just do it.
Sql= "INSERT into cdinfo values (0,%s,%s,%s,%s,%s)"
#每个值的集合为一个tuple, the entire set of parameters consists of a tuple, or list
Param= ((Title,singer,imgurl,url,alpha), (TITLE2,SINGER2,IMGURL2,URL2,ALPHA2))
#使用executemany方法来批量的插入数据. This is a really cool way!
N=cursor.executemany (Sql,param)
Note that (or I'm surprised) that you need to call the Conn.commit () method to commit after the insert or delete or modify operation has been performed. This way, the data is actually saved in the database. I don't know if it's my MySQL setup problem, in short, Today, when I started using it, the data would not remain in the database without a commit, but the data did stay in the database. Because the AutoNumber is cumulative and the number of affected rows returned is not 0.

Module function Demo

#!/usr/bin/pythonimport mysqldbcon= mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' abc ') cursor = Con.cursor () sql = "SELECT * from MYT" cursor.execute (SQL) Row=cursor.fetchone () print rowcursor.close () con.close ()

Execute the following SQL statement to get the return value:

Gets the connected cursor cursor=conn.cursor ()//Query sql = "SELECT * from" table ""//New sql = "INSERT into" table "(field, field) values (value, Value)"//Modify SQL = "Update" table "set field = ' value ' WHERE condition"//Delete sql = "Delete from" table "Where Condition" cursor.execute (SQL)

return value
Cur.execute (' SELECT * from tables ')
Its return value is the number of rows that the SQL statement gets, such as: 2L, which represents 2 rows.
The row information can then be obtained from the object's Fetchone or Fetchall method.

Get line information
The Fetchone () method of a pointer object is a tuple return value that gets one row at a time:
Reference

>>> row=cur.fetchone () >>> print row (' User1 ', ' 52c69e3a57331081823331c4e69d3f2e ', 1000L, 1000L, '/ Home/ftp/user1 ', ')

The Fetchall () method of the Pointer object, which extracts all the rows in the pointer result set, returns a tuple of result sets (tuples):
Reference

>>> cur.scroll (0, ' absolute ') >>> Row=cur.fetchall () >>> print row (' User1 ', ' 52c69e3a57331081823331c4e69d3f2e ', 1000L, 1000L, '/home/ftp/user1 ', '), (' User2 ', ' 7e58d63b60197ceb55a1c487989a3720 ', 1000L, 1000L, '/home/ftp/user2 ', None))

Move pointer
When using the Fetchone () method, the pointer is shifted. So, if you do not reset the pointer, the information that uses Fetchall will only contain the line content that follows the pointer.
To manually move the pointer using:

Cur.scroll (Int,parm)

The meaning is:
Reference
int: Number of rows moved, integer; in relative mode, positive numbers move down, negative values move up.
Parm: Mobile mode, default is relative, relative mode, acceptable absoulte, absolute mode.

modifying data
Modify the data, including INSERT, UPDATE, delete. They are all executed using the Execute () method of the Pointer object:

Cur.execute ("INSERT into table (Row1, Row2) VALUES (' 111 ', ' 222 ')") Cur.execute ("Update table Set row1 = ' Test ' where row2 = ' Row2 ') cur.execute ("Delete from table where Row1 = ' row1 '")

Because the single quotation mark "'" is used for the identity in the SQL statement, the string in Python is enclosed in double quotation marks.
In addition, you can use Python's "format string" notation to simplify commands, such as:
Cur.execute ("Update table Set Row1 = '%s ' where Row2 = '%s '"% (' value1 ', ' value2 '))

※ Note that the single quotation mark of '%s ' is the delimiter for the SQL statement, and the single quotation mark of ' value1 ' is a python string spacer, which has a different meaning. Whether you need a spacer, or if you want to use double or single quotes as the interval, depends on its meaning. For example, there are:
Cur.execute ("Update ftpusers set passwd=%s where userid= '%s '"% ("MD5 (' 123 ')", ' User2 '))

Here, paswd=%s is because the MD5 () function of SQL does not require single quote interval; "MD5 (' 123 ')" is a python string containing single quotes, so enclose it in double quotation marks.

Submit Changes
In general, the MySQLdb module will automatically commit the changes. But after we update the data, we run it manually once:

Conn.commit ()

To close a database connection
You need to close the pointer object and the connection object separately. They have the same name.

Cursor.close () Conn.close ()

Python operation MySQL