python USES the mysql database sample code

1, install mysql

If you are an windows user, the installation of mysql is very simple. You can download the installation file directly and double-click the installation file step by step.

Installation under Linux may be simpler. In addition to downloading the installation package, mysql will be available in the 1-like linux warehouse. We can download and install mysql with 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 requires the MySQL-python driver, which is an essential 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 unzip it directly. Enter the directory MySQL-python-1.2.5:

>>python setup.py install

3, the test

The test is very simple, checking 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 linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb 

If there is no error, the MySQLdb module cannot be found, indicating the installation of OK. It is necessary to review the basic operation of mysql before starting to operate the database with python:

4. Basic operation of mysql

$ mysql -u root -p  (with password) 
 
$ mysql -u root    (no 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;  // The functions and test The database 
Database changed
mysql> show tables;  // To view test The table below the library 
Empty set (0.00 sec)

// create user Table, name  and password  Two fields 
mysql> CREATE TABLE user (name VARCHAR(20),password VARCHAR(20)); Query OK, 0 rows affected (0.27 sec)

// to user Insert several pieces of data into the 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)

// To view user The data table 
mysql> select * from user;
+------+----------+
| name | password |
+------+----------+
| Tom | 1321   |
| Alen | 7875   |
| Jack | 7455   |
+------+----------+
3 rows in set (0.01 sec)

// delete name  Is equal to the Jack The data of 
mysql> delete from user where name = 'Jack';
Query OK, 1 rows affected (0.06 sec)

// Modify the name Is equal to the Alen  the password  for  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) 

5, python operates mysql database base

#coding=utf-8
import MySQLdb

conn= 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 1 The data 
#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")


# Modify the data for the query condition 
#cur.execute("update student set class='3 year 1 class' where name = 'Tom'")

# Deletes the data for the query criteria 
#cur.execute("delete from student where age='9'")

cur.close()
conn.commit()
conn.close() 


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

The Connect() method is used to create a connection to the database, where you can specify parameters: username, 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 database connection cursor() method under conn.

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

You can write pure sql statements by manipulating execute() with the cursor cur. 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 inserting a piece of data into the database, otherwise the data will not be actually inserted.

>>yum install mysql

0

Conn.close () close the database connection

6. Insert data

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

>>yum install mysql

1

If I want to insert new data, I have to change the values in this statement. We can make the following modifications:

>>yum install mysql

2

What if you want to insert multiple values into a table at once?

#coding=utf-8
import MySQLdb

conn= MySQLdb.connect(
    host='localhost',
    port = 3306,
    user='root',
    passwd='123456',
    db ='test',
    )
cur = conn.cursor()

#1 Multiple records are inserted at a time 
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 once, execute the one-on-one hit sql statement, but repeat the arguments in the argument list, returning the number of affected rows.

7. Query data

Maybe you've already tried python

>>yum install mysql

4

To query the data in the data table, but it does not print out the data in the table, somewhat disappointingly.

So let's see what does this statement get us

>>yum install mysql

5

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

>>yum install mysql

6

The fetchone() method can help us get the data in the table, but each time we execute cur.fetchone (), we get different data. In other words, I didn't execute once, so the cursor will move from the first data in the table to the next data.

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

Still not working out what we want, how do we get multiple pieces of data from a table and print them out?

>>yum install mysql

7

We know from print aa that there are 5 pieces of data in the current table. fetchmany() method can get multiple pieces of data, but it needs to specify the number of pieces of data. You can print out multiple pieces of data through a loop of for. 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]