所以我继承了一个用PHP编写的"简单"web应用程序，并通过在long_text字段中使用json将大部分事务数据存储在MariaDB的一个表中。
我需要解析出数据，以便将其放入传统的关系数据库表中。最好使用SQL和/或Python。
这是当前的表结构
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| client_name | varchar(100) | YES | | NULL | |
| invoice_total | varchar(45) | YES | | NULL | |
| invoice | longtext | NO | | NULL | |
| user_id | int(11) | YES | MUL | NULL | |
| created | datetime | NO | | NULL | |
| uuid | varchar(70) | NO | | NULL | |
| paid | tinyint(1) | NO | | 0 | |
| paid_date | datetime | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
以下是现有数据的一条记录。
341, 'Dave Moreman', '4616.75', '{"data":{"id":"341","clientCompanyName":"Dave Moreman","clientAddress":"18656 86th Ave, <br />Zwingle,Utah 50032, <br />United States, <br /> 563 555 1212.","job_descr":"Swine","Invoice":{"itemNo":["1","1","1","DF","F"],"itemName":["Pumped out of hog building, hauled 4.5 miles and applied to field (Jakes House)","Pumped out of hog building, hauled 3.5 miles and applied to field (Daughters house)","Pumped out of hog building, hauled 4 miles and applied to field(By your house)","Diesel Fuel","Finance charge"],"price":["0.0155",".0145",".015","1","1"],"quantity":["169000","88000","36002","113.00","68.22"],"total":["2619.50","1276.00","540.03","113.00","68.22"]},"notes":"281,000 total gallons","subTotal":"4616.75","tax":"","taxAmount":"0","totalAftertax":"4616.75","amountPaid":"","amountDue":"4616.75","companyAddress":""},"invoice_btn":"Save Invoice"}', 1, '2018-04-30 22:21:24', '5ae7dd3402994', 1, '2018-06-22 12:56:39'
这是我所拥有的。
#!/usr/bin/python3
import pymysql.cursors
# Connect to the database
connection = pymysql.connect(host='localhost',
user='user',
password='password',
db='shaggy',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# Read a single record
sql = "select invoice from invoices where id = 341"
cursor.execute(sql)
result = cursor.fetchall()
print(result)
print("-------------------------------------")
for row in result:
print(row["invoice"][0])
print(row["invoice"][1])
print(row["invoice"][2])
print(row["invoice"][3])
except Exception as e:
print("Exeception occured:{}".format(e))
finally:
connection.close()
这就是我的结果。
[{'invoice': '{"data":{"id":"341","clientCompanyName":"Dave Morehead","clientAddress":"18656 86th Ave, <br />Bernard,Iowa 52032, <br />United States, <br /> 563 249 5319.","job_descr":"Swine","Invoice":{"itemNo":["1","1","1","DF","F"],"itemName":["Pumped out of hog building, hauled 4.5 miles and applied to field (Jakes House)","Pumped out of hog building, hauled 3.5 miles and applied to field (Daughters house)","Pumped out of hog building, hauled 4 miles and applied to field(By your house)","Diesel Fuel","Finance charge"],"price":["0.0155",".0145",".015","1","1"],"quantity":["169000","88000","36002","113.00","68.22"],"total":["2619.50","1276.00","540.03","113.00","68.22"]},"notes":"281,000 total gallons","subTotal":"4616.75","tax":"","taxAmount":"0","totalAftertax":"4616.75","amountPaid":"","amountDue":"4616.75","companyAddress":""},"invoice_btn":"Save Invoice"}'}]
-------------------------------------
{
"
d
a
我的问题是，我如何继续通过数据获取关键：价值信息？
- 获取行
- 使用python函数将JSON转换为python结构
- 使用python代码遍历结构
- 根据需要创建
INSERT语句并执行它们
也就是说，步骤2和3最好通过应用程序语言完成，而不是通过SQL完成。