Python database programming

Python database programming explains how to operate a database in Python and complete simple addition, deletion, modification, and query. take MySQL database as an example.

The MySQL database operation module of Python is called MySQLdb, which requires additional installation.

Install the SDK using pip: pip install MySQLdb

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

The MySQLdb. Connect (parameters...) method provides the following common parameters:

13.1 add, delete, modify, and query databases

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

>>> Conn = MySQLdb. connect (host = '2017. 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.exe cute (SQL) # The number returned is the number of affected rows. 0L >>> SQL = "insert into user (id, name, password) values ('1', 'xiaoming', '123 ') ">>> cursor.exe cute (SQL) 1L >>> conn. commit () # submit a transaction and write it to the database >>> cursor.exe cute ('Show tables ') # view the created table 1L >>> cursor. fetchall () # returns all results executed by the previous cursor. The result is returned in the form of tuples by default (u 'user',),) >>> cursor.exe cute ('select * from user') 1L >>> cursor. fetchall () (1L, u'xiaoming ', u'000000 '),)

13.1.2 insert multiple data entries

>>> 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 containing multiple groups, each of which corresponds to each record. When querying multiple records, this method can effectively improve the insertion efficiency.

13.1.3 delete the record with the username 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.exe cute (SQL) 3L >>> cursor. fetchone () # obtain the first record (2L, u'zhangsan', u'000000') >>> SQL = 'select * from user' >>> cursor.exe cute (SQL) 3L >>> cursor. fetchsan (2) # obtain two Records (2L, u'zhangsan', u'000000'), (3L, u'lisi', u'000000 '))

13.1.4 return results in dictionary form

The default display is in the form of tuples. to return the dictionary form for easier processing, the cusorclass parameter in cursor ([cursorclass]) is used. Input MySQLdb. cursors. dictCursor class: >>> cursor = conn. cursor (MySQLdb. cursors. dictCursor) >>> SQL = 'select * from user' >>> cursor.exe cute (SQL) 3L >>> cursor. fetchall () ({'password': u'000000', 'id': 2L, 'name': u'zhangsan'}, {'password': u'000000 ', 'id': 3L, 'name': u'lisi'}, {'password': u'000000', 'id': 4L, 'name ': u'wangwu '})

13.2 traverse 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')

Use the for loop to traverse the query results and add exception handling.