PYTHON MySql数据库的插入、更新与查询

最新推荐文章于 2024-09-10 23:12:24 发布

磊，，北

最新推荐文章于 2024-09-10 23:12:24 发布

阅读量821

点赞数

分类专栏：爬虫文章标签： mysql python

本文链接：https://blog.csdn.net/qq_43561314/article/details/97617156

版权

爬虫专栏收录该内容

4 篇文章 1 订阅

订阅专栏

一、动态插入

1.传入字典

2.构造sql语句

3.插入

import pymysql

db = pymysql.connect(host='localhost',user='root',password='',port=3306,db='scrapy_db')
cursor = db. cursor()   #数据库连接
data = {
    'num':'22',
    's_name':'tang2'
}
table ='scrapy' 
keys =','.join(data. keys()) 
values = ','.join(['%s'] * len(data)) 
sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ' .format(table=table, keys=keys, values=values) 
print(sql) # INSERT INTO scrapy(num,s_name) VALUES (%s,%s)
try:
    if cursor. execute(sql, tuple(data. values())): 
        print('Successful')
        db.commit()
except: 
    print('Fail')
    db.rollback()
db.close()

二、动态更新

在插入语句后添加代码ON DUPLICATE KEY UPDATE。如果主键已经存在，就执行更新操作。

import pymysql

db = pymysql.connect(host='localhost',user='root',password='',port=3306,db='scrapy_db')
cursor = db. cursor() 
data = {
    'num':'22',
    's_name':'tang3'
}
table ='scrapy' 
keys =','.join(data. keys()) 
values = ','.join(['%s'] * len(data)) 
sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE ' .format(table=table, keys=keys, values=values) 
print(sql) 
#INSERT INTO scrapy(num,s_name) VALUES (%s,%s) ON DUPLICATE KEY UPDATE num = %s,s_name = %s
update = ','.join(['{key} = %s'.format(key=key) for key in data])
sql +=update
print(sql)
try:
    if cursor. execute(sql, tuple(data.values())*2): 
        print('Successful')
        db.commit()
except: 
    print('Fail')
    db.rollback()
db.close()

三、查询

import pymysql

db = pymysql.connect(host='localhost',user='root',password='t15086976922',port=3306,db='scrapy_db')
cursor = db. cursor() 
sql = 'SELECT * FROM scrapy WHERE num > 20'
try: 
    cursor.execute(sql) 
    print('Count:', cursor.rowcount) 
    one = cursor. fetchone () 
    print('one', one)
    results = cursor. fetchall() 
    print('Results:', results) 
    print('Results Type :', type(results)) 
    #1.
    for row in results: 
        print(row) 
    #2.(推荐)
    row = cursor. fetchone()
    while row:
        print('Row', row) 
        row = cursor. fetchone() 
except: 
    print('Error')