Use mysql database in python for details, pythonmysql

Use mysql database in python for details, pythonmysql

1. Install mysql

If you are a windows user, mysql installation is very simple. Download the installation file directly and double-click the installation file to proceed step by step.

The installation in Linux may be simpler. Apart from downloading the installation package for installation, mysql is usually installed in the linux repository. You can download and install it by using only one command:

Ubuntu \ deepin
Copy codeThe Code is as follows:
> Sudo apt-get install mysql-server

> Sudo apt-get install mysql-client

CentOS/redhat
Copy codeThe Code is as follows:
> Yum install mysql

Ii. Install MySQL-python

To enable python to operate mysql, you need the MySQL-python driver, which is an essential module for python to operate mysql.

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

Download the mysql-python-1.2.5.zip file and decompress it directly. Go to the MySQL-python-1.2.5 directory:
Copy codeThe Code is as follows:
> Python setup. py install

Iii. Test

The test is very simple. Check whether the MySQLdb module can be imported normally.
Copy codeThe 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

If no error is reported, the MySQLdb module cannot be found. The installation is OK. Before using python to operate the database, we need to review the basic mysql operations:

Iv. Basic mysql operations

Copy codeThe Code is as follows:
$ Mysql-u root-p (with password)
$ Mysql-u root (when no password is available)

Copy codeThe Code is as follows:
Mysql> show databases; // view all databases currently
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Csvt |
| Csvt04 |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
6 rows in set (0.18 sec)

Mysql> use test; // role and test Database
Database changed
Mysql> show tables; // view the table in the test Database
Empty set (0.00 sec)

