使用pymysql连接数据库

python字符串格式化

print("I'm %s. I'm %d year old" % ('Vamei', 99))
print("I'm %(name)s. I'm %(age)d year old" % {'name':'Vamei', 'age':99})

pymysql操作数据库

import pymysql
import time 
# python 批量插入数据库
# 打开数据库连接
db = pymysql.connect(host="localhost",
    user="root",
    password="root",
    db="douban_book",
    port=3306,
    charset='utf8')

# 使用cursor()方法获取游标
cur = db.cursor()

# 1、查询操作

sql = "select id,user_name from music_two"
try:
    cur.execute(sql)

    results = cur.fetchall()
    print("id", "user_name")

    # 遍历查询结果
    for row in results:
            id = row[0]
            user_name = row[1]
            print(id,user_name)

except Exception as e:
    raise e

finally: 
    db.close()

# 2、插入操作 循环批量插入

for i in range(10000):
    stamp_time = int(time.time())
    insert_sql = "insert into tableone (uid) values (%d)" %(stamp_time)
    try:
        cur.execute(insert_sql)
        # 提交
        db.commit()
    except Exception as e:
        db.rollback()

db.close()

#  3、更新数据 删除数据
sql_update = "update tableone set uid=%d where id=%d"
sql_delete = "delete from tableone where id=%d"

try:
    cur.execute(sql_update % (1008611, 1))
    cur.execute(sql_delete % (4))
    db.commit()
except Exception as e:
    db.rollback()
finally:
    db.close()

数据库搜索，去重

SELECT DISTINCT uid, count(*) FROM tableone;
SELECT uid,Count(*) FROM tableone GROUP BY uid HAVING Count(*)>1;