Python mysqldb module

Using Python to operate the MySQL database, you will use the MySQLdb module, which is actually the same way as the file database SQLite operation

Let's take a look at this simple and practical module

Inserting data

Import mysqldb# Create a MySQL Connection object conn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 1234 ', db= ' mydb ') #创建操作游标, All operations on the database are called methods in the cursor cur = conn.cursor () "" "note here that unlike other modules is the placeholder for the parameter in value, and in MySQL no matter what the value is, the placeholder is only a pattern of%s. This has to be remembered ~~!! "" "Recount =cur.execute (' INSERT into UserInfo (name,age) VALUES (%s,%s) ', (' Alex ')) #执行插入语句会返回成功插入的条目数. The number of entries can be printed out print recountconn.commit () cur.close () Conn.close ()

This code is a bit of a database base can be read, in fact, in the Cur.execute () method in parentheses to write the SQL statement to execute it just fine.

If you have more than one statement to insert, the simplest way to insert the data is by using a for loop. But the module comes with the Executemany method, which makes it more convenient to implement the function of inserting multiple statements simultaneously.

Import Mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 1234 ', db= ' mydb ') cur = conn.cursor () # In tuples, save the values you want to insert, Li =[(' Alex ', ' USA '), (' SB ', ' USA '), "#这里改成调用executemany () method to manipulate multiple data at once recount = Cur.executemany (' I Nsert into UserInfo (name,address) VALUES (%s,%s) ', Li ' Conn.commit () cur.close () conn.close () print recount

The query operation is our most common database operation, the module also gives us a lot of options

Query operations

Import Mysqldbconn = MySQLdb.connect (host= ' 192.168.10.105 ', user= ' WGW ', passwd= ' 123456 ', db= ' wgwtest ') cur = Conn.cursor () recount = Cur.execute (' select * from User_list ') #通过fetchone方法获取一条结果print cur.fetchone () print ' Select%s Lins '%recount

The result of the above code execution is

(1L, 1L, ' WGW ', ' 123456 ', 0L) Select 3 Lins

The results returned were 3 data, and we only got one. What's going on? This is because we have used the Fetchone method. This method does not matter how many data is queried, it only takes the first one. In addition, we see that there are a lot of L in the query results, and this L is the number type that we query out is long int length shaping.

So what if we're going to get all the query results? Then use the Fetchall () method

#!/usr/bin/env python#-*-coding:utf-8-*-import mysqldbconn = MySQLdb.connect (host= ' 192.168.10.105 ', user= ' WGW ', Passwd= ' 123456 ', db= ' wgwtest ') cur = conn.cursor () recount = Cur.execute (' select * from User_list ') # Get all results by Fetchall method Print Cur.fetchall () print ' Select%s lins '%recount

The results of this implementation are as follows

((1L, 1L, ' WGW ', ' 123456 ', 0L), (2L, 2L, ' Alex ', ' 123456 ', 1L), (3L, 3L, ' Eric ', ' 123456 ', 0L)) Select 3 Lins

All data entries are queried, and the result of Fetchall is that a large tuple contains all the data entries, each of which is represented by a small tuple. But the way the tuple is nested is also cumbersome. For example, we want to take the user name of the field value, it needs to slice the contents of the tuple, first cut large and then small. Is it a lot of trouble? We also have a way to solve this. The problem is being on a cursor object.

The statement we use when we create the object of the cursor is

cur = conn.cursor ()

In this mode, the results obtained by default are presented in tuples. Now let's change the method of creating an object

cur = conn.cursor (Cursorclass = MySQLdb.cursors.DictCursor)

This way specifies that the cursor returns the data as a dictionary, and we look at the effect.

First look at Fetchone.

#!/usr/bin/env python#-*-coding:utf-8-*-import mysqldbconn = MySQLdb.connect (host= ' 192.168.10.105 ', user= ' WGW ', Passwd= ' 123456 ', db= ' wgwtest ') #cur = Conn.cursor () #指定游标的数据返回类型cur = conn.cursor (Cursorclass = MySQLdb.cursors.DictCursor) recount = Cur.execute (' select * from User_list ') #通过fetchall方法获取全部结果print Cur.fetchall () print ' Select%s lines '%recount

The operation results are as follows

{' Tid ': 1L, ' passwd ': ' 123456 ', ' login_count ': 0L, ' uid ': 1L, ' name ': ' WGW '}select 3 lines

The returned query result becomes the dictionary form, the dictionary key is the field name, value is the field value we query. This allows us to get the user name by fetchone[' name '. It's a lot easier to slice.

And look at the effect of Fetchall.

#!/usr/bin/env python#-*-coding:utf-8-*-import mysqldbconn = MySQLdb.connect (host= ' 192.168.10.105 ', user= ' WGW ', Passwd= ' 123456 ', db= ' wgwtest ') #cur = Conn.cursor () #指定游标的数据返回类型cur = conn.cursor (Cursorclass = MySQLdb.cursors.DictCursor) recount = Cur.execute (' select * from User_list ') #通过fetchall方法获取全部结果print Cur.fetchall () print ' Select%s lines '%recount

The results of the operation are as follows:

({' Tid ': 1L, ' passwd ': ' 123456 ', ' login_count ': 0L, ' uid ': 1L, ' name ': ' WGW '}, {' Tid ': 2L, ' passwd ': ' 123456 ', ' Login_coun '  T ': 1L, ' uid ': 2L, ' name ': ' Alex '}, {' Tid ': 3L, ' passwd ': ' 123456 ', ' login_count ': 0L, ' uid ': 3L, ' name ': ' Eric '} ' select 3 Lines

Look at the results the contents of each data entry are already represented in a dictionary, but the Fetchall () method is encapsulated in the outermost layer of the result with a tuple. There's no way to do it, but it's much better than the original pattern.

Using the MySQLdb module, the personal feeling is 3 points

1. The cursor gets the type specified as the dictionary type

2, if the query results only one to use the Fetchone () method, so that you can less processing a layer of tuples

3, learn to learn the database SQL language, the core of this module is to call the SQL implementation of the database. It's not written in SQL, and the rest is blind.

This article from "Thunderbolt Tofu" blog, declined reprint!

Python mysqldb module