comparing 2 data field in mysql with python

Question

This is my database

i want to comparing 2 date time datatype (datetime_comp & date_time) simple comparison. etc : date1 > date 2 = "On_Time"

but my problem is, i need to compare the data 1 by 1 (loop for sure) and this is my code

from datetime import datetime
import pymysql
import time

#Declare Connection
conn = pymysql.connect(host='localhost', port='', user='root', passwd='', db='tes_coba', use_unicode=True, charset="utf8mb4")
cur = conn.cursor()

EW = "Tepat Waktu"
LW = "Tidak Tepat Waktu"

#get data from database
cur.execute("SELECT datetime_comp FROM `tweet2`")
row1 = cur.fetchall()
cur.execute("SELECT date_time FROM `tweet2`")
row2 = cur.fetchall()

n  = 1

for date1 in row1:
    print(date1)

for date2 in row2:
    print(date2)

if date1 > date2:
    Result=EW
elif date1 < date2:
    Result=LW

print(Result)
cur.execute("UPDATE tweet2 SET on_time=%s WHERE no=%s AND relevance='Relevan'",(str(Result), str(n)))
n = n + 1


conn.commit()
cur.close()
conn.close()

and the result is, the comparison code only work with last data in database, because the comparison code does not looping for sure (confusing)

but if i put the comparison code to one if the looping process,

for date1 in row1:
    print(date1)

for date2 in row2:
    print(date2)

    if date1 > date2:
        Result=EW
    elif date1 < date2:
        Result=LW

only the last data compare with all data

i knew it for sure, but if i comparing inside the loop, only the last database comparing with the loop data (if u know what i mean) — Nanda
– Nanda, Commented Jul 25, 2018 at 4:57
also it will be better to SELECT both columns in one query like SELECT datetime_comp, date_time FROM tweet2 and iterate over tuples — Azat Ibrakov
– Azat Ibrakov, Commented Jul 25, 2018 at 5:08

Ankit Jaiswal · Accepted Answer · 2018-07-25 08:04:46Z

2

First of all, you do not need two separate queries to match two columns of the same table. It can be done in the same query.

cur.execute("SELECT * FROM `tweet2`")
records = cur.fetchall()

iterate the records:

for record in records:
    if record[2] < record[5]: # I'm assuming the column index 2 and 5 have the relevant table column values
        do something here
    else:
        do something else here

answered Jul 25, 2018 at 8:04

Ankit Jaiswal

23.5k5 gold badges43 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nanda Over a year ago

really thanks to you man, this is work. i didnt know how to mention the record before. thanks again :D

experiment · Accepted Answer · 2018-07-25 07:28:45Z

1

Firstly load your table into a dataframe and np.where to compare columns to form a new column

import pymysql
import time
import numpy as np
import pandas as pd

#Declare Connection
conn = pymysql.connect(host='localhost', port='', user='root', passwd='', db='tes_coba', use_unicode=True, charset="utf8mb4")


query = "SELECT date_time,datetime_comp FROM tweet2"
df = pd.read_sql(query, conn)

df['result'] = np.where(df['datetime_comp']>df["date_time"], 'Tepat Waktu', 'Tidak Tepat Waktu')

I hope it works for you!

edited Jul 25, 2018 at 7:28

answered Jul 25, 2018 at 5:46

experiment

3154 silver badges19 bronze badges

6 Comments

Nanda Over a year ago

(ValueError: DataFrame constructor not properly called!) error sir @experiment. could you help with pymysql library ?

experiment Over a year ago

@Nanda Dont call me sir/maam! What kind of data you get when you run the query SELECT date_time,datetime_comp FROM tweet2 ?

Nanda Over a year ago

okay sorry man :D, its going wrong when df = pd.DataFrame(cur.fetchall()) and pop the error

Ankit Jaiswal Over a year ago

Why do we need pandas and np for such task?

experiment Over a year ago

@AnkitJaiswal There are other solutions as well. I prefer loading the data to dataframes to do the processing part.As nanda wanted an extra column of results ,using dataframes we can get that effectively!

|

Collectives™ on Stack Overflow

comparing 2 data field in mysql with python

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related