本文主要是写了pymysql的用法,自己记录一下
# coding=utf8
import pymysql
import time
import datetime
'''
启动MySQL
cd /usr/local/mysql 进入MySQL目录
sudo /usr/local/mysql/support-files/mysql.server start 启动MySQL
'''
sync_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
stamp = time.time() # 获取当前时间戳
# 使用 cursor() 方法创建一个游标对象 cursor
conn = pymysql.connect(
host="localhost", # 数据库IP
port=3306, # 端口号
user='root', # 数据库账号
passwd='000', # 数据库登录密码
db='db_name',
charset='utf8',
unix_socket="/tmp/mysql.sock" # 路径
)
def create_sql():
cursor = conn.cursor()
# 使用 execute() 方法执行 SQL,如果表存在则删除
# cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
cursor.execute("drop table if exists EMPLOYEE")
# 使用预处理语句创建表 create table
sql = """create table EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT,
sync_date datetime
)"""
cursor.execute(sql)
def add_sql():
# 增加数据库字段
cursor = conn.cursor()
sql = "alter table EMPLOYEE add sync varchar(100);"
cursor.execute(sql)
def insert_into():
# 插入数据库字段
cursor = conn.cursor()
sql = "INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME, sync_date)" \
" VALUES ('{}', '{}', {}, '{}', {} ,'{}')".format('Mac2', 'haode', 20, 'M', 2000 , sync_date)
# 执行sql语句
try:
cursor.execute(sql)
# 提交到数据库执行
conn.commit()
print("数据插入成功")
except:
print ("Error: unable to fetch data")
conn.close()
def select():
# 查询数据库字段
cursor = conn.cursor()
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE "
try:
# 执行SQL语句
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
for i in results:
print(i[5])
print(list(i))
# res = results[i]
# print(res)
# for j in res:
# print(res[5])
except:
print ("Error: unable to fetch data")
# 关闭数据库连接
conn.close()
def delete():
# 删除数据库字段
curses = conn.cursor()
sql = "DELETE FROM EMPLOYEE WHERE FIRST_NAME = 'Mac3'"
try:
curses.execute(sql)
conn.commit()
print("数据删除成功")
except:
print ("Error: unable to fetch data")
conn.close()
def update():
# 修改数据库字段
curses = conn.cursor()
sql = "UPDATE EMPLOYEE SET FIRST_NAME='Mac3' , SEX = '男' WHERE LAST_NAME = 'haode' "
try:
curses.execute(sql)
conn.commit()
print("数据更新成功")
except:
print ("Error: unable to fetch data")
if __name__ == '__main__':
add_sql()
create_sql()
insert_into()
select()
update()
select()
delete()