Python database programming

Explain the Python operation database, complete the simple deletion and modification work, take 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:

Connect object returns the Connect () function:

The cursor object also provides several methods:

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 ())" &G t;>> cursor.execute (SQL)   # The number returned 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 the created table 1l>>> Cursor.fetchall ()  # Returns all the results of the previous cursor execution, which is returned by default as a tuple ( 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>>> Conn.commit () >>> sql = ' select * ' from user '                   >>> cursor.execute (SQL)       3l>>> 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 the first record (2L, U ' Zhangsan ', U ' 123456 ') >>> sql = ' select * ' from user ' >>> cursor.execute (SQL)         3l>& Gt;> Cursor.fetchmany (2) # gets 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.

