运行环境:Ubuntu 18.04
以下所有命令均在root用户下执行。
一、安装mysql和PyMySQL
(1)安装mysql。
apt install mysql-server -y
apt install mysql-client -y
apt install libmysqlclient-dev -y
(2)修改默认密码。
cat /etc/mysql/debian.cnf,显示如下:
图有‘user=debian-sys-maint’,即为自动配置的默认用户,
‘password=zNBBBmhoGQuTxEzE’,即为自动配置的密码。
mysql -u debian-sys-maint -p # 之后再输入自动配置的密码
use mysql;
# 密码改成123
update mysql.user set authentication_string=password('123') where user='root' and Host ='localhost';
update user set plugin="mysql_native_password";
flush privileges;
quit;
(3)创建数据库BIGDATA。
service mysql restart # 重启mysql
mysql -u root -p # 然后输入密码123进入mysql
create database BIGDATA; # 创建数据库BIGDATA
show databases; # 查看所有数据库
quit # 退出mysql
(4)安装python3-pip和PyMySQL。
apt install python3-pip -y
pip3 install PyMySQL
二、编写python代码,操作MySQL
(1)连接数据库并打印SQL版本号。
vim version.py,内容如下:
#!/usr/bin/python3
# -*- coding: UTF-8 -*-
# 功能:打印SQL版本号
import pymysql as pysql
# 打开数据库连接
db = pysql.connect("localhost", "root", "123", "BIGDATA") # ip、用户、密码、数据库名称
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据
data = cursor.fetchone()
print ("Database vesion: %s " % data)
db.close()
(2)创建EMPLOYEE表。
vim create.py,内容如下:
#!/usr/bin/python3
# -*- coding: UTF-8 -*-
# 功能:创建EMPLOYEE表
import pymysql as pysql
db = pysql.connect("localhost", "root", "123", "BIGDATA")
cursor = db.cursor()
sql = """CREATE TABLE EMPLOYEE (
NAME CHAR(20) NOT NULL,
AGE INT,
GENDER CHAR(1),
INCOME FLOAT )"""
try:
cursor.execute(sql)
db.commit()
print("Create table EMPLOYEE successfully!")
except Exception as e:
print(e)
db.rollback()
db.close()
(3)向EMPLOYEE表中批量插入n行随机数构成的数据。
vim insert.py,内容如下:
#!/usr/bin/python3
# -*- UTF-8 -*-
# 功能:插入n行随机数构成的数据
import pymysql as pysql
from random import randint
from random import choice
db = pysql.connect("localhost", "root", "123", "BIGDATA")
cursor = db.cursor()
n = 10
for i in range(n):
# Name: 3-8, Abc
# ascii : int -> char
## random, chr
l = randint(3, 8) # get a random integer from [3,8]
name = ""
name += chr(randint(65, 90)) # Lowercase letters
for j in range(l - 1):
name += chr(randint(97, 122)) # Uppercase letters
# Age: 18-55
age = randint(18, 55)
# Gender : "M", "F"
genders = ["M", "F"]
gender = choice(genders)
# Income: 1000-10000
income = randint(1000,10000)
sql = "INSERT INTO EMPLOYEE (NAME, AGE, GENDER, INCOME) VALUES ('%s', '%s', '%s', '%s')" \
% (name, age, gender, income)
try:
cursor.execute(sql)
db.commit()
except Exception as e:
print(e)
db.rollback()
print("Insert %s lines successfully!" % n)
db.close()
(4)查看EMPLOYEE表的数据。
vim show.py,内容如下:
#!/usr/bin/python3
# -*- coding: UTF-8 -*-
# 功能:查看EMPLOYEE表的数据
import pymysql as pysql
db = pysql.connect("localhost", "root", "123", "BIGDATA")
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE"
try:
cursor.execute(sql)
results = cursor.fetchall() # fetchall接收返回结果的全部行
if results: # results不为空
for r in results:
print("NAME:%s\tAGE:%s\tGENDER:%s\tINCOME:%s" % (r[0], r[1], r[2], r[3]))
else:
print("No data!")
except Exception as e:
print(e)
db.rollback()
db.close()
(5)删除EMPLOYEE表中的所有数据。
vim delete.py,内容如下:
#!/usr/bin/python3
# -*- UTF-8 -*-
# 功能:删除EMPLOYEE表中的所有数据
import pymysql as pysql
db = pysql.connect("localhost", "root", "123", "BIGDATA")
cursor = db.cursor()
sql = "DELETE FROM CHINA"
try:
cursor.execute(sql)
db.commit()
print("Delete all data of table EMPLOYEE successfully!")
except Exception as e:
print(e)
db.rollback()
db.close()
(6)按顺序运行之前编写的python程序。
chmod +x *py
ll
./version.py
./create.py
./insert.py
./show.py
./delete.py
./show.py
运行结果如下图:
本文同步分享在 博客"nefu_ljw"(CSDN)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。