DB-API with Python

Question

I just spent the better half of my day trying to figure this one out so please for the love of god, help me.

I'm trying to insert some data into a local MySQL database by using MySQL Connector/Python -- apparently the only way to integrate MySQL into Python 3 without breaking out the C Compiler.

I tried all the examples that come with the package; Those who execute can enter data just fine. Unfortunately my attempts to write anything into my tables fail.

Here is my code:

import mysql.connector


def main(config):
    db = mysql.connector.Connect(**config)
    cursor = db.cursor()

    stmt_drop = "DROP TABLE IF EXISTS urls"
    cursor.execute(stmt_drop)

    stmt_create = """
    CREATE TABLE urls (
        id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
        str VARCHAR(50) DEFAULT '' NOT NULL,
        PRIMARY KEY (id)
    ) CHARACTER SET 'utf8'"""
    cursor.execute(stmt_create)

    cursor.execute ("""
        INSERT INTO urls (str)
        VALUES
        ('reptile'),
        ('amphibian'),
        ('fish'),
        ('mammal')
        """)
    print("Number of rows inserted: %d" % cursor.rowcount)
    db.close()
if __name__ == '__main__':
    import config
    config = config.Config.dbinfo().copy()
    main(config)

OUTPUT:

Number of rows inserted: 4

I orientate my code strictly on what was given to me in the examples and can't, for the life of mine, figure out what the problem is. What am I doing wrong here?

Fetching table data with the script works just fine so I am not worried about the configuration files. I'm root on the database so rights shouldn't be a problem either.

Answer 1

You need to add a db.commit() to commit your changes before you db.close()!