python对mysql数据库进行读写的操作

python对mysql数据库进行读写的操作

import pymysql
 
# 使用python连接mysql数据库，并对数据库进行添加数据的方法
# 创建连接，数据库主机地址 数据库用户名称 密码 数据库名 数据库端口 数据库字符集编码
conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password='123456',
                       database='empdb',
                       port=3307,
                       charset='utf8')
print("连接成功")
 
# 创建游标
cursor = conn.cursor()
 
# 添加一条数据数据
def insertdata1():
    insert_emp_sql = "insert into empdb.employee (ename,age,dept_id,wage_id) values ('王十八',45,1,3)"
    # 执行语句
    cursor.execute(insert_emp_sql)
    # 提交数据
    conn.commit()
 
# 批量添加数据
def insertdata2():
    insert_emp_sql = "insert into empdb.employee (ename,age,dept_id,wage_id) values ('{}',{},1,3);"
 
    # 插入10条数据0-9
    for i in range(10):
        uname = '高少少'+str(i)
        age=30+i
 
        ins_sql= insert_emp_sql.format(uname,age)
        cursor.execute(ins_sql)
        conn.commit()
 
# 删除数据
def deletedata(id):
    delete_emp_sql = "delete from empdb.employee where eid={}"
    del_sql = delete_emp_sql.format(id)
    cursor.execute(del_sql)
    conn.commit()
 
 
# 更改数据
def updatadata():
    updata_emp_sql = "update empdb.employee set age=66 where eid = 26"
    cursor.execute(updata_emp_sql)
    conn.commit()
 
# 关闭游标跟连接
def closeconn():
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()
 
try:
    # insertdata1()
    # insertdata2()
    deletedata(1)
    updatadata()
except:
    conn.rollback()
 
closeconn()

对mysql数据库进行读写的操作

import pymysql
import datetime


def get_nowtoday():
    get_nowtoday = datetime.datetime.now()
    return get_nowtoday


table = 'capture_dianping_uu_item_req_log'
request_time1 = get_nowtoday()

field_array = ['uuid', 'filterbtnid', 'filterbtntext', 'response_json', 'request_time']

SQL = f"INSERT INTO {table}({','.join(field_array)}) VALUES ({','.join(['%s'] * len(field_array))})"


uuid = 'H2q0PyY0LqgsYfA7'
filterbtnid = 'H2q0PyY0LqgsYfA7'
filtertext= 'H2q0PyY0LqgsYfA7'
data = '青年男性青年男性体检今约明检|B幽门螺杆菌 肺部CT'
db = pymysql.connect(
    host="rm-bp118963s12pqo5l7fo.mysql.rds.aliyuncs.com",
    port=3306,
    user="captraea",
    passwd="HQ@2e#WW",
    db="captraea",
    charset='utf8'
)

values = []
#使用cursor方法创建一个游标
cursor = db.cursor()
if cursor:
    try:
        print("连接成功")
        cursor.execute("show tables;")
        table_number = cursor.fetchall()
        print(table_number)
        val = [uuid, filterbtnid, filtertext, data, request_time1]
        print("val=",val)
        values.append(tuple(val))
        cursor.executemany(SQL, values)
        db.commit()
        print("数据插入成功")
    except Exception as e:
        print("异常",e)

else:
    print("连接失败")



# 数据库重新连接
db = pymysql.connect(
    host="rm-bp118963s12pqo5l7fo.mysql.rds.aliyuncs.com",
    port=3306,
    user="captraea",
    passwd="HQ@2e#WW",
    db="captraea",
    charset='utf8'
)

values = []
#使用cursor方法创建一个游标
cursor = db.cursor()
#查询数据库并打印内容
cursor.execute('''SELECT * FROM capture_dianping_uu_item_req_log WHERE gmt_create > '2023-02-28';''')
results = cursor.fetchall()
print("results==",results)
for row in results:
    print("ROW==",row)
#关闭
cursor.close()
db.commit()
db.close()

python如何将数据插入数据库

#导入需要使用到的数据模块
import pandas as pd
import pymysql

#读入数据
filepath = 'E:\_DataSet\catering_sale.xls'
data = pd.read_excel(filepath)

#建立数据库连接
db = pymysql.connect('localhost','root','1234','python_analysis')
#获取游标对象
cursor = db.cursor()
#创建数据库，如果数据库已经存在，注意主键不要重复，否则出错
try:
    cursor.execute('create table catering_sale(num int primary key,date datetime, sale float )')
except:
    print('数据库已存在！')

#插入数据语句
query = """insert into catering_sale (num, date, sale) values (%s,%s,%s)"""

#迭代读取每行数据
#values中元素有个类型的强制转换，否则会出错的
#应该会有其他更合适的方式，可以进一步了解
for r in range(0, len(data)):
    num = data.ix[r,0]
    date = data.ix[r,1]
    sale = data.ix[r,2]
    values = (int(num), str(date), float(sale))
    cursor.execute(query, values)

#关闭游标，提交，关闭数据库连接
#如果没有这些关闭操作，执行后在数据库中查看不到数据
cursor.close()
db.commit()
db.close()

#重新建立数据库连接
db = pymysql.connect('localhost','root','1234','python_anylysis')
cursor = db.cursor()
#查询数据库并打印内容
cursor.execute('''select * from catering_sale''')
results = cursor.fetchall()
for row in results:
    print(row)
#关闭
cursor.close()
db.commit()
db.close()