Python connection to MySQL operation

One, install MySQL

If you are a Windows user, MySQL installation is very simple, download the installation files directly, double-click the installation file step by steps.

Linux installation may be more simple, in addition to download the installation package for installation, the General Linux warehouse will have MySQL, we only need a command to download the installation:

Ubuntu\deepin

>>sudo Apt-get Install Mysql-server

>>sudo Apt-get Install Mysql-client

Centos/redhat

>>yum install MySQL

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

Three, test

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

[email protected]:~/pyse$ python python 2.7.4 (default, Sep, 03:20:56) [GCC 4.7.3] on Linux2type ' help ', ' Copyrigh T "," credits "or" license "for more information.>>> import MySQLdb

No error message MySQLdb module can not be found, instructions to install OK, below to start using Python to manipulate the database, we need to review the basic MySQL operation:

Four, the basic operation of MySQL

$ mysql-u root-p (when password is available)

$ mysql-u root (without password)

mysql> show databases; View all current databases +--------------------+| Database |+--------------------+| Information_schema | | CSVT | | csvt04 | | MySQL | | Performance_schema | |   Test |+--------------------+6 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 |+------+----------+3 rows in Set (0.01 sec)//delete NAMe equals 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 |+--------+----------+3 rows in Set (0.00 sec)

Five, python operation MySQL database base

#coding =utf-8import mysqldbconn= mysqldb.connect (        host= ' localhost ',        port = 3306,        user= ' root ',        Passwd= ' 123456 ',        db = ' Test ', '        cur = conn.cursor () #创建数据表 #cur.execute ("CREATE TABLE student (ID int, name varchar (a), class varchar (2), age varchar (+)) ") #插入一条数据 #cur.execute (" INSERT into student values (' 3 ', ' 2 class ', ' 9 ') ', ' ". ') #修改查询条件的数据 #cur.execute ("Update student set class= ' 3 Year 1 class ' WHERE name = ' Tom '") #删除查询条件的数据 #cur.execute ("delete From student where age= ' 9 ' ") Cur.close () Conn.commit () Conn.close ()

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

The Connect () method is used to create a connection to a database where parameters can be specified: User name, password, host, and so on.

This is only connected to the database and you need to create a cursor to manipulate the database.

>>> cur = conn.cursor ()

Create a cursor by getting to the cursor () method under the database connection conn.

>>> cur.execute ("CREATE TABLE student (ID int, name varchar (), class varchar (10), age varchar") ")

The Execute () method through the cursor cur operation can write to a pure SQL statement. Manipulate the data by writing it in the Execute () method, such as an SQL statement.

>>>cur.close ()

Cur.close () Close cursor

>>>conn.commit ()

The Conn.commit () method commits the thing and must have this method when inserting a piece of data into the database, otherwise the data is not actually inserted.

>>>conn.close ()

Conn.close () to close the database connection

Six, 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?

#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 ()

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.

Seven. Querying data

Maybe you've tried it in Python.

>>>cur.execute ("SELECT * FROM Student")

To query data in a data table, but it does not print out the data in the table, some disappointment.

Let's see what this statement is getting.

>>>aa=cur.execute ("SELECT * FROM Student")

>>>print AA

5

It only gets the number of data in our table. So how do you get the data from the table? Enter the Python shell

>>> Import Mysqldb>>> conn = mysqldb.connect (host= ' localhost ', port = 3306,user= ' root ',    

The Fetchone () method helps us to get the data in the table, but each time we execute Cur.fetchone () The data is different, in other words I did not do it once, and the cursor moves from the first data in the table to the next data position, so When I do it again, I get the second piece of data.

The scroll (0, ' absolute ') method can position the cursor to the first data in the table.

Or does it not solve the result we want, how to get multiple data in the table and print it?

#coding =utf-8import mysqldbconn= mysqldb.connect (        host= ' localhost ',        port = 3306,        user= ' root ',        Passwd= ' 123456 ',        db = ' Test ', '        cur = conn.cursor () #获得表中有多少条数据aa =cur.execute ("SELECT * from student") Print aa# How much data in the printed table info = cur.fetchmany (aa) for II in info:    print iicur.close () conn.commit () Conn.close ()

Through the previous print AA we know that the current table has 5 data, the Fetchmany () method can get multiple data, but need to specify the number of data, through a for loop can be more than one data to print out! The results of the implementation are as follows:

5 (1L, ' Alen ', ' 1 Year 2 class ', ' 6 ') (3L, ' Huhu ', ' 2 year 1 class ', ' 7 ') (3L, ' Tom ', ' 1 Year 1 class ', ' 6 ') (3L, ' Jack ', ' 2 y Ear 1 class ', ' 7 ') (3L, ' Yaheng ', ' 2 Year 2 class ', ' 7 ') [Finished in 0.1s]

Python connection to MySQL operation