DEV Community

Cover image for IMPORTING DATA USING PYTHON TO MYSQL
allan-pg
allan-pg

Posted on

1

IMPORTING DATA USING PYTHON TO MYSQL

Introduction

Importing data manually into your database especially when its a number of tables, can not only be tiresome but also time consuming. This can be made easier by use of python libraries.

Download painting dataset from kaggle. Paintings data set is made up of 8 csv files that we will import to our database by use of a simple python script, instead of importing data to our database table manually.

Steps to import data

  • Create database in PG-admin and call it painting
create database painting
Enter fullscreen mode Exit fullscreen mode
  • Open jupyter notebook and install python libraries
pip install sqlalchemy
pip install pandas
Enter fullscreen mode Exit fullscreen mode
  • Import Python libraries
import pandas as pd
from sqlalchemy import create_engine
Enter fullscreen mode Exit fullscreen mode
  • Create a connection to your pg-admin database
conn_string = 'postgresql://postgres:1344@localhost/painting'
db = create_engine(conn_string) 
conn = db.connect()
Enter fullscreen mode Exit fullscreen mode

In conn_string stores url to our database where postgresql is our database since we created database in pg-admin, postgres is default name of our database name pg-admin, 1344 is password to our pg-admin, @localhost is host name and painting is our database name

  • Load files to your database
files = ['artist', 'canvas_size', 'image_link', 'museum', 'museum_hours', 'product_size', 'subject', 'work']

for file in files:

    df = pd.read_csv(fr"C:\Users\Admin\Desktop\famous painti\{file}.csv")
    df.to_sql(file, con = conn, if_exists='replace', index = False)
Enter fullscreen mode Exit fullscreen mode

files is the names we wish to name our tables in painting database. for loop is used so that we can iterate over our files. fr in pd.read csv represents f string and raw data. Index must be set to false to not import default pandas index.

Conclusion

We have learned how to use Python and MySQL Connector to create an entirely new database in MySQL Server, create tables within that database instead of doing it manually.

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more