mySQL MULTIPLE ROWS DYNAMIC INSERT PYTHON

Question

My Objective is to add multiple rows of Data in the Table but I get an error everytime , I tried multiple variations of the code and Sorry for not asking the question earlier , I didn't know how , I don't use much of Stack Overflow .

The Updated Code is in the end .

Error:

Traceback (most recent call last):
  File "G:/Python/sqlconnector4.py", line 25, in <module>
    entry_table(number)
  File "G:/Python/sqlconnector4.py", line 17, in entry_table
    c.execute("INSERT INTO T1 (NAME , ADDRESS ) VALUES (thelist.split("'',''"))")
  File "C:\Users\tushar\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\cursor.py", line 559, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\tushar\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\connection.py", line 494, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\tushar\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\connection.py", line 396, in _handle_result
    raise errors.get_exception(packet)
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

Error

import mysql.connector

my=mysql.connector.connect(host='localhost' , user='root' , password='rootroot' , database='tushar')
c=my.cursor()

def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS T1 (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(30) NOT NULL , ADDRESS VARCHAR(50) NOT NULL)")
    my.commit()

def entry_table(n):
    thelist = []  
    for x in range(0,n) :
        user_name = input("ENTER NAME NUMBER {}: ".format(x+1))
        user_state = input("ENTER STATE {} LIVES IN : ".format(user_name))
        thelist.append(user_name)
        thelist.append(user_state)
        c.execute("INSERT INTO T1 (NAME , ADDRESS ) VALUES (thelist.split("'',''"))")
        my.commit()
    for x in range(0,n) :
        c.execute("SELECT * FROM T1")

number = int(input("Enter The Number of Rows You want to Fill : "))

create_table()
entry_table(number)

import mysql.connector

my=mysql.connector.connect(host='localhost' , user='root' , password='rootroot' , database='tushar') c=my.cursor()

def create_table():

c.execute("CREATE TABLE IF NOT EXISTS T1 (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(30) NOT NULL , ADDRESS VARCHAR(50) NOT NULL)")
my.commit()

def entry_table(n):

for x in range(0,n) :
    user_name = input("ENTER NAME NUMBER {}: ".format(x+1))
    user_state = input("ENTER STATE {} LIVES IN : ".format(user_name))
    c.execute("INSERT INTO T1 (NAME , ADDRESS ) VALUES ( ? , ?)", user_name , user_state )
    my.commit()

c.execute("SELECT * FROM T1")
myresult = c.fetchall()

for x in myresult:
      print(x)

number = int(input("Enter The Number of Rows You want to Fill : ")) create_table() entry_table(number) c.close() my.close()

Answer 1

The error is because you are passing the python syntax as a part of your sql statement and the commands don’t make sense. Many fixes, simplest one is that you don’t need the list

def entry_table(n):
    for x in range(0,n) :
        user_name = input("ENTER NAME NUMBER {}: ".format(x+1))
        user_state = input("ENTER STATE {} LIVES IN : ".format(user_name))
        data = (user_name, user_state)
        c.execute("INSERT INTO T1 (NAME , ADDRESS ) VALUES (%s, %s);", data)
        my.commit()
    c.execute("SELECT * FROM T1")
    print(c.fetchall())

Some other things that are out of scope here but relevant:

1. The transaction could be committed after the loop since we are not accessing the data prior to it.
2. The cursor is never closed.
3. The connection is never closed.

Edit: Replaced ?,? With%s, %s per API Docs