CODEWITHSUNDEEP
pythoncsvmysql-python
Fiore
Fiore

Reputation: 33

Data not getting inserted in mysql by python

I have used the same code in ubuntu and it worked fine but when i tried to run it in windows it gave me a "()" output.What can be the reason .I am new to this field to i need assistance.`

import csv
import string
import MySQLdb

with open('cool1.txt','r') as csvfile:
    scoreFileReader=csv.reader(csvfile)
    scoreList=[]
    for row in scoreFileReader:
        if len(row) !=0:
            scoreList=scoreList + [row]



csvfile.close()

print(scoreList)

temp=str(scoreList).translate(string.maketrans('', ''), '[]\'')
#print(temp)
db = MySQLdb.connect("localhost","root","","test123" )
  #setup cursor
cursor = db.cursor()
 #create anooog1 table
cursor.execute("DROP TABLE IF EXISTS db1234")

sql = """CREATE TABLE db1234 (
          uid VARCHAR(100) NOT NULL,
          price INT(100) NOT NULL)"""
cursor.execute(sql)

try:
   cursor.execute("""INSERT INTO db1234 VALUES (%s,%s)""",(scoreList))
   db.commit()
except:     
     db.rollback()
#how table
cursor.execute("""SELECT * FROM db1234;""")
print cursor.fetchall()
db.close()

and the text file data looks like these:

E0 C1 7F 7A

0

after removing the except the error came like these

[['E0 C1 7F 7A'], ['0']]
Traceback (most recent call last):
  File "csvfile_writer2.py", line 31, in <module>
    cursor.execute("""INSERT INTO db1234 VALUES (%s,%s)""",(scoreList))
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'),("\'0\'",))\' at line 1')

before with except clause it was coming 

C:\Users\supar\Desktop>python csvfile_writer2.py
[['E0 C1 7F 7A'], ['0']]
()

C:\Users\supar\Desktop>

Upvotes: 0

Views: 44

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169524

You have two placeholders and only one item to insert on each iteration. So just reduce the number of placeholders to one.

And since you have a container of containers to be inserted use .executemany(), e.g.:

cursor.executemany("""INSERT INTO db1234 VALUES (%s)""", scoreList)

Upvotes: 1

Related Questions