Shulou(Shulou.com)06/01 Report--

Let's learn about Python operation using MySQL database method. I believe everyone will benefit a lot after reading it. The text is not much in essence. I hope that Python operation uses MySQL database method. This short article is what you want.

First, install mysql

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

The installation under Linux may be easier. In addition to downloading the installation package for installation, mysql is available in most linux repositories. We only need to download and install it with one command:

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, MySQL-python driver is needed, which is an indispensable module for python to operate mysql.

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

Download the MySQL-python-1.2.5.zip file and extract it directly. Enter the MySQL-python-1.2.5 directory:

> > python setup.py install

Third, testing

The test is simple enough to check that the MySQLdb module can be imported properly.

Fnngj@fnngj-H24X:~/pyse$ python Python 2.7.4 (default, Sep 26 2013, 03:20:56) [GCC 4.7.3] on linux2Type "help", "copyright", "credits" or "license" for more information. > > import MySQLdb

There is no error indicating that the MySQLdb module cannot be found, indicating that OK is installed. Before we start to use python to operate the database, we need to review the basic operations of mysql:

Fourth, the basic operation of mysql

$mysql-u root-p (when there is a password)

$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 with test database Database changedmysql > show tables; / / View table Empty set (0.00 sec) / / create user table under test library. Name and password fields mysql > CREATE TABLE user (name VARCHAR (20), password VARCHAR (20)); Query OK, 0 rows affected (0.27 sec) / / insert several pieces of data mysql > insert into user values ('Tom','1321') into user table Query OK, 1 row affected (0.05sec) mysql > insert into user values ('Alen','7875'); Query OK, 1 row affected (0.08sec) mysql > insert into user values (' Jack','7455'); Query OK, 1 row affected (0.04sec) / / View user table data mysql > select * from user +-+-+ | name | password | +-+-+ | Tom | 1321 | Alen | 7875 | Jack | 7455 | +-+-+ 3 rows in set (0.01sec) / delete data mysql where name equals Jack > delete from user where name = 'Jack' Query OK, 1 rows affected (0.06 sec) / / modify the password where name equals Alen as 1111mysql > update user set password='1111' where name = 'Alen';Query OK, 1 row affected (0. 05 sec) Rows matched: 1 Changed: 1 Warnings: 0 Rows matched / View table contents mysql > select * from user +-+-+ | name | password | +-+-+ | Tom | 1321 | | Alen | 1111 | +-+-+ 3 rows in set (0.00 sec)

Fifth, the basis of python operation of mysql database

# coding=utf-8import MySQLdbconn= MySQLdb.connect (host='localhost', port = 3306, user='root', passwd='123456', db = 'test',) cur = conn.cursor () # create a data table # cur.execute ("create table student (id int, name varchar (20), class varchar (30), age varchar (10)") # insert a piece of data # cur.execute ("insert into student values '3 year 2 class','9')) # modify data for query conditions # cur.execute ("update student set class='3 year 1 class' where name =' Tom'") # Delete data for query conditions # cur.execute ("delete from student where age='9'") cur.close () conn.commit () conn.close ()

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

The Connect () method is used to create a connection to the database, where you can specify parameters: user name, password, host, and so on.

This is just a connection to the database, and you need to create a cursor to manipulate the database.

> > cur = conn.cursor ()

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

> cur.execute ("create table student (id int, name varchar (20), class varchar (30), age varchar (10)")

Pure sql statements can be written through the cursor cur operation execute () method. Manipulate the data by writing statements such as sql in the execute () method.

> > cur.close ()

Cur.close () closes the cursor

> > conn.commit ()

The conn.commit () method must have this method when submitting things and inserting a piece of data into the database, otherwise the data will not be actually inserted.

> > conn.close ()

Conn.close () closes the database connection

Sixth, insert data

It is not convenient to insert data by writing pure sql statements in the above execute () method. Such as:

> cur.execute ("insert into student values ('2 year 2 class','9')")

If I want to insert new data, I have to change 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 () # insert a piece of data sqli= "insert into student values (% sdaga% s% s)" cur.execute (sqli, (' 3Fengzhong Huhuangjia 2 year 1 class') '7') cur.close () conn.commit () conn.close ()

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

# coding=utf-8import MySQLdb

Conn= MySQLdb.connect (host='localhost', port = 3306, user='root', passwd='123456', db = 'test',) cur = conn.cursor () # insert more than one record at a time sqli= "insert into student values (% SMagade% s,% SMagade% s)" cur.executemany (sqli, [((' 3) year 1 class','6'), (('3) '2 year 1 class','7'), (2 year 2 class','7'),]) cur.close () conn.commit ()

Conn.close ()

The executemany () method can insert more than one value at a time and execute a single sql statement, but repeat the parameters in the parameter list and return the number of affected rows.

Seventh, query data

Maybe you've tried to pass through python.

> cur.execute ("select * from student")

To query the data in the data table, but it did not print out the data in the table, which is a little disappointed.

Let's see what this statement gets.

> aa=cur.execute ("select * from student")

> print aa

five

All it gets is how many pieces of data there are in our table. So how do you get the data in the table? Enter python shell

> import MySQLdb > conn = MySQLdb.connect (host='localhost',port = 3306, 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

The fetchone () method can help us get the data in the table, but the data is different each time I execute cur.fetchone (). In other words, if I don't execute it once, the cursor moves from the first piece of data in the table to the location of the next piece of data, so I get the second piece of data when I execute it again.

The scroll method locates the cursor to the first piece of data in the table.

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

# coding=utf-8import MySQLdbconn= MySQLdb.connect (host='localhost', port = 3306, user='root', passwd='123456', db = 'test',) cur = conn.cursor () # get how many pieces of data are in the table aa=cur.execute ("select * from student") how much data is in the print aa# print table info = cur.fetchmany (aa) for ii in info: print iicur.close () conn.commit () conn.close ()

Through the previous print aa, we know that there are five pieces of data in the current table. The fetchmany () method can get multiple pieces of data, but you need to specify the number of pieces of data, and you can print out multiple pieces of data through a for loop! The implementation results 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 year 1 class','7') (3L, 'Yaheng',' 2 year 2 class','7') [Finished in 0.1s]

After reading this article on Python operation using MySQL database method, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.