So i need to make a python script for school to manipulate a MySQL database but when i want to add something in the database i get this error
#🔒 Python MySQL
tired vessel
mellow ridgeBOT
So i need to make a python script for school to manipulate a MySQL database but ...
@tired vessel
Python help channel opened
tired vessel
this is the code im using for this option
print("Add performance selected")
AthleteID = input("What is the athlete's ID? ")
CategoryID = input("What is the category ID? ")
EventID = input("What is the event ID? ")
Performance = input("What is the performance? Example: 10.5 sec or 60.2 meters ")
PerformanceDate = input("On what date was this achieved? Example: 2000-01-31 ")
sql = "INSERT INTO performances (AthleteID, CategoryID, EventID, Performance, PerformanceDate) VALUES (%s, %s, %s, %s, %s)"
values = (AthleteID, CategoryID, EventID, Performance, PerformanceDate)
mycursor.execute(sql, values)
mydb.commit()
this is the same code but in english
fossil crypt
what dpes the table look like? when you created it
youre inserting everythin as strings, and it looks like its expecting something as something not a string
tired vessel
fossil crypt
that would be your issue, convert the relevant strings to ints
tired vessel
okay so i need to specify the all the inputs?
vocal linden
is the correct placeholder character being used?
fossil crypt
values = (int(AthleteID), CategoryID, EventID, Performance, PerformanceDate)
they all got white instead of brown
fossil crypt
i normally use ?, but i have seen people use %s, but i dont know much about it other than google says its something about a str replacement
when you hover what does it say the error is
tired vessel
oh wait my mistake
fossil crypt
add int to everything that should be an int
tired vessel
all right i will try
vocal linden
according to the docs, %s is indeed the correct placeholder
tired vessel
getting the same error
i think something else is broken because my code is only 80 lines and this one goes all the way to 1199
fossil crypt
all that means is one of the functions to make sure you are using it right is catching the error
tired vessel
i use pymysql
fossil crypt
can you copy paste that error at the bottom, i dont feel like typing it out
tired vessel
yeah of course one moment
pymysql.err.OperationalError: (1136, "Column count doesn't match value count at row 1")
vocal linden
@tired vessel could you try enclosing the args tuple in a list?
tired vessel
how do i do that? sorry im very bad at python
fossil crypt
i found the issue
tired vessel
ohhh
fossil crypt
dont worry about that
tired vessel
okay
vocal linden
mycursor.execute(sql, [values])
fossil crypt
count how many columns you are selecting in the sql variable.
then count how many you are trying to insert in values
its throwing the error bc you are telling it 4 values and then giving 5
tired vessel
oh my god no way
vocal linden
ah, it's only 4 named columns
fossil crypt
didnt catch that bc im used to seeing the error worded differently since i use sqlite3 lol
vocal linden
OndereelID is missing
fossil crypt
can you try using ? instead of %s?
vocal linden
pymysql docs say to use %s
tired vessel
fossil crypt
im really mostly curious, if it works it fixes his problem, if not at least we tried something lol
vocal linden
If args is a list or tuple, %s can be used as a placeholder in the query. If args is a dict, %(name)s can be used as a placeholder in the query.
tired vessel
same error
fossil crypt
did you convert your datetime properly?
tired vessel
On what date was this achieved? Example: 2000-01-31 2024-11-09
vocal linden
@tired vessel so we're clear, you're running mycursor.execute(sql, values), yes? my advice from before of wrapping it in a list was incorrect
fossil crypt
if thats the format, i would actually change the entry to be a varchar bc date is something else entirely
tired vessel
<@341467182565294082> so we're clear, you're running `mycursor.execute(sql, valu...
yes
tired vessel
if thats the format, i would actually change the entry to be a varchar bc date i...
okay let me do that one moment
fossil crypt
date is basically an object that can give you any kind of date related data you can think of about its stored date
(if using datetime module lol)
fossil crypt
it seems to be a formatting error from what im reading on google
but i dont see that format error in your code
tired vessel
that's weird
fossil crypt
can you send what your code looks like now?
tired vessel
elif option == 4:
print("Prestatie toevoegen geselecteerd")
AtleetID = input("Wat is het ID van de atleet? ")
CategorieID = input("Wat is het ID van de categorie? ")
OnderdeelID = input("Wat is het ID van het onderdeel? ")
Prestatie = input("Wat is de prestatie? Voorbeeld: 10.5 sec of 60.2 meter ")
DatumPrestatie = input("Op welke datum is dit behaald? voorbeeld 2000-01-31 ")
sql = "INSERT INTO prestaties (AtleetID, CategorieID, Prestatie, DatumPrestatie) VALUES (%s, %s, %s, %s)"
values = (int(AtleetID), int(CategorieID), int(OnderdeelID), Prestatie, DatumPrestatie)
mycursor.execute(sql, values)
mydb.commit()
fossil crypt
your sql and values still dont line up
4 names, 4 %s, and 5 values
it needs to all be the same
mellow ridgeBOT
Python help channel closed