mysql tode_【20201007】Python操作MySQL数据库

介绍

介绍

今天我们学习如何通过Python操作MySQL数据库。

在Python2.7下使用的是pymysql这个软件包，跟着福哥来操作吧。

安装

安装pymysql

直接使用pip安装即可

pip insall pymysql

授权

MySQL数据库服务器默认只能在安装了数据库服务器的本机操作，也就是在TFLinux上操作，但是我们的python是安装在TFWindows上的啊！所以，我们要授权任何电脑都可以操作TFLinux上的MySQL数据库。当然了，肯定是需要正确的用户名和密码的啦！

在TFLinux上登录mysql

[root@tfdev ~]# mysql -uroot -pabcdef

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

授权tfse用户可以在任意电脑上连接MySQL数据库服务器，且对数据库tfse有完全控制权限

mysql> grant all on tfse.* to tfse@'%' identified by 'abcdef';

Query OK, 0 rows affected, 1 warning (0.02 sec)

测试

建立MySQLDemo.py用来进行pymysql的测试

测试连接

写入如下代码，测试mysql的连接

import pymysql

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

如果没有报错，证明连接成功了！

测试写入数据

写入如下代码，向websites表写入一条数据

import pymysql

try:

# connection

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

cursor = cn.cursor()

# select database

cn.select_db('tfse')

except Exception as e:

print e

exit()

try:

# insert data into websites

cursor.execute("INSERT INTO websites (domainName, weight, createDT) VALUES (%s,%s,now())", ['tongfu.net', 1])

# commit

cn.commit()

except Exception as e:

print e

# rollback

cn.rollback()

finally:

# close

cursor.close()

cn.close()

如果没有报错，就表示写入成功了！

查看数据

到TFLinux上查看刚刚写入的数据

mysql> select * from websites;

+-----------+------------+-------+----------+--------+---------------------+-------------+-------------+

| websiteId | domainName | title | descript | weight | createDT | lastFetchDT | nextFetchDT |

+-----------+------------+-------+----------+--------+---------------------+-------------+-------------+

| 3 | tongfu.net | NULL | NULL | 1 | 2020-10-08 12:47:12 | NULL | NULL |

+-----------+------------+-------+----------+--------+---------------------+-------------+-------------+

1 row in set (0.00 sec)

测试查询数据

写入如下代码，查询websites里的数据

import pymysql

try:

# connection

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

cursor = cn.cursor()

# select database

cn.select_db('tfse')

except Exception as e:

print e

exit()

try:

# query

cursor.execute("SELECT * FROM websites")

print ("总记录数量：" + str(cursor.rowcount))

rows = cursor.fetchall()

print ("记录信息")

for row in rows:

print row

# commit

cn.commit()

except Exception as e:

print e

# rollback

cn.rollback()

finally:

# close

cursor.close()

cn.close()

教程

接下来我们系统学习一下pymysql这个软件包的功能

写入操作

写入操作包括很多，常见的命令包括：CREATE DATABASE/DROP DATABASE、CREATE TABLE/DROP TABLE/ALTER TABLE、INSERT INTO/UPDATE/DELETE等等。

写入操作都需要通过pymysql的cursor.execute方法来运行，如果运行出现异常会抛出except，我们捕获它进行后面的处理即可。

这里我们着重讲讲插入数据，更新数据，删除数据的操作

插入单行数据

插入一行数据，强烈建议使用指定字段名称的方式写入数据

import pymysql

try:

# connection

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

cursor = cn.cursor()

# select database

cn.select_db('tfse')

except Exception as e:

print e

exit()

try:

# insert data into websites

cursor.execute("INSERT INTO websites (domainName, weight, createDT) VALUES (%s,%s,now())", ['www.tongfu.net', 1])

# websiteId of insert data

websiteId = cursor.lastrowid

print ("插入数据的websiteId是：" + str(websiteId))

# commit

cn.commit()

except Exception as e:

print e

# rollback

cn.rollback()

finally:

# close

cursor.close()

cn.close()

插入多行数据

可以传入数组到cursor.executemany实现多行数据插入的操作

