I'm trying to:
The issue is that I run the code, get the all clear message, check the database and nothing seems to have changed... #1 might be working but I can't figure out how to run it using all the rows and #2 nothing seems to have changed when I look back at the database:
import csv
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="",
database="hw6"
)
mycursor = mydb.cursor()
f = open("UCI_Credit_Card.csv")
for row in csv.reader(f):
a= row[0]
b= row[1]
c= row[2]
d= row[3]
e= row[4]
f= row[5]
g= row[6]
h= row[7]
i= row[8]
j= row[9]
k= row[10]
l= row[11]
m= row[12]
n= row[13]
o= row[14]
p= row[15]
q= row[16]
r= row[17]
s= row[18]
t= row[19]
u= row[20]
v= row[21]
w= row[22]
x= row[23]
y= row[24]
sql = "INSERT INTO customers (ID, LIMIT_BAL, SEX, EDUCATION, MARRIAGE, AGE, PAY_0, PAY_2, PAY_3, PAY_4, PAY_5, PAY_6, BILL_AMT1, BILL_AMT2, BILL_AMT3, BILL_AMT4, BILL_AMT5, BILL_AMT6, PAY_AMT1, PAY_AMT2, PAY_AMT3, PAY_AMT4, PAY_AMT5, PAY_AMT6, default_payment_next_month) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
val = (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
mycursor = mydb.cursor(buffered=True)
print("Before updating a record ")
sql_select_query = """select * from customers"""
mycursor.execute(sql_select_query)
record = mycursor.fetchone()
print(record)
# Update single record now
sql_update_query = """UPDATE CUSTOMERS SET SEX = 2 where SEX = 3"""
mycursor.execute(sql_update_query)
mydb.commit()
print("Record Updated successfully ")
print("After updating record ")
mycursor.execute(sql_select_query)
record = mycursor.fetchone()
print(record)
# Delete record now
mycursor = mydb.cursor(buffered=True)
sql_update_query = "DELETE FROM customers WHERE (BILL_AMT1<0)AND(BILL_AMT2<0)AND(BILL_AMT3<0)AND(BILL_AMT4<0)AND(BILL_AMT5<0)AND(BILL_AMT6<0) """
sql_delete_query = """select * from customers"""
mycursor.execute(sql_update_query)
mydb.commit()
print("Record Delete successfully ")```
The UPDATE
statement looks fine.
But when it comes to the second requirement:
Remove all data records with negative BILL_AMT values (in any of the BILL_AMT1 through BILL_AMT6)
Presumably, you want or
ed conditions rather than and
in the delete
query:
DELETE FROM customers
WHERE
BILL_AMT1 < 0
OR BILL_AMT2 < 0
OR BILL_AMT3 < 0
OR BILL_AMT4 < 0
OR BILL_AMT5 < 0
OR BILL_AMT6 < 0