如何使用Pymysql/Python从第三方API插入数据到数据库？

Question

如何使用Pymysql/Python将实时数据从第三方API插入数据库？

我正在尝试从第三方API请求数据，

我们得到了如下的API链接: GET /api/v4/dblines

如何使用Pymysql/Python、requests、pymysql和pandas从第三方API向数据库插入实时数据？我们必须插入2020.01.01中的数据

下面是来自这个示例链接的示例JSON数据：https://api.example.com/api/v4/dblines?a=ABC123&b=1min

    [
    1593649440000, start_time
    "2.9923453200",
    "2.9923453200",
    "2.0045299700",
    "2.0045299700",
    "2.2400009700",
    1593649499999, end_time
    "2.0010870500",
    2,
    "2.0300009700",
    "2.0001359600",
    "0"
    ],
    [
    1593649500000,
    "2.9923453297",
    "2.9923453297",
    "2.9923453297",
    "2.9923453297",
    "25.950000970",
    1593649559999,
    "2.1176054000",
    4,
    "25.950000970",
    "2.1176054000",
    "0"
    ],
    [
    1593649560000,
    "2.9923453297",
    "2.9923453297",
    "2.9923453297",
    "2.9923453297",
    "2.0000009718",
    1593649619999,
    "1.00000097",
    0,
    "1.00000097",
    "1.00000097",
    "0"
    ],

Answer 1

下面是一个示例：

from sqlalchemy import create_engine
import pandas as pd
import requests

# getting json using requests
# data = requests.get('https://api.example.com/api/v4/dblines?a=ABC123&b=1min').json()
# data example
data = [
    [
        1593649440000,
        "2.9923453200",
        "2.9923453200",
        "2.0045299700",
        "2.0045299700",
        "2.2400009700",
        1593649499999,
        "2.0010870500",
        2,
        "2.0300009700",
        "2.0001359600",
        "0"
    ],
    [
        1593649500000,
        "2.9923453297",
        "2.9923453297",
        "2.9923453297",
        "2.9923453297",
        "25.950000970",
        1593649559999,
        "2.1176054000",
        4,
        "25.950000970",
        "2.1176054000",
        "0"
    ]
]

# create df using json from API
df = pd.DataFrame(
    data,
    columns=[
        # change aliases to column names here...
        'start_time',
        'alias1',
        'alias2',
        'alias3',
        'alias4',
        'alias5',
        'end_time',
        'alias6',
        'alias7',
        'alias8',
        'alias9',
        'alias10',
    ]
)
# drop unnecessary columns, duplicates, processing df, blabla etc...
# and init db connection
engine = create_engine('mysql+pymysql://{user}:{pw}@localhost/{db}'
                       .format(user='db_user',
                               pw='db_password',
                               db='db_name'))
# insert df into db using connection 
# change if_exists if you need
df.to_sql(con=engine, name='table_name_here', if_exists='replace')

请参见注释。希望这能有所帮助。