python中mysql的存储

时间：2023-01-20 00:13:06

1. 连接mysql

import pymysql

db = pymysql.connect(host='localhost', user='root', password='', port=3306)

cursor = db.cursor()

cursor.execute('select version()')

data = cursor.fetchone()

print('database version:', data)

cursor.execute('create database spider default character set utf8')

db.close()

2. 创建表

import pymysql

db = pymysql.connect(host='localhost', user='root', password='', port=3306, db='spider')

cursor = db.cursor()

sql = 'create table if not exists students (id varchar(255) not null, name varchar(255) not null, age int not null, PRIMARY KEY (id))'

cursor.execute(sql)

db.close()

3. 插入数据

mport pymysql

id = ''

user = 'bob'

age = 20

db=pymysql.connect(host='localhost', user='root', password='', port=3306, db='spider')

cursor = db.cursor()

sql = 'insert into students(id,name,age) values(%s, %s, %s)'

try:

    cursor.execute(sql, (id, user, age))

    db.commit()

except:

    db.rollback()

db.close()

4. 更新数据

4.1：普通更新

import pymysql

db=pymysql.connect(host='localhost', user='root', password='', port=3306, db='spider')

cursor= db.cursor()

sql = "update spider.students set age = %s where name = %s"

try:

    cursor.execute(sql, (20, 'bob'))

    db.commit()

except:

    db.rollback()

db.close()

4.2：去重更新

如果主键存在就更新，不存在就新增

 import pymysql

 db=pymysql.connect(host='localhost', user='root', password='', port=3306, db='spider')

 cursor= db.cursor()

 data = {

         'id':'',

         'name': 'jack',

         'age': 21

 }

 table = 'students'

 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)

 update = ','.join([" {key} = %s".format(key=key) for key in data])

 sql += update

 try:

     if cursor.execute(sql, tuple(data.values())*2):

         print('Successful')

         db.commit()

 except:

     print('Failed')

     db.rollback()

 db.close()

分析理解

>>> keys = ', '.join(data.keys())

>>> keys

'age, name, id'

>>> values = ', '.join(['%s'] * len(data))

>>> values

'%s, %s, %s'

>>> table = 'students'

>>> sql = 'insert into {table}({keys}) values ({values}) on duplicate key update'.format(table=table, keys=keys, values=values)

>>> sql

'insert into students(age, name, id) values (%s, %s, %s) on duplicate key update'

>>> update = ','.join([" {key} = %s".format(key=key) for key in data])

>>> update

' age = %s, name = %s, id = %s'

>>> sql+=update

>>> sql

'insert into students(age, name, id) values (%s, %s, %s) on duplicate key update age = %s, name = %s, id = %s'

>>> data.values()

dict_values([, 'jack', ''])

>>> tuple(data.values())

(, 'jack', '')

>>> tuple(data.values())*

(, 'jack', '', , 'jack', '')

5. 删除数据

import pymysql

db=pymysql.connect(host='localhost', user='root', password='', port=3306, db='spider')

cursor= db.cursor()

table = 'students'

condition = 'age > 20'

sql= 'delete from {table} where {condition}'.format(table=table, condition=condition)

try:

    cursor.execute(sql)

    db.commit()

except:

    db.rollback()

db.close()

6. 查询数据

import pymysql

db=pymysql.connect(host='localhost', user='root', password='', port=3306, db='spider')

cursor= db.cursor()

sql = 'select * from students where age >= 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)) # 类型是元组

    for row in results:

        print(row)

except:

    print('Error')

相关文章