python操作mysql数据库实例

python操作mysql

代码示例。

import pymysql
import json
import time
from pymysql.converters import escape_string


# 打开数据库连接
conn = pymysql.connect(host = 'localhost',user = "root",passwd = "******",db='test', charset = 'utf8')

# 数据初始化（需要插入的变量）
url = 'https://www.zhihu.com/'
title = '123'
hot_num = '123'
topics = json.dumps([1,2,3])
concern_and_view = json.dumps([1,2,3])
time_snap = str(int(time.time()))

# sql语句（table填对应的表名）
sql_insert = 'INSERT INTO table VALUES (%s, %s, %s, %s, %s, %s)'
# 获取游标
cursor = conn.cursor()
# 转化sql语句
row = cursor.execute(sql_insert,(url,title,hot_num,topics,concern_and_view,time_snap))
# 提交插入sql
conn.commit()

# 简单查询
cursor.execute('select *from table')
all = cursor.fetchall()
print(all)

# 关闭操作
cursor.close()
conn.close()