I am attempting to insert/update a df into a mysql table. I'm receiving this error
"_mysql_exceptions.OperationalError: (1136, "Column count doesn't match value count at row 1")"
on the execute line in the code below. Any suggestions on a workaround or a better way?
db = MySQLdb.connect(
host = "myhost",
user = "username",
passwd = "password",
db = "mydb",
charset='utf8'
)
df.to_sql(con=db, name='t', if_exists='replace', flavor='mysql')
sqlStatement = """INSERT INTO db.films SELECT t.* FROM db.t
ON DUPLICATE KEY UPDATE
films.releasedP=t.releasedP,
films.runtimeP=t.runtimeP,
films.imdbRatingP=t.imdbRatingP,
films.votesYearP=t.votesYearP,
films.years=t.years,
films.votesYear=t.votesYear
;"""
cursor = db.cursor()
cursor.execute(sqlStatement)
db.commit()
This is the schema of the tables I am attempting to insert/update data.
DESCRIBE db.t;
+----------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+-------------+------+-----+---------+-------+
| Title | varchar(63) | YES | | NULL | |
| imdbID | varchar(63) | YES | | NULL | |
| Released | varchar(63) | YES | | NULL | |
| Runtime | varchar(63) | YES | | NULL | |
| imdbRating | varchar(63) | YES | | NULL | |
| imdbVotes | varchar(63) | YES | | NULL | |
| releasedDateTime | datetime | YES | | NULL | |
| maxDateTime | datetime | YES | | NULL | |
| years | float | YES | | NULL | |
| votesYear | float | YES | | NULL | |
| rating | float | YES | | NULL | |
| runtimeF | float | YES | | NULL | |
| releasedP | float | YES | | NULL | |
| runtimeP | float | YES | | NULL | |
| imdbRatingP | float | YES | | NULL | |
| votesYearP | float | YES | | NULL | |
+----------------------------+-------------+------+-----+---------+-------+
DESCRIBE db.films;
+----------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+-------------+------+-----+---------+-------+
| Actors | varchar(63) | YES | | NULL | |
| Awards | varchar(63) | YES | | NULL | |
| Country | varchar(63) | YES | | NULL | |
| Director | varchar(63) | YES | | NULL | |
| Genre | varchar(63) | YES | | NULL | |
| Language | varchar(63) | YES | | NULL | |
| Metascore | varchar(63) | YES | | NULL | |
| Plot | varchar(63) | YES | | NULL | |
| Poster | varchar(63) | YES | | NULL | |
| Rated | varchar(63) | YES | | NULL | |
| Released | varchar(63) | YES | | NULL | |
| Response | varchar(63) | YES | | NULL | |
| Runtime | varchar(63) | YES | | NULL | |
| Title | varchar(63) | YES | | NULL | |
| Type | varchar(63) | YES | | NULL | |
| Writer | varchar(63) | YES | | NULL | |
| Year | varchar(63) | YES | | NULL | |
| imdbID | varchar(63) | YES | UNI | NULL | |
| imdbRating | varchar(63) | YES | | NULL | |
| imdbVotes | varchar(63) | YES | | NULL | |
| years | float | YES | | NULL | |
| votesYear | float | YES | | NULL | |
| rating | float | YES | | NULL | |
| runtimeF | float | YES | | NULL | |
| releasedP | float | YES | | NULL | |
| runtimeP | float | YES | | NULL | |
| imdbRatingP | float | YES | | NULL | |
| votesYearP | float | YES | | NULL | |
| releasedDateTime | datetime | YES | | NULL | |
| maxDateTime | datetime | YES | | NULL | |
+----------------------------+-------------+------+-----+---------+-------+
The schemas of the tables db.films and db.t do not match. So you need to define wich columns of table db.films will be set to wich value from table ``.
Try this query:
INSERT INTO db.films (
Title,
imdbID,
Released,
Runtime,
imdbRating,
imdbVotes,
releasedDateTime,
maxDateTime,
years,
votesYear,
rating,
runtimeF,
releasedP,
runtimeP,
imdbRatingP,
votesYearP
)
SELECT
Title,
imdbID,
Released,
Runtime,
imdbRating,
imdbVotes,
releasedDateTime,
maxDateTime,
years,
votesYear,
rating,
runtimeF,
releasedP,
runtimeP,
imdbRatingP,
votesYearP
FROM db.t
ON DUPLICATE KEY UPDATE
films.releasedP=t.releasedP,
films.runtimeP=t.runtimeP,
films.imdbRatingP=t.imdbRatingP,
films.votesYearP=t.votesYearP,
films.years=t.years,
films.votesYear=t.votesYear