Example code of how python uses mysql database

This article mainly introduces the pythonusing mysql database example code. The editor thinks it is quite good, so I will share it with you now and give it as a reference. Let’s follow the editor to take a look.

1. Installationmysql

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 proceed step by step.

Linux The installation may be simpler. In addition to downloading the installation package for installation, there will be mysql in the general Linux warehouse. We only need to download it with one command. Installation:

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, you need MySQL-pythondriver, which is an essential module for python to operate mysql.

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

>>python setup.py install

Third, 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 linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb

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

Four, basic operations of mysql

$ mysql -u root -p （有密码时）
 
$ mysql -u root   （无密码时）

mysql> show databases; // 查看当前所有的数据库
+--------------------+
| Database      |
+--------------------+
| information_schema |
| csvt        |
| csvt04       |
| mysql       |
| performance_schema |
| test        |
+--------------------+
6 rows in set (0.18 sec)

mysql> use test;  //作用与test数据库
Database changed
mysql> show tables;  //查看test库下面的表
Empty set (0.00 sec)

//创建user表，name 和password 两个字段
mysql> CREATE TABLE user (name VARCHAR(20),password VARCHAR(20)); Query OK, 0 rows affected (0.27 sec)

//向user表内插入若干条数据
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)

//查看user表的数据
mysql> select * from user;
+------+----------+
| name | password |
+------+----------+
| Tom | 1321   |
| Alen | 7875   |
| Jack | 7455   |
+------+----------+
3 rows in set (0.01 sec)

//删除name 等于Jack的数据
mysql> delete from user where name = 'Jack';
Query OK, 1 rows affected (0.06 sec)

//修改name等于Alen 的password 为 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

//查看表内容
mysql> select * from user;
+--------+----------+
| name  | password |
+--------+----------+
| Tom  | 1321   |
| Alen  | 1111   |
+--------+----------+
3 rows in set (0.00 sec)

Fifth, basic operation of mysql database in python

#coding=utf-8
import MySQLdb

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

#创建数据表
#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")

#插入一条数据
#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")


#修改查询条件的数据
#cur.execute("update student set class='3 year 1 class' where name = 'Tom'")

#删除查询条件的数据
#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',)

Connect() method is used tocreate database connection, you can specify parameters: user name, password, host and other information.

This is just connected to the database. To operate the database, you need to create a cursor.

>>> cur = conn.cursor()

Create a cursor through the cursor() method under the obtained database connection conn.

Copy code The code is as follows:

>>> 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. Operate data by writing sql statements in the execute() method.

>>>cur.close()

cur.close() Closes the cursor

>>>conn.commit()

conn.commit() method is submitting things. This method must be used when inserting a piece of data into the database, otherwise the data will not be truly of insertion.

>>>conn.close()

Conn.close() closes the database connection

Six, Insert data

Through the above execute( ) method to write pure SQL statements to insert data is not convenient. For example:

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

If I want to insert new data, I must modify the value in this statement. We can make the following modifications:

#coding=utf-8
import MySQLdb

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

#插入一条数据
sqli="insert into student values(%s,%s,%s,%s)"
cur.execute(sqli,('3','Huhu','2 year 1 class','7'))

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

What if we want to insert multiple values ​​into the data table at one time?

#coding=utf-8
import MySQLdb

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

#一次插入多条记录
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 ​​at one time and execute a single SQL statement, but the parameters in the parameter list are repeatedly executed, and the return value is the number of affected rows.

Seven, query data

Maybe you have tried to query the data table through

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

in python data, but it did not print out the data in the table, which was a bit disappointing.

Let’s see what this statement gets

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

5

What it gets is just how many pieces of data there are in our table. So how can we get the data in the table? Entering the 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')

fetchone() method can help us obtain the data in the table, but the data obtained is different every time cur.fetchone() is executed. In other words, I did not execute it once, and the cursor will start from The first piece of data in the table is moved to the position of the next piece of data, so when I execute it again, I get the second piece of data.

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

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

#coding=utf-8
import MySQLdb

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

#获得表中有多少条数据
aa=cur.execute("select * from student")
print aa

#打印表中的多少数据
info = cur.fetchmany(aa)
for ii in info:
  print ii
cur.close()
conn.commit()
conn.close()

Through the previous print aa, we know that there are 5 pieces of data in the current table. The fetchmany() method can obtain multiple pieces of data, but the number of pieces of data needs to be specified through a for loopYou can print out multiple pieces of data! The execution 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]

The above is the detailed content of Example code of how python uses mysql database. For more information, please follow other related articles on the PHP Chinese website!