Reputation: 377
I am doing a classification using k-nearest neighbor with python. The data I am using is float type, latitude
and longitude
and get the output from it like this:
prediction
preds2 = model.predict(new_list)
print(type(preds2))
print(preds2)
print("-----------")
print(new_list)
print(type(new_list))
print("-----------")
Output prediction not merged
<class 'numpy.ndarray'>
['Taman Suaka Margasatwa Angke' 'Taman Suaka Margasatwa Angke']
-----------
[[-6.1676580997222, 106.9042428], [-6.1676580997222, 106.9042428]]
<class 'list'>
Merge them together
mergedata = [[*new_list, preds2] for new_list, preds2 in zip(new_list, preds2)]
print(mergedata)
output from mergedata
[[-6.1676580997222, 106.9042428, 'Taman Suaka Margasatwa Angke'], [-6.1676580997222, 106.9042428, 'Taman Suaka Margasatwa Angke']]
I want to store the output from mergedata
to database mysql so this is the code that I've tried
import mysql.connector
connection = mysql.connector.connect(host='localhost',
database='destinasi',
user='root',
password='')
mycursor = connection.cursor()
myArray = mergedata
arraySize = len(myArray)
for r in range(0,arraySize):
try:
mycursor.execute(
"""INSERT INTO hasilclass (Latitude,Longitude,Class) VALUES (%s,%s,%s)""",
(",".myArray[r], )
)
connection.commit()
except:
print("Error")
connection.rollback()
The output is:
Error
Error
This is the display of database in mysql
CREATE TABLE `hasilclass` (
`Latitude` float(15,11) NOT NULL,
`Longitude` float(15,11) NOT NULL,
`Class` varchar(55) NOT NULL
)
It seems that mycursor.execute
is not executing the mysql statement, i don't know the correct way to do it. Any Suggestions how to correct it? is the statement is false? all the data is from mysql as well but im using pymysql to call all the data and do all the work untill i need to store them to database.
The reference in the internet i found is using mysql.connector to store 2 dimensional list in python so i follow it and it goes error like this.
Upvotes: 0
Views: 54
Reputation: 83587
You are trying to build a tuple with a single element. However, it doesn't work the way you want because ",".myArray
tries to get a property named myArray
from the string ","
, but this doesn't exist. Instead, myArray
is a global variable that you want to access.
To do this, change
(",".myArray[r], )
to
myArray[r]
Also, in Python, we prefer to iterate over the elements of an array directly rather than counting the indexes. This means you can change
arraySize = len(myArray)
for r in range(0,arraySize):
to
for row in myArray:
And now you can simplify your SQL command further:
mycursor.execute(
"INSERT INTO hasilclass (Latitude,Longitude,Class) VALUES (%s,%s,%s)",
row
)
Finally, a warning that assigning
myArray = mergedata
does not make a copy. This doesn't look like it does much of anything for you so I suggest removing it. Instead, just use mergedata
directly:
for row in mergedata:
Upvotes: 2