Question 118865 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

returning oracle cursor results to json python

I am working with python oracle connection. But i have a issue returning date with the cursor result as json.

below is the json result of the cursor, the issue is format of create_dttm. When creating a dataframe from this it is not changing the format. Any suggestion

result = cursur.execute("**my query**")
data = list(result)
final = json.dumps(data)
print(final)
[{"create_dttm": {"$date": 1677264505842}, "update_dttm": {"$date": 1677264505842}, "wo_id": "ABC-63953"},{"create_dttm": {"$date": 1677264505843}, "update_dttm": {"$date": 1677264505843}, "wo_id": "ABC-63954"}]

I want the data to be like below when creating a dataframe

create_dttm   update_dttm   wo_id
2021-5-09     2021-5-09    ABC-63953
2021-5-09     2021-5-09    ABC-63953
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I created below function to help solve my question, used some parts from previous answer as well. But this works effeciently

    date_columns = ['create_dttm','update_dttm']
def clean_dateset(df):
    df = pd.DataFrame.from_records(df)
    for i in df.columns:
        if i in date_columns:
            df[i] = df[i].apply(lambda x: pd.to_datetime(x.get('$date'), unit='ms', errors = 'coerce').date())
    return df

my_fina_dataset = clean_dateset(df)

  2. 0

    You could do something like this:

    import pandas as pd

# Define the list of JSON objects
json_list = [{"create_dttm": {"$date": 1677264505842}, "update_dttm": {"$date": 1677264505842}, "wo_id": "ABC-63953"},
             {"create_dttm": {"$date": 1677264505843}, "update_dttm": {"$date": 1677264505843}, "wo_id": "ABC-63954"}]


dfItem = pd.DataFrame.from_records(json_list)
print(dfItem)

# to get only the timestamp
dfItem['create_dttm'] = dfItem['create_dttm'].map(lambda x: x['$date'])

dfItem['update_dttm'] = dfItem['update_dttm'].map(lambda x: x['$date'])

# converting the milliseconds to date time
dfItem['create_dttm'] = pd.to_datetime(dfItem['create_dttm'], unit='ms')
dfItem['update_dttm'] = pd.to_datetime(dfItem['update_dttm'], unit='ms')

# keeping only the date
dfItem['create_dttm'] =dfItem['create_dttm'].dt.date
dfItem['update_dttm'] =dfItem['update_dttm'].dt.date

print(dfItem)

    Output:
    enter image description here

    Also see if there’s a way you can directly convert the milliseconds to date time format and then extract only the date from it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search