Cara menggunakan sqlite to csv python

Python is perfect language for this task because it has great libraries for sqlite and CSV DataFrames.

Creating a sqlite database

sqlite is a lightweight database that can be started as an empty text file. You can create the file with

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

from pathlib import Path
Path('my_data.db').touch()

A zero byte text file is a great starting point for a lightweight database!

Creating sqlite table

Create a database connection and cursor to execute queries.

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

Execute a query that’ll create a

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

c.execute('''CREATE TABLE users (user_id int, username text)''')

Load CSV file into sqlite table

Suppose you have the following

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

user_id,username
1,pokerkid
2,crazyken

Pandas makes it easy to load this CSV data into a sqlite table:

import pandas as pd

# load the data into a Pandas DataFrame
users = pd.read_csv('users.csv')
# write the data to a sqlite table
users.to_sql('users', conn, if_exists='append', index = False)

The

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

Fetch values from sqlite table

Fetch all the rows from the

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

c.execute('''SELECT * FROM users''').fetchall() # [(1, 'pokerkid'), (2, 'crazyken')]

The

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

c.execute('''CREATE TABLE users (user_id int, username text)''')

Load another CSV into the databases

Suppose you have the following

c.execute('''CREATE TABLE users (user_id int, username text)''')

order_id,user_id,item_name
1,1,speaker
2,1,phone
3,2,spoon
4,2,fork
5,2,speaker

Create a table and then load the orders data into the database.

c.execute('''CREATE TABLE orders (order_id int, user_id int, item_name text)''')
orders = pd.read_csv('orders.csv') # load to DataFrame
orders.to_sql('orders', conn, if_exists='append', index = False) # write to sqlite table

Fetch results of database join

Join the

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

c.execute('''CREATE TABLE users (user_id int, username text)''')

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

c.execute('''SELECT * FROM users u LEFT JOIN orders o ON u.user_id = o.user_id''')
c.fetchall()

Here’s the array that’s returned:

[(1, 'pokerkid', 1, 1, 'speaker'),
 (1, 'pokerkid', 2, 1, 'phone'),
 (2, 'crazyken', 3, 2, 'spoon'),
 (2, 'crazyken', 4, 2, 'fork'),
 (2, 'crazyken', 5, 2, 'speaker')]

You can also read the SQL query directly into a Pandas DataFrame.

import sqlite3

conn = sqlite3.connect('my_data.db')
c = conn.cursor()

Next steps

Python’s build in sqlite library coupled with Pandas DataFrames makes it easy to load CSV data into sqlite databases.

sqlite databases are great for local experimentation and are used extensively on mobile phones. It’s a great database when you’d like relational database query functionality without the overhead of Postgres.

I was helping a friend through how to solve problems by dissecting them into small tiny problems and solve them one-by-one, So here is the code assignment as the main problem we are trying to solve.

The Problem:
Use Python to upload and import CSV file into a pre-defined table in MySQL database.

Steps

1. Create file upload form
2. Upload the CSV using Flask
3. Parse CSV file data
4. Connect to the database
5. Insert rows into a specific table in the database

Stack we will use:

1. Flask;
   A Python Minimal (Micro) web frameworks.
   
2. MySQL;
   If you use Windows you can use WAMP or XAMPP, if you are using Linux XAMPP is a good choice. If you are using macOS, XAMPP or MAMP are good solutions.
   
3. Pandas;
   Pandas is an open-source python library for data analytics and manipulation. It's widely used among data scientists and data engineers.
   
4. mysql.connector;
   The library that we will use to connect to the MySQL database and insert the data.

Requirements

• Install Flask;
  Here I will install Python directly with

  pip install pandas
  pip install sql.connector

  2, but I strongly recommend using

  pip install pandas
  pip install sql.connector

  3 for best practice.
  

pip install Flask

• Install XAMPP;
  The XAMPP package will include MySQL database and PHPMyAdmin to manage our MySQL databases.
  
  
• Install the required libraries (Pandas, sql.connector)
  

pip install pandas
pip install sql.connector

• A sample CSV file, I will be using

  pip install pandas
  pip install sql.connector

  4 from here.

0 Get Flask ready

Now let's test if Flask works by starting to create our main file

pip install pandas
pip install sql.connector

from flask import Flask

app = Flask(__name__)

@app.route("/")
def hello():
     return "Flask CSV Filer Uploader and Parser"

if (__name__ == "__main__"):
     app.run(port = 5000)

We can run the file by

pip install pandas
pip install sql.connector

pip install pandas
pip install sql.connector

If everything goes as planned, you will get the rendered message.

Next step is to create the HTML page which will be responsible for uploading the CSV files.

1- Create file upload form

Let's create

pip install pandas
pip install sql.connector

<!doctype html>
<html>
  <head>
    <title>FLASK CSV File Upload</title>
  </head>
  <body>
    <h1>Upload your CSV file</h1>
    <form method="POST" action="" enctype="multipart/form-data">
      <p><input type="file" name="file"></p>
      <p><input type="submit" value="Submit"></p>
    </form>
  </body>
</html>

2- Upload the files to the server

Now we need to accept the incoming files from this form, let's add the reasonable function in

pip install pandas
pip install sql.connector

from flask import Flask, render_template, request, redirect, url_for
import os
from os.path import join, dirname, realpath

app = Flask(__name__)

# enable debugging mode
app.config["DEBUG"] = True

# Upload folder
UPLOAD_FOLDER = 'static/files'
app.config['UPLOAD_FOLDER'] =  UPLOAD_FOLDER


# Root URL
@app.route('/')
def index():
     # Set The upload HTML template '\templates\index.html'
    return render_template('index.html')


# Get the uploaded files
@app.route("/", methods=['POST'])
def uploadFiles():
      # get the uploaded file
      uploaded_file = request.files['file']
      if uploaded_file.filename != '':
           file_path = os.path.join(app.config['UPLOAD_FOLDER'], uploaded_file.filename)
          # set the file path
           uploaded_file.save(file_path)
          # save the file
      return redirect(url_for('index'))

if (__name__ == "__main__"):
     app.run(port = 5000)

As everything is set, we can test the file upload now at

from flask import Flask

app = Flask(__name__)

@app.route("/")
def hello():
     return "Flask CSV Filer Uploader and Parser"

if (__name__ == "__main__"):
     app.run(port = 5000)

from flask import Flask

app = Flask(__name__)

@app.route("/")
def hello():
     return "Flask CSV Filer Uploader and Parser"

if (__name__ == "__main__"):
     app.run(port = 5000)

3- Parse CSV file

To parse the CSV file data into rows, we will use

from flask import Flask

app = Flask(__name__)

@app.route("/")
def hello():
     return "Flask CSV Filer Uploader and Parser"

if (__name__ == "__main__"):
     app.run(port = 5000)