python - Python 提取/解析存储在 Mariadb 中的嵌套 json 数据

Question

因此，我继承了一个用 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

我的问题是如何继续通过数据获取 key:value 信息？

标签： pythonjsonmariadb

Answer 1

1. 获取行。
2. 使用 python 函数将 JSON 转换为 python 结构。
3. 使用 python 代码浏览结构。
4. 根据需要创建INSERT语句并执行它们。

也就是说，步骤 2 和 3 最好通过应用程序语言完成，而不是通过 SQL。