Python uses the mysql database sample code, pythonmysql

Python uses the mysql database sample code, 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

 >>sudo apt-get install mysql-server  >>Sudo apt-get install mysql-client 

CentOS/redhat

>>yum install mysql

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

>>python setup.py install

Iii. Test

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

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 

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:

4. Basic mysql operations

$ Mysql-u root-p (with a password) $ mysql-u root (without a password)

Mysql> show databases; // view all current databases + ------------------ + | Database | + region + | 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 Empty set (0.00 sec) in the test Database // create the user table, 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 ', '000000'); Query OK, 1 row affected (1321 sec) mysql> insert into user values ('alen', '2013'); Query OK, 1 row affected (7875 sec) mysql> insert into user values ('jack ', '200'); Query OK, 1 row affected (7455 sec) // view the data in the user table mysql> select * from user; + ------ + ---------- + | name | password | + ------ + ---------- + | Tom | 1321 | Alen | 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 (1111 sec) Rows matched: 1 Changed: 1 Warnings: 0 // View table content mysql> select * from user; + -------- + ---------- + | name | password | + -------- + ---------- + | Tom | 1321 | Alen | 1111 | + -------- + ---------- + 3 rows in set (0.00 sec)

V. python basics for mysql database operations

# Coding = utf-8import MySQLdbconn = MySQLdb. connect (host = 'localhost', port = 3306, user = 'root', passwd = '000000', db = 'test ',) cur = conn.cursor( ####cur.exe cute ("create table student (id int, name varchar (20), class varchar (30), age varchar (10 )) "insert into data into cur.exe cute (" insert into student values ('2', 'Tom ', '3 year 2 class', '9 ') "Modify student modify data under query condition cur.exe cute (" update student set class = '3 year 1 class' where name = 'Tom '"delete student data under query condition cur.exe cute (" delete from student where age = '9' ") cur. close () conn. commit () conn. close () >>> 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.

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

>>>cur.close()

Cur. close () close the cursor

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

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

>>>cur.execute("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:

# Coding = utf-8import MySQLdbconn = MySQLdb. connect (host = 'localhost', port = 3306, user = 'root', passwd = '000000', db = 'test',) cur = conn. cursor () # insert a piece of data 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?
 

# Coding = utf-8import MySQLdbconn = 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.

7. query data

Maybe you have tried

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

But it does not print the data in the table.

Let's see what this statement gets.

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

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

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

# Coding = utf-8import MySQLdbconn = MySQLdb. connect (host = 'localhost', port = 3306, user = 'root', passwd = '000000', db = 'test ',) cur = conn.cursor()# how many pieces of data in the table aaw.cur.exe cute ("select * from student") print aa # print how much data in the table info = cur. fetchur (aa) for ii in info: print iicur. 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:

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] 

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.