Python python operation MySQL

One, install MySQL

Here is not a small part of the detailed introduction, if there is not installed can refer to the following blog

Http://www.cnblogs.com/wj-1314/p/7573242.html

Second, install Mysql-python

To enable Python to operate MySQL requires the Mysql-python driver, which is a necessary module for Python to operate MySQL.

: https://pypi.python.org/pypi/MySQL-python/

Unzip directly after downloading the Mysql-python-1.2.5.zip file. Enter the mysql-python-1.2.5 directory:

>>python setup.py Install

Then install Pymysql

Pip Install Pymysql

　　

Third, test the Pymysql module

The test is very simple and checks whether the MySQLdb module can be imported properly.

No error notification MYSQLDB module not found, instructions to install OK

Four,MySQLthe basic Operation

mysql> show databases; View all current databases +--------------------+| Database |+--------------------+| Information_schema | | CSVT | | csvt04 | | MySQL | | Performance_schema | |   Test |+--------------------+rows in Set (0.18 sec) mysql> use test;   function and test database changedmysql> show tables; View the table below the test library empty Set (0.00 sec)//Create a user table, name and password two fields mysql> create table user (name VARCHAR, password VAR  CHAR (20)); Query OK, 0 rows affected (0.27 sec)//inserts several data into the user table mysql> insert into user values (' Tom ', ' 1321 '); Query OK, 1 row affected (0.05 sec) mysql> insert into user values (' Alen ', ' 7875 '); Query OK, 1 row affected (0.08 sec) mysql> insert into user values (' Jack ', ' 7455 '); Query OK, 1 row affected (0.04 sec)//view data for user table mysql> SELECT * FROM user;+------+----------+| name | Password |+------+----------+| Tom | 1321 | | Alen | 7875 | | Jack | 7455 |+------+----------+rows in Set (0.01 sec)//delete name etc.To Jack's data mysql> delete from user where name = ' Jack '; Query OK, 1 rows affected (0.06 sec)//modify name equals alen password for 1111mysql> update user set password= ' 1111 ' WHERE name = ' Alen '; Query OK, 1 row affected (0.05 sec) Rows matched:1 changed:1 warnings:0//View table Contents mysql> SELECT * from user;+--------+- ---------+| name | Password |+--------+----------+| Tom | 1321 | | Alen | 1111 |+--------+----------+rows in Set (0.00 sec)

　　

Five,python operation mysql database base 1, execute SQL, the following statement:

#coding =utf-8import MySQLdb
# Open Database connection

conn= mysqldb.connect (        host= ' localhost ',        port = 3306,        user= ' root ',        passwd= ' 123456 ',        db = ' Test ' ,        )

cur = conn.cursor () #创建数据表 #cur.execute ("CREATE TABLE student (ID int, name varchar, class varchar (), age varchar (10)) ") #插入一条数据 #cur.execute (" INSERT into student values (' 2 ', ' Tom ', ' 3 Year 2 class ', ' 9 ') ") #修改查询条件的数据 #cur.execute (" Update Student set class= ' 3 Year 1 class ' WHERE name = ' Tom '] #删除查询条件的数据 #cur.execute ("Delete from student where age= ' 9 '")
# Close Cursor Cur.close ()

# Commit, otherwise unable to save new or modified data conn.commit ()

# Close Database connection conn.close ()

2, get the newly created data self-increment ID

Import PYMYSQL  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123 ', db= ' t1 ') cursor = Conn.cursor () Cursor.executemany ("INSERT into hosts (host,color_id) VALUES (%s,%s)", [("1.1.1.11", 1), ("1.1.1.11", 2)]) Conn.commit () Cursor.close () Conn.close ()  # Get the latest auto-increment idnew_id = Cursor.lastrowid

3. Get Query data

Import PYMYSQL  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123 ', db= ' t1 ') cursor = Conn.cursor () cursor.execute ("SELECT * from hosts")  # Gets the first row of data Row_1 = Cursor.fetchone ()  # Gets the first n rows of data # row_2 = Cursor.fetchmany (3) # Get all data # Row_3 = Cursor.fetchall ()  conn.commit () cursor.close () Conn.close ()

Fetchone () method can help us get the data in the table, but each execution Cur.fetchone () get different data, in other words I didn't do it once, the cursor moves from the first data in the table to the next data position, so I get the second piece of data when I execute it again.

Note: In order to fetch data, you can use Cursor.scroll (Num,mode) to move the cursor position, such as:

• Cursor.scroll (1,mode= ' relative ') # moves relative to the current position
• Cursor.scroll (2,mode= ' absolute ') # relative absolute position movement

4. Fetch data type

The data that is obtained by default is the Ganso type, if desired or the dictionary type of data, i.e.:

Import PYMYSQL  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123 ', db= ' t1 ')  # Cursor set to dictionary type cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) R = Cursor.execute ("Call P1 ()")  result = Cursor.fetchone ()  conn.commit () cursor.close () Conn.close ()

5, inserting data

It is not convenient to insert data by writing a pure SQL statement in the Execute () method above. Such as:

>>>cur.execute ("INSERT into student values (' 2 ', ' Tom ', ' 3 Year 2 class ', ' 9 ')")

If I want to insert new data, I have to modify the value in this statement. We can make the following changes:

#coding =utf-8import mysqldbconn= mysqldb.connect (        host= ' localhost ',        port = 3306,        user= ' root ',        Passwd= ' 123456 ',        db = ' Test ', '        cur = conn.cursor () #插入一条数据sqli = "INSERT into student values (%s,%s,%s,%s)" Cur.execute (Sqli, (' 3 ', ' Huhu ', ' 2 year 1 class ', ' 7 ')) Cur.close () Conn.commit () Conn.close ()

What if you want to insert more than one value into a data table at a time?

The Executemany () method can insert multiple values at a time, execute a single-heads SQL statement, but repeatedly execute parameters in the parameter list, with the returned value being the number of rows affected.

#coding =utf-8import mysqldbconn= mysqldb.connect (        host= ' localhost ',        port = 3306,        user= ' root ',        Passwd= ' 123456 ',        db = ' Test ', '        cur = conn.cursor () #一次插入多条记录sqli = "INSERT into student values (%s,%s,%s,%s)" Cur.executemany (sqli,[    (' 3 ', ' Tom ', ' 1 Year 1 class ', ' 6 '),    (' 3 ', ' Jack ', ' 2 year 1 class ', ' 7 '),    (' 3 ', ' Yaheng ', ' 2 Year 2 class ', ' 7 '),    ]) cur.close () Conn.commit () Conn.close ()

　　　

Six, consolidation exercises

Exercises:
Reference table structure:
User type

User Information

Permissions

User Type & Permissions
Function:

# login, register, retrieve password
# User Management
# User Type
# Rights Management
# Assigning permissions

Special: The program is only one executable file

Python python operation MySQL