Connect Python to the mysql database

First of all, you need to download and install the corresponding modules. You can search for them on the internet and install them easily, after installation, the MySQLdb folder under the site-packages folder under the Lib folder under the python installation directory stores the definition of this module. After the preparation is complete, we need to import MySQLdb in the source code.

Database connection:

After the module is introduced, we need to connect to the database. The instance code is as follows:

Db = MySQLdb. connect ("localhost", "root", "123456", "myciti ")

The meanings of these three key parameters are clear at a glance: the first is the server address, the second is the user name, the third is the dbms password, and the fourth is the database to be accessed, in fact, the parameters of the connect function are not limited to these parameters, but they are omitted because they have default values and do not need to be modified in most cases. The following list is provided:

Host, the host name of the connected database server. The default value is local host ).

User, the user name used to connect to the database. The default value is the current user.

Passwd, connection password, no default value.

Database, the name of the connected database, with no default value.

Conv: maps text to a Python dictionary. The default value is MySQLdb. converters. conversions.

The type used by cursorclass and cursor (). The default value is MySQLdb. cursors. Cursor.

Compress: enables protocol compression.

Named_pipe is connected to a named pipe in windows.

Init_command: Once the connection is established, a statement is specified for the database server to run.

Read_default_file: Use the specified MySQL configuration file.

Read_default_group: the default read group.

Unix_socket: the socket used for connection in unix. TCP is used by default.

Port: Specifies the connection port of the database server. The default value is 3306.

You may notice that the port number is not used in the source code. This is because the default value of this parameter in the connect function of MySQLdb is 3306. If you modify the database port number when installing mysql, then you need to add the modified value of this parameter in the source code.

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:

Ubuntudeepin

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

$ Mysql-u root (when no password is available)

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.

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

# 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.exe cute ("select * from student ")

But it does not print the data in the table.

Let's see what this statement gets.

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

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