// Create the user table, name and password fields
Mysql> create table user (name VARCHAR (20), password VARCHAR (20); Query OK, 0 rows affected (0.27 sec)

// Insert several data entries into the user table
Mysql> insert into user values ('Tom ', '123 ');
Query OK, 1 row affected (0.05 sec)

Mysql> insert into user values ('alen', '123 ');
Query OK, 1 row affected (0.08 sec)

Mysql> insert into user values ('jack', '123 ');
Query OK, 1 row affected (0.04 sec)

// View the data in the user table
Mysql> select * from user;
+ ------ + ---------- +
| Name | password |
+ ------ + ---------- +
| Tom | 1, 1321 |
| AlN | 7875 |
| Jack| 7455 |
+ ------ + ---------- +
3 rows in set (0.01 sec)

// Delete data whose name is equal to Jack
Mysql> delete from user where name = 'jack ';
Query OK, 1 rows affected (0.06 sec)

// Change the password with name equal to Alen to 1111
Mysql> update user set password = '000000' where name = 'alen ';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

// View the table content
Mysql> select * from user;
+ -------- + ---------- +
| Name | password |
+ -------- + ---------- +
| Tom | 1, 1321 |
| AlN | 1111 |
+ -------- + ---------- +
3 rows in set (0.00 sec)

V. python basics for mysql database operations

Copy codeThe Code is as follows:
# Coding = UTF-8
Import MySQLdb

Conn = MySQLdb. connect (
Host = 'localhost ',
Port = 3306,
User = 'root ',
Passwd = '000000 ',
Db = 'test ',
)
Cur = conn. cursor ()

# Creating a data table
Export cur.exe cute ("create table student (id int, name varchar (20), class varchar (30), age varchar (10 ))")

# Insert a data entry
Export cur.exe cute ("insert into student values ('2', 'Tom ', '3 year 2 class', '9 ')")

# Modify the data of a query Condition
Export cur.exe cute ("update student set class = '3 year 1 class' where name = 'Tom '")

# Deleting data with query Conditions
Export cur.exe cute ("delete from student where age = '9 '")

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

Copy codeThe Code is as follows:
>>> Conn = MySQLdb. connect (host = 'localhost', port = 3306, user = 'root', passwd = '000000', db = 'test ',)

The Connect () method is used to create a database connection. You can specify parameters such as user name, password, and host information.

This is only connected to the database. To operate the database, you need to create a cursor.
Copy codeThe Code is as follows:
>>> Cur = conn. cursor ()

Create a cursor by using the cursor () method under the database connection conn obtained.
Copy codeThe Code is as follows:
>>> Cur.exe cute ("create table student (id int, name varchar (20), class varchar (30), age varchar (10 ))")

You can use the cursor cur to operate the execute () method to write pure SQL statements. Use the execute () method to write SQL statements for data operations.

Copy codeThe Code is as follows:
>>> Cur. close ()

Cur. close () close the cursor
Copy codeThe Code is as follows:
>>> Conn. commit ()

The conn. commit () method must be used to commit a transaction and insert a data entry into the database. Otherwise, the data will not be inserted.
Copy codeThe Code is as follows:
>>> Conn. close ()

Conn. close () close database connection

6. insert data

It is not convenient to insert data by writing pure SQL statements in the execute () method above. For example:
Copy codeThe Code is as follows:
>>> Cur.exe cute ("insert into student values ('2', 'Tom ', '3 year 2 class', '9 ')")

To insert new data, you must modify the value in this statement. We can make the following changes:

Copy codeThe Code is as follows:
# Coding = UTF-8
Import MySQLdb

Conn = MySQLdb. connect (
Host = 'localhost ',
Port = 3306,
User = 'root ',
Passwd = '000000 ',
Db = 'test ',
)
Cur = conn. cursor ()

# Insert a data entry
Sqli = "insert into student values (% s, % s )"
Cur.exe cute (sqli, ('3', 'huhu', '2 year 1 class', '7 '))

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

What if I want to insert multiple values to the data table at a time?

Copy codeThe Code is as follows:
# Coding = UTF-8
Import MySQLdb

Conn = MySQLdb. connect (
Host = 'localhost ',
Port = 3306,
User = 'root ',
Passwd = '000000 ',
Db = 'test ',
)
Cur = conn. cursor ()

# Insert multiple records at a time
Sqli = "insert into student values (% s, % s )"
Cur.exe cute.pdf (sqli ,[
('3', 'Tom ', '1 year 1 class', '6 '),
('3', 'jack', '2 year 1 class', '7 '),
('3', 'aheng', '2 year 2 class', '7 '),
])

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

The executemany () method can insert multiple values at a time and execute a single-pick SQL statement. However, if you repeat the parameters in the parameter list, the returned value is the number of affected rows.

VII. query data

Maybe you have tried
Copy codeThe Code is as follows:
>>> Cur.exe cute ("select * from student ")

But it does not print the data in the table.

Let's see what this statement gets.
Copy codeThe Code is as follows:
>>> Aa=cur.exe cute ("select * from student ")

>>> Print aa

5

It only obtains the number of data entries in our table. How can we get the data in the table? Go to python shell

Copy codeThe Code is as follows:
>>> Import MySQLdb
>>> Conn = MySQLdb. connect (host = 'localhost', port = 3306, user = 'root', passwd = '000000', db = 'test ',)
>>> Cur = conn. cursor ()
>>> Cur.exe cute ("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 obtain the data in the table, but each execution of cur. the data obtained by fetchone () is different. In other words, if I did not execute it once, the cursor will move from the first data in the table to the next data location. Therefore, when I run the command again, I get the second data.

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

We still didn't solve the problem. How can we get and print multiple data records in the table?

Copy codeThe Code is as follows:
# Coding = UTF-8
Import MySQLdb

Conn = MySQLdb. connect (
Host = 'localhost ',
Port = 3306,
User = 'root ',
Passwd = '000000 ',
Db = 'test ',
)
Cur = conn. cursor ()

# Obtain the number of data entries in the table
Aa=cur.exe cute ("select * from student ")
Print aa

# Print the data in the table
Info = cur. fetchmany (aa)
For ii in info:
Print ii
Cur. close ()
Conn. commit ()
Conn. close ()

With the previous print aa, we know that the current table has five data records. The fetchiterator () method can obtain multiple data records, but you need to specify the number of data records, you can print multiple pieces of data through a for loop! The execution result is as follows:
Copy codeThe 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, 'aheng', '2 year 2 class', '7 ')
[Finished in 0.1 s]