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

This chapter explains how Python operates the database and completes simple additions, deletions, modifications and queries, taking MySQL database as an example.

Python's MySQL database operation module is called MySQLdb and requires additional installation.

Install through the pip tool: pip install MySQLdb

MySQLdb module, we mainly use the method of connecting to the database MySQLdb.Connect (), after connecting to the database, and then use some methods to do the corresponding operation.

MySQLdb.Connect (parameters...) Method provides the following common parameters:

Parameters.

Description

Host database address user database user name, passwd database password, default is empty db database name, no default library port database port, default 3306connect_timeout connection timeout, seconds use_unicode result returns charset insert database code as unicode string

The connect () function returned by the connection object:

Commit () commits the transaction. For databases and tables that support transactions, if the commit modification operation does not apply, the rollback () transaction rollback will not be written to the database. For databases and tables that support transactions, if you execute this method, the current transaction is rolled back. Without commit (). Cursor ([cursorclass]) creates a cursor object. All sql statements are executed under the cursor object. MySQL itself does not support cursors, and the MySQLdb module simulates its cursors.

Cursor objects also provide several methods:

Close () closes cursor execute (sql) executes sql statement excutemany (sql) executes multiple sql statements fetchone () fetches the first record from the execution result fetchmany (n) fetches n records fetchall () fetches all records scroll (self, value, mode='relative') cursor scrolls from the execution result

Blog address: http://lizhenliang.blog.51cto.com

QQ group: 323779636 (Shell/Python operation and maintenance development group)

13.1 additions, deletions, modifications and queries in the database

13.1.1 create a user table in the test library and add a record

> conn = MySQLdb.Connect (host='192.168.1.244',user='root',passwd='QHyCTajI',db='test',charset='utf8') > cursor = conn.cursor () > sql = "create table user (id int,name varchar (30), password varchar (30)" > cursor.execute (sql) # the number returned is the number of affected rows 0L > sql = "insert into user (id,name,password) values ('1mm) values' '123456') "> cursor.execute (sql) 1L > conn.commit () # commit transaction Write to the database > cursor.execute ('show tables') # View the created table 1L > cursor.fetchall () # return all the results executed by the previous cursor. By default, all the results executed by the previous cursor are returned as tuples (select * from user') 1L > cursor.fetchall ()) ((1L, utilisation 123456'),)

13.1.2 insert multiple pieces of data

> sql = 'insert into user (id,name,password) values (% srecinct% s)' > args = [('2l) cursor.execute (sql) 4L > > cursor.fetchall () (1L, ubixiaomingying, upright 123456') (1L, ubixiaomingying, upright 123456') Upright zhangsanfang, upright 123456'), (3L, upright lisification, upright 123456'), (4L, upright wangwujiang, upright 123456'))

The args variable is a list of multiple groups, with each tuple corresponding to each record. When querying multiple records, using this method can effectively improve the insertion efficiency.

13.1.3 Delete the record of the user name xiaoming

> > sql = 'delete from user where name= "xiaoming' > > cursor.execute (sql) 1L > > conn.commit () > > sql = 'select * from user' > cursor.execute (sql) 3L > cursor.fetchall () ((2L, uprizhangsanitary, upright 123456'), (3L, upright lisification, upright 123456'), (4L, upright wangwangwuyan, upright 123456')

13.1.4 query record

> sql = 'select * from user' > cursor.execute (sql) 3L > cursor.fetchone () # get the first record (2L, ubizhangsanitary, upright 123456') > sql =' select * from user' > > cursor.execute (sql) 3L > cursor.fetchmany (2) # get two records ((2L, uprizhangsanitary, upright 123456'), (3L, upright lisification, upright 123456')

13.1.4 return the result in dictionary form

The default display is in tuple form, and to return to dictionary form to make it easier to handle, use the cusorclass parameter in cursor ([cursorclass]).

Pass in the MySQLdb.cursors.DictCursor class:

> cursor = conn.cursor (MySQLdb.cursors.DictCursor) > sql = 'select * from user' > cursor.execute (sql) 3L > cursor.fetchall () ({' password': upright 123456, 'id': 2L,' name': upright Zhangsan'}, {'password': upright 123456,' id': 3L, 'name': utilisi'}, {'password': upright 123456,' id': 4L, 'name': uplift Wangwu'})

13.2 iterate through the query results

#! / usr/bin/env python#-*-coding: utf-8-*-import MySQLdbtry: conn = MySQLdb.Connect (host='127.0.0.1', port=3306, user='root', passwd='123456', connect_timeout=3, charset='utf8') cursor = conn.cursor () sql = "select * from user" cursor.execute (sql) for i in cursor.fetchall (): print iexcept Exception E: print ("Connection Error:" + str (e)) finally: conn.close () # python test.py (2L, uplifting, upright 123456') (3L, upright lisification, upright 123456') (4L, upright wangwuyi, upright 123456')

Use a for loop to iterate over the query results and add exception handling.

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.