MySQL: How do I write query to choose MAX and today date to fetch data from given list and store in DB

Question

I having problem regarding store data in mysql database. a variable having list of list df = [['Euro', 0.877641, '2018-10-24'], ['British Pound', 0.7752180000000001, '2018-10-24'], ['Indian Rupee', 73.21869000000001, '2018-10-24'], ['Australian Dollar', 1.4135360000000001, '2018-10-24'], ['Canadian Dollar', 1.299316, '2018-10-24']] want to store this in the column currency_name, date ,value from the last updated date. So How could i write that logic in code(my be with max(), min() method of sql.)

tryied:

    import pandas as pd
     import pymysql
    import MySQLdb
        sqlconn = pymysql.connect(host='ip', port=port, user='username',
                                  passwd='pass', db='dbname', autocommit=True)
        # Create a cursor object
        if (sqlconn == None):
            print("conn not found")
        else:
            print("conn is found")


        cursor.execute("INSERT INTO TestCurrencyHistory (currency, date, value) VALUES (%s, %s, %s)") % (
                                              elements
                                            )
        element = cursor.fetchall()

        cursor.close()
        sqlconn.close()
Offcource this is not working. So please help ..

thanks in advance..(please let me no if there is any python/pandas logic to choose max and min date)

Hello. Could you precise your issue with input / output data required ? — hathija, Oct 26 '18 at 09:28

dennisdee · Accepted Answer · 2018-10-26T10:47:44.930

0

With mysql-connector, you could use executemany() but I'm not sure if pandas supports that yet. This post could help: how to transform pandas dataframe for insertion via executemany() statement?

df = [['Euro', 0.877641, '2018-10-24'], ['British Pound', 0.7752180000000001, '2018-10-24'], ['Indian Rupee', 73.21869000000001, '2018-10-24'], ['Australian Dollar', 1.4135360000000001, '2018-10-24'], ['Canadian Dollar', 1.299316, '2018-10-24']]

To get the latest date of df:

date_list = []
for x in df:
    date_list.append(x[-1])
latest = max(date_list)

# turn into datetime format, not str
latest = datetime.datetime.strptime(latest, '%Y-%m-%d').date()

# increase by one day
next_day = latest + datetime.timedelta(days=1)

I don't know what your SQL query looks like, but you might then do something like the following to get data from the next day:

latest_date_query_results = cursor.execute(
"""
SELECT currency, value, added_date
FROM your_table
WHERE added_date > {}
""".format(next_day)
)

The results should be turned into a tuple to work with executemany()

elements = [] 
for x in df:
    elements.append(tuple((x)))
elements = tuple((elements))
print(elements)
# (('Euro', 0.877641, '2018-10-24'), ('British Pound', 0.7752180000000001, '2018-10-24'), ('Indian Rupee', 73.21869000000001, '2018-10-24'), ('Australian Dollar', 1.4135360000000001, '2018-10-24'), ('Canadian Dollar', 1.299316, '2018-10-24'))

Then, you could do this:

sql = "INSERT INTO TestCurrencyHistory (currency, date, value) VALUES (%s, %s, %s)"
cursor.executemany(sql, elements)

edited Oct 26 '18 at 10:47

answered Oct 26 '18 at 09:29

dennisdee

160
10

To get help on the max() piece, I'll need a little more info about what exactly you're trying to do. Do you want to only insert data for the latest date? – dennisdee Oct 26 '18 at 09:30
I convert that data like: RIC price Added 0 Euro, 0.877641, 2018-10-24 1 British Pound, 0.775218, 2018-10-24 2 Indian Rupee, 73.218690, 2018-10-24 3 Australian Dollar, 1.413536 , 2018-10-24 ...........................I want to find last updated date from the previous there, from next to that date want to add the above data – psw Oct 26 '18 at 09:35
Sorry, not super sure what exactly you're trying to do. This article shows you how to get the latest date in SQL: https://www.w3resource.com/sql/aggregate-functions/max-date.php – dennisdee Oct 26 '18 at 09:45
Got Max date, want to add data from next to that date Suppose max date is 2018-10-20 so want to add data in existing table from 2018-10-21(may be with python, sql, pandas) – psw Oct 26 '18 at 10:00
added to the answer, hope this helps! – dennisdee Oct 26 '18 at 10:47
with your answer this error will comming: ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). – psw Oct 26 '18 at 13:04
Seems to be a pandas-specific error that has to do with "and / or" statements in python: https://markhneedham.com/blog/2017/07/26/pandas-valueerror-the-truth-value-of-a-series-is-ambiguous/ Perhaps this link helps you resolve. It's a separate issue, though! – dennisdee Oct 26 '18 at 13:28
@pavan have you been able to resolve? Would be helpful if you accepted as answer if so :) – dennisdee Oct 30 '18 at 09:29

MySQL: How do I write query to choose MAX and today date to fetch data from given list and store in DB

1 Answers1