import pymysql

try:

# connection

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

cursor = cn.cursor()

# select database

cn.select_db('tfse')

except Exception as e:

print e

exit()

try:

# insert data into websites

rows = [

('www.baidu.com',1),

('www.taobao.com',1),

('www.jd.com',1)

]

cursor.executemany("INSERT INTO websites (domainName, weight, createDT) VALUES (%s,%s,now())", rows)

# commit

cn.commit()

except Exception as e:

print e

# rollback

cn.rollback()

finally:

# close

cursor.close()

cn.close()

更新数据

使用UPDATE语句进行数据的修改操作

import pymysql

try:

# connection

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

cursor = cn.cursor()

# select database

cn.select_db('tfse')

except Exception as e:

print e

exit()

try:

# insert data into websites

cursor.execute("UPDATE websites SET weight = 10 WHERE domainName = %s", 'tongfu.net')

# commit

cn.commit()

except Exception as e:

print e

# rollback

cn.rollback()

finally:

# close

cursor.close()

cn.close()

删除数据

使用DELETE语句删除数据

import pymysql

try:

# connection

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

cursor = cn.cursor()

# select database

cn.select_db('tfse')

except Exception as e:

print e

exit()

try:

# insert data into websites

cursor.execute("DELETE FROM websites WHERE domainName = %s", 'www.baidu.com')

# commit

cn.commit()

except Exception as e:

print e

# rollback

cn.rollback()

finally:

# close

cursor.close()

cn.close()

读取操作

读取操作只的就是SELECT语句了，这个SELECT语句可以有非常复杂的变化，设计到多表联查的时候会更加复杂，有时候一个SQL语句几千个字符都很正常。

读取一行数据

通过cursor.fetchone读取一行数据

import pymysql

try:

# connection

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

cursor = cn.cursor()

# select database

cn.select_db('tfse')

except Exception as e:

print e

exit()

try:

# insert data into websites

cursor.execute("SELECT * FROM websites WHERE domainName = %s", 'tongfu.net')

# fetch one

result = cursor.fetchone()

print result

# commit

cn.commit()

except Exception as e:

print e

# rollback

cn.rollback()

finally:

# close

cursor.close()

cn.close()

读取全部数据

通过cursor.fetchall读取查询结果集的全部数据

import pymysql

try:

# connection

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

cursor = cn.cursor()

# select database

cn.select_db('tfse')

except Exception as e:

print e

exit()

try:

# insert data into websites

cursor.execute("SELECT * FROM websites")

# fetch all

results = cursor.fetchall()

for result in results:

print result

# commit

cn.commit()

except Exception as e:

print e

# rollback

cn.rollback()

finally:

# close

cursor.close()

cn.close()

数据分页

所谓数据翻页就是实现指定读取数据的起始位置和读取数据的条目数的技巧，通过这个技巧可以将一组数据均匀分成若干份，每一份相当于一页，这样的设计可以让用户像浏览图书一样的翻页效果

import pymysql

import math

try:

# connection

cn = pymysql.connect("192.168.1.168","tfse","abcdef")

cursor = cn.cursor()

# select database

cn.select_db('tfse')

except Exception as e:

print e

exit()

try:

# insert data into websites

cursor.execute("SELECT * FROM websites")

# pages

cursor.scroll(0, mode="absolute") # move to 0

try:

# count page numbers

totalNum = cursor.rowcount

pageNum = 1

pageCount = math.ceil(float(totalNum)/3)

while 1:

# fetch 2 rows

results = cursor.fetchmany(2)

print ("页码：" + str(pageNum))

for result in results:

print result

# page number +1

pageNum = pageNum+1

# page number more than page count then break

if pageNum > pageCount:

break

except Exception as e:

print e

# commit

cn.commit()

except Exception as e:

print e

# rollback

cn.rollback()

finally:

# close

cursor.close()

cn.close()

总结

今天我们学习了通过pymysql操作MySQL数据库的方法，大家课后可以自己多多练习，一定要数量掌握这个技巧才可以哦~~【20201007】Python操作MySQL数据库 第 1 页​m.tongfu.net