用python 操作mysql的基本操作

1插入（单条）数据：insert into    （改写法，在遇到主键重复的情况下，代码会报错）

data = {'name':'GREAT','from':'hkg','id':'082001','age':'99','shengao':'188','tianjia':'666'}
    try:
        conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset="utf8")
        cursor = conn.cursor()
        insert_sql = """insert into test_biao(name,`from`,id,age,shengao,tianjia)  values(%s, %s, %s,%s, %s, %s)"""
        aa = cursor.execute(insert_sql,(data['name'],data['id'],data['age'],data['shengao'],data['tianjia']))
        conn.commit()  #进行insert，update，delete的时候是需要commit的，
        cursor.close()
        conn.close()
    except Exception as e:
        print('wrong' , e)

1-2  批量插入数据：executemany  参数是列表：[(),()]

data = {'name': 'GREAT', 'from': 'hkg', 'id': '082001', 'age': '1010', 'shengao': '188', 'tianjia': '666'}
    try:
        conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset="utf8")
        cursor = conn.cursor()
        insert_sql = """insert   into test_biao(name,age)  values(%s, %s)"""
        all_data_list = []
        for i in range(200, 210):
            all_data_list.append((data['name'], i))
  
        aa = cursor.executemany(insert_sql, all_data_list)
        conn.commit()

        cursor.close()
        conn.close()
    except Exception as e:
        print('wrong', e)

 

2 查找、获取数据：select

data = {'name':'GREAT','from':'hkg','id':'082001','age':'99','shengao':'188','tianjia':'666'}
    try:
        conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset="utf8")
        cursor = conn.cursor()
        insert_sql = """select age from test_biao where name='hhha'"""
        cursor.execute(insert_sql)
        result = cursor.fetchall()
        print('reslut',result)
        print('reslut',type(result))
        print(result[0],result[0][0])
        cursor.close()
        conn.close()
    except Exception as e:
        print('wrong' , e)

3更新数据：update (存在就更新（所有符合条件的都更新），若不存在，就不操作）

    try:
        conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset="utf8")
        cursor = conn.cursor()
        insert_sql = """update test_biao set age=110  where name='hhhh'"""
        aa = cursor.execute(insert_sql)
        conn.commit()

        cursor.close()
        conn.close()
    except Exception as e:
        print('wrong' , e)

4更新数据：insert ignore into    前提是设置了主键，否则也是无脑插入： 存在则不操作，若不存在，则插入，若遇到主键重复的数据，该写法不会报错，只会提醒warning

data = {'name': 'GREAT', 'from': 'hkg', 'id': '082001', 'age': '1010', 'shengao': '188', 'tianjia': '666'}
    try:
        conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset="utf8")
        cursor = conn.cursor()
        insert_sql = """insert  ignore into test_biao(name,age)  values(%s, %s)"""
        try:
            aa = cursor.execute(insert_sql, (data['name'], data['age']))
            conn.commit()
        except Exception as e:
            print('''dddddddddd''', e)
        cursor.close()
        conn.close()
    except Exception as e:
        print('wrong', e)

 

 

5删除某条数据：delete

    try:
        conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test', charset="utf8")
        cursor = conn.cursor()
        insert_sql = """delete from test_biao  where name='jianxiong'"""
        cursor.execute(insert_sql)
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        print('wrong' , e)

5查询某个时间的数据大于现在的写法：

SELECT * FROM 表名 WHERE TO_DAYS(字段名) >= TO_DAYS(NOW())