How to import the famous quotes CSV file to MySQL using Python

This tutorial will be similar to the SQL file importing presented here.

You'll have to install the mysql-connector package using pip, open a new connection to the running MySQL server and import the data line by line from the CSV file.

Install the mysql-connector package with pip, this will let you define a connection to MySQL and use a cursor to import the data:

pip install mysql-connector-python

Also, we will be using the DictReader because is easier to parse the CSV file and manipulate the data.

import mysql.connector
import csv

# define a connection to your database
# where USER, PASSWORD and DATABASE are your values

connection = mysql.connector.connect(
    user='USER', 
    password='PASSWORD',
    host='127.0.0.1', 
    database='DATABASE'
)

cursor = connection.cursor()

# you can create the database like below or from the MySQL client
# watch out to not execute it twice
cursor.execute(
    '''
    DROP TABLE IF EXISTS `famous_quotes`;
    CREATE TABLE `famous_quotes` (
        `primary_key` BIGINT NOT NULL AUTO_INCREMENT,
        `author` LONGTEXT,
        `categories` LONGTEXT,
        `quote` LONGTEXT,
        PRIMARY KEY (`primary_key`)
    ) CHARSET=utf8;
    '''
)

with open('famous_quotes.csv') as csvfile:
    reader = csv.DictReader(csvfile)

    # csv row example
    # {'author': 'A. J. McLean', 'categories': 'Life , Love , Music', 'quote': 'Music is love, love is music,'}
    for row in reader:
        cursor.execute(
            '''
            INSERT INTO `famous_quotes` (`author`, `categories`, `quote`)
            VALUES
            ('{author}', '{categories}', '{quote}');
            '''.format(author=row['author'], categories=row['categories'], quote=row['quote'])
        )

connection.commit()
connection.close()