MySQLConverter' object has no attribute '_tuple_to_mysql' exception with mysql-connector

Question

I have 8 kinds of data that I would like to insert into a mysql table through mysql-connector using python. I have looked at some documents saying that it is better to use int, string, or tuple when using mysql-connector. I have tried to adjust some data types into either string or tuple, but the IDE keeps showing error.... If anyone please help me clarify which data type I shall use.

The data structure is set as follows(if anything is better to be changed please kindly let me know):

+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| URL      | varchar(1023) | YES  |     | NULL    |       |
| Title    | varchar(1023) | YES  |     | NULL    |       |
| Content  | varchar(1023) | YES  |     | NULL    |       |
| Month    | varchar(1023) | YES  |     | NULL    |       |
| Date     | varchar(1023) | YES  |     | NULL    |       |
| Year     | varchar(1023) | YES  |     | NULL    |       |
| Time     | varchar(1023) | YES  |     | NULL    |       |
| TimeZone | varchar(1023) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

---

My codes are as follows:

for i in range(len(URL)):
    dbcur.execute(
        """INSERT INTO scripting (URL, Title, Content, Month, Date, Year, Time, TimeZone)
           VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""",
           ((URL[i],), (Title[i],), (Content[i],), (Month[i],), (Date[i],), (Year[i],), 
           (Time1[i],), (TimeZone[i],)))
    dbcon.commit()

ps- URL[], Title[], Content[]... TimeZone[] are lists of data, and their ranges are the same.

• URL: url. I set this with tuple and it is fine.(can be successfully stored)

• Title: A title of an essay.(i.e. an sentence) I have tried to either set it as tuple with (Title[i],) or a string with StringIO.String(Title[i]), but the error are

  MySQLConverter' object has no attribute '_tuple_to_mysql  
  AttributeError: 'module' object has no attribute 'String'
  

  separately.

• Content: several sentences. faced same problems as above

• Month, Date, Year, Time, TimeZone: have not tried yet, but I guess I can import these data with tuple type?

I have looked over stackoverflow and tried several data types but it still doesn't work out here. How do I deal with these data may someone kindly let me know?

Answer 1

You're wrapping each individual argument within a tuple; don't do that. That is, do this instead:

dbcur.execute(
     """INSERT INTO scripting (URL, Title, Content, Month, Date, Year, Time, TimeZone) 
     VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""",
     (URL[i], Title[i], Content[i], Month[i], Date[i], Year[i], Time1[i], TimeZone[i]))

You only need to wrap all substituted values in one tuple, no exceptions.

I can see why you're confused ("URL: url. I set this with tuple and it is fine."): with DBAPI a single value also needs to be wrapped in 1-tuple in the following example), but that is still the same rule applying here:

 dbcur.execute('INSERT INTO scripting (URL) VALUES (%s)', (URL,))

Now we only substitute URL, but we still wrap "all", e.g. that single argument, in one tuple.

Answer 2

Here i have a solution for you.

let's consider only three columns as example. URL, TITLE and CONTENT. As you have mentioned URL and other are lists of data.

URL=['url1','url2','url3']
TITLE=['title1','title2','title3']

and so on. And you have the same columns in DATABASE table.

cursor_object.execute(query,params)

lets write a query here:

query='''
INSERT INTO scripting(URL, TITLE, CONTENT)
VALUES (%s,%s,%s)'''

and executing the sql command:

cursor_object.execute(query%(URL[i],TITLE[i],CONTENT[i]))

when it is translated following will be the result in first iteration.

'INSERT INTO scripting(url, title)VALUES (url1,title1)' url1 is not the string here when actually translated into mysql query. So you have to change little.

query='''
INSERT INTO scripting(URL, TITLE, CONTENT)
VALUES ("%s","%s","%s")'''

OR

Alternatively you can use dictionary method.

query=''' *query statement* values
(%(url)s,%(title)s,%(content)s)
dictionary={'url':'url1','title':'title1','content':'content1'}
cursor_object.execute(query,dictionary)

for multiple rows:

for i in range len(URL):
    cursor_object.execute(query,{'url':URL[i],'title':TITLE[i],'content':CONTENT[i]}

simplest solution was given by @Antti Haapala. Hope you enjoy.

Answer 3

Antti Haapala has a great answer but you could also tidy it up by using zip to build the row to insert for you

for row in zip(URL, Title, Content, Month, Date, Year, Time1, TimeZone):
    dbcur.execute(
        """INSERT INTO scripting (URL, Title, Content, Month, Date, Year, Time, TimeZone)
           VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""",
           row)
    dbcon.commit()