Error when inserting row into MySQL database from Python

Question

I'm trying to update an empty mySQL database using Python.

This is the error I receive:

pymysql.err.InternalError: (1416, 'Cannot get geometry object from data you send to the GEOMETRY field')

Here's the problematic code:

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `projects` (`name`, `country`,  `activation_date`, `active`) VALUES (%s, %s, %s, %s)"
        cursor.execute(sql, ('Nate', 'USA', '2016-06-23 09:11:32', '1'))

    connection.commit()

finally:
    connection.close()

Here is what the table looks like in the database (it's empty):

Could you include the table's definition (including indexes and triggers)? It's not quite obvious from your post what raises the error. — Ilja Everilä
– Ilja Everilä, Commented Jun 23, 2016 at 7:49
Yes, I think you're using the datatype LineString the wrong way and meant to use a varchar or text type. A LineString consists of Points and is meant for spatial data, not text. — Ilja Everilä
– Ilja Everilä, Commented Jun 23, 2016 at 8:00

Yuval · Accepted Answer · 2016-06-23 08:02:17Z

1

It looks like you're using the MySQL type linestring to represent a string, while in MySQL the correct type to represent a string of characters would be text or varchar(<some_length>). linestring is a representation of a geometric line, as you can see here: https://dev.mysql.com/doc/refman/5.7/en/gis-linestring-property-functions.html

To fix this, simply change the column types of name and country to varchar(<some-length>) or text. See: http://dev.mysql.com/doc/refman/5.7/en/char.html

answered Jun 23, 2016 at 8:02

Yuval

2371 gold badge2 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nate Over a year ago

Thanks everyone, this fixed it!

Collectives™ on Stack Overflow

Error when inserting row into MySQL database from Python

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related