Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief description of the detailed steps of python operating MySQL. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on the detailed steps of python operating MySQL can bring you some practical help.

DescriptionMySQLdb 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.

Note that the MySQLdb module is used for MySQL databases, but MySQLdb, the third-party library used by Python to link to MySQL, does not support Python3.x

Install MySQLdb

Download MySQL-python-1.2.4b4.tar.gz

[root@localhost 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. [root@localhost IdcCheck] # tar-zxvf MySQL-python-1.2.4b4.tar.gz

Decompress

[root@localhost IdcCheck] # tar-zxvf MySQL-python-1.2.4b4.tar.gz

Perform [root@localhost IdcCheck] # cd MySQL-python-1.2.4b4 compilation

[root@localhost MySQL-python-1.2.4b4] # python setup.py build installation

[root@localhost MySQL-python-1.2.4b4] # python setup.py install test whether the installation is successful. If you execute the following code and do not report an error, you will be successful.

[root@localhost IdcCheck] # pythonPython 2.6.6 (r266Sep 84292, Sep 12 2011, 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 >

Connect to MySQL database

Import MySQLdb# opens the database connection db= MySQLdb.connect (host= "localhost", user= "root", passwd= "sa", db= "mytable") # uses the cursor () method to get the operation cursor cursor = db.cursor ()

Close the database connection

Need to close the pointer object and the connection object respectively

Cursor.close () conn.close ()

Coding (preventing garbled code)

Points to pay attention to:

1 Python file sets encoding utf-8 (file is preceded by # encoding=utf-8)

2 MySQL database charset=utf-8

3 Python connection MySQL is added with the parameter charset=utf8

4 set the default encoding of Python 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: perform database operations

Con=cursor.execute (sql,param)

We will use the connection object to get a cursor object, and then we will use the methods provided by cursor to do the work.

These methods include two categories: 1. Carry out the order, 2. Receive return value

The method used by cursor to execute the command:

Callproc (self, procname, args): used to execute a stored procedure. The parameters received are the stored procedure name and parameter list, and the return value is the number of affected rows.

Execute (self, query, args): executes a single sql statement. The parameters received are the sql statement itself and the list of parameters used, and the return value is the number of affected rows.

Executemany (self, query, args): executes a single sql statement, but repeats the parameters in the parameter list. The return value is the number of affected rows.

Nextset (self): move to the next result set

The method used by cursor to receive the return value:

Fetchall (self): receives all the returned result rows.

Fetchmany (self, size=None): receives the size bar and returns the result row. If the value of size is greater than the number of result rows returned, 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', means moving the value bar from the current row, if mode='absolute', means moving the value bar from the first row of the result set.

A complete example.

# use the SQL statement, where the parameters to be received use the% s placeholder. It is important to note that no matter what type of data you want to insert, the placeholder will always use% s

Sql= "insert into cdinfo values (% SJM% s)"

# param should be tuple or list

Param= (title,singer,imgurl,url,alpha)

# execute, if successful, the value of n is 1

N=cursor.execute (sql,param)

# Let's perform a query operation

Cursor.execute ("select * from cdinfo")

# We use the fetchall method. In this way, all the results returned by the query will be saved in the cds. Each result is a data of type tuple, and these tuple make up a tuple

Cds=cursor.fetchall ()

# because it is tuple, you can use the result set in this way

Print cds [0] [3]

# or show it directly to see what the result set really looks like

Print cds

# if you need to insert data in bulk, do so

Sql= "insert into cdinfo values (0JI% SJM% sJM% sJet% sJet% s)"

# each set of values is a tuple, and the whole parameter set forms a tuple, or list

Param= ((title,singer,imgurl,url,alpha), (title2,singer2,imgurl2,url2,alpha2))

# use the executemany method to insert data in bulk. This is really a cool way!

N=cursor.executemany (sql,param)

It is important to note (or to my surprise) that after performing an insert or delete or modification, you need to call the conn.commit () method to commit. In this way, the data will really be saved in the database. I don't know if it's my mysql setting problem. Anyway, when I started using it today, if I didn't use commit, the data would not be kept in the database, but the data did stay in the database. Because automatic numbering is accumulated, and the number of affected rows returned is not 0. 0.

Module function demonstration

#! / 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:

/ / get the connected cursor cursor=conn.cursor () / / query sql = "select * from [table]" / / add 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')

The return value is the number of rows obtained by the SQL statement, such as: 2L, which represents 2 rows.

You can then get the row information from the object's fetchone or fetchall method.

Get line information

The fetchone () method of the pointer object gets the tuple return value of one row at a time:

Quote

> row=cur.fetchone () > > print row ('user1',' 52c69e3a57331081823331c4e69d3f2esubscription, 1000L, 1000L,' / home/FTP/user1','')

The fetchall () method of the pointer object fetches all the rows in the pointer result set and returns a tuples of the result set:

Quote

> cur.scroll > > row=cur.fetchall () > > print row (('user1',' 52c69e3a573381823331c4e69d3f2etransition, 1000L, 1000L, 1000L)), (' user2', '7e58d63b60197ceb55a1c487989a3720, 1000L, 1000L)

Move the pointer

When using the fetchone () method, the pointer moves. Therefore, if you do not reset the pointer, the information using fetchall will only contain the contents of the row after the pointer.

Manually move the pointer using:

Cur.scroll (int,parm)

The meaning is:

Quote

Int: the number of rows moved, integers; in relative mode, positive numbers move down and negative values move up.

Parm: mobile mode. Default is relative, relative mode; absoulte is acceptable, absolute mode.

Modify data

Modify 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 ('111mm,' 222')) cur.execute (" update table set row1 = 'test' where row2 =' row2' ") cur.execute (" delete from table where row1 = 'row1' ")

Because single quotation marks "'" are used for identification in SQL statements, strings in python need to be enclosed in double quotes.

Alternatively, you can use python's format string 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 spacer of the SQL statement, and the single quote of 'value1' is the string spacer of python, which has a different meaning. Whether a spacer is required and whether to use double or single quotation marks as the interval depends on its meaning. For example, there are also:

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 need to be separated by single quotes; "md5 ('123')" is the string of python that contains single quotes, so it is enclosed in double quotes.

Submit changes

In general, the MySQLdb module automatically commits changes. But after we update the data, we run it manually once:

Conn.commit ()

Close the database connection

You need to close the pointer object and connect the object respectively. They have the same name.

Cursor.close () conn.close ()

For the above detailed steps about python operation of MySQL, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.