Use MySQL database in Python for detailed description

First, 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

The code is as follows:

>>sudo Apt-get Install Mysql-server

>>sudo Apt-get Install Mysql-client

Centos/redhat

The code is as follows:

>>yum install MySQL

Second, installation Mysql-python

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

Download Address: 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:

The code is as follows:

>>python setup.py Install

Third, testing

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

The code is 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 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:

Iv. Basic MySQL operation

The code is as follows:

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

The code is 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 for the user table
Mysql> select * from user;
+------+----------+
| name | password |
+------+----------+
| Tom | 1321 |
| Alen | 7875 |
| Jack | 7455 |
+------+----------+
3 rows in Set (0.01 sec)

Delete the name equals jack data
mysql> Delete from user where name = ' Jack ';
Query OK, 1 rows affected (0.06 sec)

Modify the password of name equals alen 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)

V. Python Operation MySQL Database Foundation

The code is 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 (10), age varchar") ")

#插入一条数据
#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 ()

The code is as follows:

>>> 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.

The code is as follows:

>>> cur = conn.cursor ()

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

The code is as follows:

>>> 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.

The code is as follows:

>>>cur.close ()

Cur.close () Close cursor

The code is as follows:

>>>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.

The code is as follows:

>>>conn.close ()

Conn.close () to close the database connection

Vi. Inserting data

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

The code is 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 modify the value in this statement. We can make the following changes:

The code is 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 a data table at a time?

The code is 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 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.

Vii. Querying data

Maybe you've tried it in Python.

The code is as follows:

>>>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.

The code is 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 from the table? Enter the Python shell

The code is 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 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?

The code is 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 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:

The code is 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]

