The 13th Chapter Python database programming

This section explains the Python operations database, complete simple additions and deletions to work, take the MySQL database as an example.

Python's MySQL database operation module is called MYSQLDB, which requires additional installation.

Install via PIP tool: Pip install MySQLdb

MYSQLDB module, we mainly use the connection database method MySQLdb.connect (), connected to the database, and then use some methods to do the corresponding operation.

MySQLdb.connect (Parameters ...) The methods provide the following common parameters:

Parameters	Describe
Host	Database address
User	Database user name,
passwd	Database password, default is empty
Db	Database library name, no default library
Port	Database port, default 3306
Connect_timeout	Connection time-out, in seconds
Use_unicode	The result is returned as a Unicode string
CharSet	Insert Database Encoding

Connect object returns the Connect () function:

Commit ()	Commits the transaction. For databases and tables that support transactions, if you commit a modification, this method will not be written to the database
Rollback ()	The transaction is rolled back. For databases and tables that support transactions, if this method is executed, the current transaction is rolled back. In the absence of a 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.

The cursor object also provides several methods:

Close ()	Close Cursors
Execute (SQL)	Execute SQL statement
Excutemany (SQL)	Execute multiple SQL statements
Fetchone ()	Take the first record from the execution result
Fetchmany (N)	Fetch n records from the execution result
Fetchall ()	Fetch all records from the execution results
Scroll (self, value, mode= ' relative ')	Cursor scrolling

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

QQ Group:323779636 (shell/python devops Development Group )

13.1 Database Deletion and modification

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 (), Password varchar ()) ">>>  Cursor.execute (SQL)    #  returned number is the number of rows affected 0l    >>> sql =   "Insert into user (Id,name,password)  values (' 1 ', ' xiaoming ', ' 123456 ')" >>>  Cursor.execute (SQL) 1l>>> conn.commit ()   #  commit TRANSACTION, write to database >>>  Cursor.execute (' show tables ')   #  view created Table 1l>>> cursor.fetchall ()   #   Returns all results performed by the previous cursor, which is returned by default in tuples ((U ' user ',),) >>> cursor.execute (' select * from  User ')            1l>>> cursor.fetchall () ( (1l, u ' xiaoming ',  u ' 123456 '),) 

13.1.2 inserting more than one data

>>> sql = ' INSERT INTO user ' (Id,name,password) VALUES (%s,%s,%s) ' >>> args = [(' 2 ', ' Zhangsan ', ' 123456 '), (' 3 ', ' Lisi ', ' 123456 '), (' 4 ', ' Wangwu ', ' 123456 ')] >>> cursor.executemany (sql, args) 3l>>> Conn.commit () >>> sql = ' select * ' from user ' >>> cursor.execute (SQL) 4l>>> Cursor.fetchall () (( 1L, U ' xiaoming ', U ' 123456 '), (2L, U ' Zhangsan ', U ' 123456 '), (3L, U ' Lisi ', U ' 123456 '), (4L, U ' Wangwu ', U ' 123456 '))

The args variable is a list that contains multiple groups, each of which corresponds to each record. When querying multiple records, using this method can effectively improve the efficiency of insertion.

13.1.3 Deleting a record for a user name Xiaoming

>>> sql = ' Delete from user where name= ' xiaoming ' >>> cursor.execute (SQL) 1L&G T;>> conn.commit () >>> sql = ' SELECT * from user ' >>> cursor.execute (SQL) 3 L>>> Cursor.fetchall ((2L, U ' Zhangsan ', U ' 123456 '), (3L, U ' Lisi ', U ' 123456 '), (4L, U ' Wangwu ', U ' 123456 ') ))

13.1.4 Query Records

>>> sql = ' select * ' from user ' >>> cursor.execute (SQL) 3l>>> cursor.fetchone () # Get first Record (2L, U ' Zhangsan ', U ' 123456 ') >>> sql = ' select * ' from user ' >>> cursor.execute (SQL) 3l>> > Cursor.fetchmany (2) # Get Two records ((2L, U ' Zhangsan ', U ' 123456 '), (3L, U ' Lisi ', U ' 123456 '))

13.1.4 returning results as a dictionary

The default display is the tuple form, which is used to return the dictionary form, making it easier to work with the Cusorclass parameter in the cursor ([Cursorclass]). Incoming MySQLdb.cursors.DictCursor class:>>> cursor = Conn.cursor (MySQLdb.cursors.DictCursor) >>> sql = ' SELECT * from user ' >>> cursor.execute (SQL) 3l>>> cursor.fetchall ({' Password ': U ' 123456 ', ' id ': 2L, ' Name ': U ' Zhangsan '}, {' Password ': U ' 123456 ', ' id ': 3L, ' name ': U ' Lisi '}, {' Password ': U ' 123456 ', ' id ': 4L, ' name ': U ' wangw U '})

13.2 Traversing 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, u ' Zhangsan ',  u ' 123456 ') (3l, u ' Lisi ',  u ' 123456 ') (4l, u ' Wangwu ',  u ' 123456 ') 

Uses a For loop to iterate through the results of the query and adds exception handling.

This article is from the "Li Zhenliang Technology Blog" blog, make sure to keep this source http://lizhenliang.blog.51cto.com/7876557/1874283

The 13th Chapter Python database programming