登录mysql-workbench 建表
使用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()查询插入的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'])