Use MySQL database in Python to introduce _python in detail

First, install MySQL

If it is a Windows user, MySQL installation is very simple, download the installation files directly, double-click the installation file step-by-step operation can be.

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

Ubuntu\deepin

Copy Code code as follows:

>>sudo Apt-get Install Mysql-server

>>sudo Apt-get Install Mysql-client

Centos/redhat

Copy Code code as follows:

>>yum install MySQL

Second, install Mysql-python

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

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

After downloading the Mysql-python-1.2.5.zip file, unzip it directly. Enter mysql-python-1.2.5 directory:

Copy Code code as follows:

>>python setup.py Install

Third, testing

The test is very simple, check whether the MySQLdb module can be imported normally.

Copy Code code as follows:

fnngj@fnngj-h24x:~/pyse$ python
Python 2.7.4 (default, Sep 26 2013, 03:20:56)
[GCC 4.7.3] on linux2
Type ' help ', ' copyright ', ' credits ' or ' license ' for the more information.
>>> Import MySQLdb

No error prompts MySQLdb module can not find, description installation OK, the following start using Python operations database, we need to review the basic operation of MySQL:

Four, the basic operation of MySQL

Copy Code code as follows:

$ mysql-u root-p (with password)
$ mysql-u root (when no password)

Copy Code code as follows:

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
Database changed
Mysql> 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 VARCHAR (20)); Query OK, 0 rows affected (0.27 sec)

Inserting 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 from the 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 equal to Alen password to 1111
mysql> 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)

Python Operation MySQL Database basics

Copy Code code as follows:

#coding =utf-8
Import MySQLdb

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")

Cur.close ()
Conn.commit ()
Conn.close ()

Copy Code code as follows:

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

Connect () method to create a database connection, which can specify parameters: User name, password, host and other information.

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

Copy Code code as follows:

>>> cur = conn.cursor ()

Creates a cursor by conn the cursor () method under the database connection to which it was obtained.

Copy Code code as follows:

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

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

Copy Code code as follows:

>>>cur.close ()

Cur.close () Close cursor

Copy Code code as follows:

>>>conn.commit ()

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

Copy Code code as follows:

>>>conn.close ()

Conn.close () Close database connection

Vi. insertion of data

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

Copy Code code as follows:

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

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

Copy Code code as follows:

#coding =utf-8
Import MySQLdb

Conn= 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 the datasheet at a time?

Copy Code code as follows:

#coding =utf-8
Import MySQLdb

Conn= 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 more than one value at a time, execute a singled out SQL statement, but repeat the parameters in the parameter list, returning the value to the number of rows affected.

Vii. query data

Maybe you've tried it in Python.

Copy Code code as follows:

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

To query the data in the datasheet, but it doesn't print out the data in the table, and it's a bit disappointing.

Let's see what this statement gets.

Copy Code code as follows:

>>>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 in the table? into the Python shell

Copy Code code as follows:

>>> Import MySQLdb
>>> conn = mysqldb.connect (host= ' localhost ', port = 3306,user= ' root ', passwd= ' 123456 ', db = ' test ')
>>> cur = conn.cursor ()
>>> Cur.execute ("SELECT * FROM Student")
5L
>>> Cur.fetchone ()
(1L, ' Alen ', ' 1 Year 2 class ', ' 6 ')
>>> Cur.fetchone ()
(3L, ' Huhu ', ' 2 year 1 class ', ' 7 ')
>>> Cur.fetchone ()
(3L, ' Tom ', ' 1 Year 1 class ', ' 6 ')
...
>>>cur.scroll (0, ' absolute ')

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

The scroll (0, ' absolute ') method navigates the cursor to the first piece of data in the table.

Still not solve the results we want, how to get the table of multiple data and print it out?

Copy Code code as follows:

#coding =utf-8
Import MySQLdb

Conn= MySQLdb.connect (
host= ' localhost ',
Port = 3306,
User= ' Root ',
Passwd= ' 123456 ',
db = ' Test ',
)
cur = conn.cursor ()

#获得表中有多少条数据
Aa=cur.execute ("SELECT * FROM Student")
Print AA

#打印表中的多少数据
info = cur.fetchmany (aa)
For II in Info:
Print II
Cur.close ()
Conn.commit ()
Conn.close ()

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

Copy Code code 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 year 1 class ', ' 7 ')
(3L, ' Yaheng ', ' 2 Year 2 class ', ' 7 ')
[Finished in 0.1s]