主页 / 问题 / 1365427
Accepted
Miekrif
Asked:2022-05-24 15:08:43 +0000 UTC2022-05-24 15:08:43 +0000 UTC

如何使用 pymysql 正确地将 json 写入 mysql？

  • 772

亲爱的专家，有一个带有mysql的容器和一个包含来自git的数据的字典，该字典被转换为json

我只在python中待了一个星期，我正在等待严厉但有建设性的批评并要求您提供链接，因为我在互联网上挖出的所有内容都没有给我一寸解决缺少此信息的问题

import mysql.connector
import json
import mysql
import pymysql
import pymysql.cursors
from PythonGIT import Lastwords

#make json data from Lastwords
json_send = json.dumps(Lastwords, indent=4)
#print(json_send)
#make connect to DB
def create_connection(host_name, host_port, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            port=host_port,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection
connection = create_connection("localhost", 3306, "root", "My:S3cr3t/", "jiragit")
cursor = connection.cursor(pymysql.cursors.DictCursor)
#send json to DB
# json_send
# sql = ""
with cursor:
    sql = "CREATE TABLE IF NOT EXISTS jiragit ( JiraTicket TEXT, EVENT TEXT) VALUES (%s,%s)"
    cursor.execute(sql, json_send)




connection.commit()
#Close connections
connection.close()
print("Connect close")

json

"MID-4151": [
        [
            "7e5ac616df12ed6fb344c8cf1817e5a9f4ee6c34",
            "kurbat_kanstantsin"
        ],
        [
            "1f201a2c10e12f3781afb7cf52e0b24530ee22da",
            "kurbat_kanstantsin"
        ],
        [
            "e473e1b77857fa2ef6e34c7f4117e48d1149cb4c",
            "kkurbat"
        ],
        [
            "41085f9b17a02b1773da26291573be51dd7c44fc",
            "kkurbat"
        ]
    ],
    "IN-2558": [
        [
            "18a382da0903bc4c1aa8dbdc09423e6120293409",
            "artem"
        ],
        [
            "f28fee874a30b854536ace422f401181b70d6f28",
            "artem"
        ],
        [
            "c5f4b78781e1495c21fc02bf5a696835a1eab431",
            "artem"
        ],
        [
            "313b300d2e4ae3eb17f62cf2acd6d00713df0ca9",
            "artem"
        ],
        [
            "2a7a1a6517ffb71f5a0d1da3cf18bd6c511102d0",
            "artem"
        ]
    ],

听写

{'CE-2019': [('a3873de1b37c14ea2d9379387026da6188cdd5d2', 'Ivan Vdovin'), ('327f430f39dd2db0c4ad9bb8fa599d7be7b1b4df', 'dmitrykoptik')], 'Merge_or_Fix': [('467f483d9fac6f2a587065ad6c3eeb4557b430bc', 'Stanislav Dudkov aka D2269'), ('1565ec5429da1156b593ea16658409288176edbe', 'Petr Sabaleuski'), ('2bda4e434cbe9271e9719feb16d27b04058d689f', 'Petr Sabaleuski'), ('caf6ee5460645a2691aeb03693a94327fd6833e7', 'Alex_Odinokov'),
python

1 个回答

  1. Best Answer

    问题：

    吉拉票 事件
    MID-4151 [["7e5ac616df12ed6fb344c8cf1817e5a9f4ee6c34", "kurbat_kanstantsin"], ["1f201a2c10e12f3781afb7cf52e0b24530ee22da", "kurbat_kanstantsin"], ["e473e1b77857fa2ef6e34c7f4117e48d1149cb4c", "kkurbat"], ["41085f9b17a02b1773da26291573be51dd7c44fc", "kkurbat"]]
    IN-2558 [["18a382da0903bc4c1aa8dbdc09423e6120293409", "artem"], ["f28fee874a30b854536ace422f401181b70d6f28", "artem"], ["c5f4b78781e1495c21fc02bf5a696835a1eab431", "artem"], ["313b300d2e4ae3eb17f62cf2acd6d00713df0ca9", "artem"], ["2a7a1a6517ffb71f5a0d1da3cf18bd6c511102d0", "artem"] ]

    您如此想象桌子还是以某种方式不同？

    create table test_scm.jira_git
(
    JiraTicket text,
    EVENT      json
);

    升级版：

    尝试这样的事情：

    • 使用数据库的库和你的不一样，但也不错，使用它们的原理是相同的。
    • 顺便说一句，您被正确地告知您的 json 无效。
    from pymysql.cursors import Cursor
from pymysql import connect
import simplejson as json

conn = connect(
    host='localhost',
    port=3306,
    user='user',
    password='********',
    autocommit=True,
    database='db'
)

cur: Cursor = conn.cursor()

# В случае если Ваш JSON уже словарь
data = {
    "MID-4151": [
        [
            "7e5ac616df12ed6fb344c8cf1817e5a9f4ee6c34",
            "kurbat_kanstantsin"
        ],
        [
            "1f201a2c10e12f3781afb7cf52e0b24530ee22da",
            "kurbat_kanstantsin"
        ],
        [
            "e473e1b77857fa2ef6e34c7f4117e48d1149cb4c",
            "kkurbat"
        ],
        [
            "41085f9b17a02b1773da26291573be51dd7c44fc",
            "kkurbat"
        ]
    ],
    "IN-2558": [
        [
            "18a382da0903bc4c1aa8dbdc09423e6120293409",
            "artem"
        ],
        [
            "f28fee874a30b854536ace422f401181b70d6f28",
            "artem"
        ],
        [
            "c5f4b78781e1495c21fc02bf5a696835a1eab431",
            "artem"
        ],
        [
            "313b300d2e4ae3eb17f62cf2acd6d00713df0ca9",
            "artem"
        ],
        [
            "2a7a1a6517ffb71f5a0d1da3cf18bd6c511102d0",
            "artem"
        ]
    ]
}

# В случае если Ваш JSON - строка - воспользуйтесь `json.loads()`


def prepare_data(item):
    key, val = item
    return key, json.dumps(val)


cur.executemany(
    "insert into test_scm.jira_git ( JiraTicket, EVENT ) values (%s, %s) ",
    [
        *map(prepare_data, data.items())
    ]
)
    • 1

相关问题