pymysql操作（插入json）

MySql

登录mysql-workbench
建表

pymsql

使用pyrhon进行插入数据

import time

from socket import *
import subprocess
import json
import pymysql


def insertsql(DATA):



    # 建立数据库连接
    connection_sql = pymysql.connect(
        host='X.X.X.X',
        port=3306,
        user='root',
        password='XXXX',
        # db='qzcsbj',
        # charset='utf8'
    )

    # # 获取游标
    cursor = connection_sql.cursor()
    #
  
    sql_using='use DD'
    cursor.execute(sql_using)

    # sql = "INSERT INTO data(ID, Time, bbox, confidence, label,shape) VALUES ('%s', '%s',  %s,  '%s',  %s,%s)" % (DATA['ID'] , DATA['Time'] ,DATA['bbox'] ,DATA['confidence'] ,DATA['label'] ,DATA['shape'] )
    # sql = "INSERT INTO data(ID, Time, bbox, confidence, label,shape) VALUES ('%s', '%s',  %s,  '%s',  %s,%s)" % (100 , 100 ,100 ,100 ,100 ,(100) )
    print('DATADATA',DATA,type(DATA))

    id=json.loads(DATA)['ID']
    print('idid',id)
    sql = "INSERT INTO data(ID, info) VALUES ('%s', '%s' )" % (id , DATA )

    try:
        cursor.execute(sql)

        connection_sql.commit()
    except:

        connection_sql.rollback()


    # rows = cursor.execute(sql)  # 返回结果是受影响的行数
    #
    # # 关闭游标
    cursor.close()
    #
    # # 关闭连接
    connection_sql.close()
    #
    # # 判断是否连接成功
    # if rows >= 0:
    #     print('连接数据库成功')
    # else:
    #     print('连接数据库失败')

插入单条数据

 
#!/usr/bin/python3
 
import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 插入语句  里面的数据类型要对应
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭数据库连接
db.close()

插入多条数据

#!/usr/bin/env python
# -*-encoding:utf-8-*-
 
import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","root","123","testdb")
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s,%s,%s,%s,%s)"
# 区别与单条插入数据，VALUES ('%s', '%s',  %s,  '%s', %s) 里面不用引号
 
val = (('li', 'si', 16, 'F', 1000),
       ('Bruse', 'Jerry', 30, 'F', 3000),
       ('Lee', 'Tomcat', 40, 'M', 4000),
       ('zhang', 'san', 18, 'M', 1500))
try:
   # 执行sql语句
   cursor.executemany(sql,val)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()
 
# 关闭数据库连接
db.close()

MySql

查询插入的json数据

import pymysql

    # 建立数据库连接
    connection_sql = pymysql.connect(
        host='X.X.X.X',
        port=3306,
        user='root',
        password='XXXX',
        # db='qzcsbj',
        # charset='utf8'
    )

    # # 获取游标
    cursor = connection_sql.cursor()
 
    sql_using = 'use DD'
    cursor.execute(sql_using)

   
    sql = "select *from data"
    cursor.execute(sql)
    datas=cursor.fetchall()

    for e in datas:
        json_sting=json.loads(e[1])
        print(json_sting['Time'])
        print(json_sting['bbox'])
        print(json_sting['label'])
        print(json_sting['shape'])