python使用pymysql操作mysql数据库

1、安装pymysql

pip install pymysql

2、数据库查询示例

import pymysql

# 连接database

conn =pymysql.connect(user='root', password='' ,host='127.0.0.1',database='xinfang')

#创建游标

cursor = conn.cursor()

#执行函数  返回受影响的函数

effect_rows = cursor.execute('select * from t_base_letters where MemberNum>%s and SubjectTno1=%s',[1,''])

print("受影响的行数",effect_rows)

#提取所有结果

results = cursor.fetchall()

for row in results:

    id = row[0]

    SLID = row[1]

    SubjectType = row[4]

    # 打印结果

    print("id=%s,SLID=%s,SubjectType=%s" % (id, SLID, SubjectType))

#关闭游标

cursor.close()

#关闭连接

conn.close()

3、数据增删改示例

　

import pymysql

# 连接database

conn =pymysql.connect(user='root', password='' ,host='127.0.0.1',database='xinfang')

#创建游标

cursor = conn.cursor()

#执行函数  返回受影响的函数

# --insert---

# effect_rows = cursor.execute('insert into t_base_letters (id,SLID) values (%s,%s)',[1111110,'2020110800033'])

# --update--

# effect_rows = cursor.execute('update t_base_letters set SLID=%s where id=%s',['3020110800033',1111110])

# --delete--

# effect_rows = cursor.execute('delete from t_base_letters where id=%s',[1111111])

print("受影响的行数",effect_rows)

#提交任务

conn.commit()

#关闭游标

cursor.close()

#关闭连接

conn.close()