SQL with python is giving me error when I am trying to read in a file-CodePudding

What I was trying to do was read in a file called vegi.txt; The lines in blocs of two go like this.

Name of vegi
Cost of vegi

I am trying to use sql with python connector to read in this file and put the names in one row correlating to the cost in a second row

Code:

import mysql.connector

cnx = mysql.connector.connect(user='root', passwd='1234',
                              host='localhost',
                              database='algo')

f = open("vegi.txt", "r");
names = []
val = []
while True :
  a = f.readline()
  if a == '': break
  names.append(a[:-1])
  b = f.readline()
  val.append(b)


mycursor = cnx.cursor()
mycursor.execute("CREATE TABLE veg (vegName varchar(50), cost varchar(50)")

for i in range(len(names)):
    mycursor.execute("INSERT INTO veg (vegName, cost) VALUES (%s,%s)", (names[i],val[i]))

cnx.commit()

mycursor.execute("select * from veg")

for x in mycursor:
    print(x)

Error:

Traceback (most recent call last):
  File "C:\Users\alokk\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 472, in cmd_query
    raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: 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 '' at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:/Anchit/Important stuff for Anchit/Computer/Coding/MySQL/test.py", line 19, in <module>
    mycursor.execute("CREATE TABLE veg (vegName varchar(50), cost varchar(50)")
  File "C:\Users\alokk\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\cursor_cext.py", line 266, in execute
    raw_as_string=self._raw_as_string)
  File "C:\Users\alokk\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 475, in cmd_query
    sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 '' at line 1

vegi.txt:

tomato
15.5
potato
12.5
cucumber
30
coconut
15.0

I tried to read in a file called vegi.txt and put the values into a data base. I expected it would work and put the names and values in separate rows. This did not happen.

CodePudding user response：

Where possible, avoid collapsing code to single lines. It hides syntax errors.

Long narrow code is always better than short wide code, both for spotting typos and for diff tools such as used by git...

mycursor.execute("""
  CREATE TABLE veg (
    vegName varchar(50),
    cost varchar(50)
""")

This makes it much more obvious that a closing ) at the end of your SQL string.

mycursor.execute("""
  CREATE TABLE veg (
    vegName varchar(50),
    cost varchar(50)
  )
""")

CodePudding user response：

mycursor.execute(""" CREATE TABLE veg ( vegName varchar(50), cost varchar(50) ) """)