Ekspor database mysql ke excel menggunakan python

Katakanlah Anda ingin mengekspor tabel berikut (disebut 'dbo. product‘ table) dari SQL Server ke CSV menggunakan Python

Berikut adalah langkah-langkah yang dapat Anda ikuti

Langkah-langkah untuk Mengekspor Tabel SQL Server ke CSV menggunakan Python

Langkah 1. Instal Paket Pyodbc

Jika Anda belum melakukannya, instal paket pyodbc menggunakan perintah di bawah ini (pada Windows)

pip install pyodbc

Anda dapat memeriksa panduan berikut untuk instruksi menginstal paket dengan Python menggunakan pip

Langkah 2. Hubungkan Python ke SQL Server

Ada beberapa item yang dapat Anda ambil sebelum menghubungkan Python ke SQL Server, termasuk

• Nama server
• Nama basis data

Sebagai contoh, misalkan kita diberi informasi di bawah ini

• Nama servernya adalah. RON\SQLEXPRESS
• Nama databasenya adalah. test_database

Oleh karena itu, kode untuk menghubungkan Python ke SQL Server akan terlihat seperti berikut (perhatikan bahwa Anda perlu menyesuaikan kode untuk mencerminkan informasi server dan basis data Anda)

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=test_database;'
                      'Trusted_Connection=yes;')

Anda dapat meninjau panduan berikut untuk langkah-langkah lengkap untuk menghubungkan Python ke SQL Server

Langkah 3. Ekspor Tabel SQL Server ke CSV menggunakan Python

Untuk langkah terakhir, Anda dapat menggunakan paket Pandas untuk mengekspor tabel dari SQL Server ke CSV

Kamu akan membutuhkan

• Untuk menginstal paket Pandas jika Anda belum melakukannya. Anda dapat menginstal paket Pandas menggunakan perintah ini. pip instal panda
• Kueri untuk mendapatkan hasil yang akan diekspor. Sebagai contoh kita, kuerinya adalah. pilih * dari test_database. dbo. produk
• Jalur tempat file CSV akan disimpan. Sebagai contoh kita, jalannya adalah. C. \Users\Ron\Desktop\exported_data. csv

Setelah Anda mengambil informasi di atas, Anda harus menambahkan sintaks berikut ke dalam kode

import pandas as pd
sql_query = pd.read_sql_query('''
                              select * from test_database.dbo.product
                              '''
                              ,conn) # here, the 'conn' is the variable that contains your database connection information from step 2

df = pd.DataFrame(sql_query)
df.to_csv (r'C:\Users\Ron\Desktop\exported_data.csv', index = False) # place 'r' before the path name

Menyatukan semua komponen

import pandas as pd
import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=test_database;'
                      'Trusted_Connection=yes;')

sql_query = pd.read_sql_query(''' 
                              select * from test_database.dbo.product
                              '''
                              ,conn) # here, the 'conn' is the variable that contains your database connection information from step 2

df = pd.DataFrame(sql_query)
df.to_csv (r'C:\Users\Ron\Desktop\exported_data.csv', index = False) # place 'r' before the path name

Jalankan kode dengan Python (disesuaikan dengan informasi dan jalur koneksi database Anda), dan file CSV Anda akan diekspor ke lokasi yang Anda tentukan

Setelah Anda membuka file, Anda akan melihat data ini

Anda mungkin juga ingin memeriksa panduan berikut untuk langkah-langkah mengimpor file CSV ke SQL Server menggunakan Python

Di sini saya akan menunjukkan kepada Anda cara membuat laporan excel dari database MySQL menggunakan Python Flask. Kami akan membuat aplikasi web menggunakan kerangka web ringan yang disebut Flask. Kami akan membuat file excel dengan cepat dari database MySQL di aplikasi web ini. Tautan unduhan akan disediakan di front-end atau UI tempat pengguna akhir akan mengklik dan akan mendapatkan opsi untuk menyimpan file excel yang dihasilkan sesuai lokasi yang mereka pilih

Kita akan menggunakan xlwt API untuk membuat laporan excel. Karena kami tidak menyimpan laporan excel ini di mana pun di lokasi fisik server, maka kami perlu menulis data sebagai byte. Oleh karena itu, kami akan menggunakan paket io_ di sini dari Python 3

Mengapa Pelaporan Excel?

• Sebagian besar orang dalam organisasi cenderung akrab dengan excel, sehingga memastikan kurva pembelajaran singkat (atau tidak ada) selama implementasi
• Ini memungkinkan Anda menggunakan templat dan rumus untuk mengumpulkan data
• Ini memiliki fungsionalitas bagan yang sangat mudah digunakan dibandingkan dengan perangkat lunak lain, dan beberapa fitur bawaan yang bermanfaat
• Ini juga bagus karena Anda memiliki kemampuan untuk memotong dan menempel ke dalam laporan. Kebanyakan orang menulis laporan di Word, jadi mudah untuk memotong dan menempelkan data atau bagan dari Excel ke Word

Prasyarat

Piton 3. 8. 0, Labu 1. 1. 1, xlwt1. 3. 0 (pemasangan pip xlwt)

Membuat Tabel MySQL

Karena kita akan membuat laporan CSV dari database MySQL, maka kita memerlukan tabel dan data ke dalam tabel ini untuk dapat menghasilkan laporan dari tabel

Kami akan membuat tabel karyawan dan membuang beberapa data sampel ke dalamnya

CREATE TABLE IF NOT EXISTS `employee` (
  `emp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_first_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `emp_last_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `emp_mgr_id` int(11) DEFAULT NULL,
  `emp_designation` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7975 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `employee` (`emp_id`, `emp_first_name`, `emp_last_name`, `emp_mgr_id`, `emp_designation`) VALUES
	(7369, 'SMITH', 'JHON', 7902, 'CLERK'),
	(7499, 'ALLEN', 'BORDER', 7698, 'SALESMAN'),
	(7521, 'WARD', 'SPACE', 7698, 'SALESMAN'),
	(7654, 'MARTIN', 'FOWLER', 7698, 'SALESMAN'),
	(7698, 'BLAKE', 'RAY', NULL, 'MANAGER'),
	(7782, 'CLARK', 'MICHAEL', NULL, 'MANAGER'),
	(7788, 'SCOTT', 'TIGER', 7566, 'ANALYST'),
	(7839, 'KING', 'ROY', NULL, 'VICE PRESIDENT'),
	(7844, 'TURNER', 'RICK', 7698, 'SALESMAN'),
	(7876, 'ADAMS', 'EVE', 7788, 'CLERK'),
	(7900, 'JAMES', 'BOND', 7698, 'CLERK'),
	(7902, 'FORD', 'LAMBDA', 7566, 'ANALYST'),
	(7934, 'MILLER', 'JOHN', 7782, 'CLERK'),
	(7954, 'FRANK', 'JOHN', 7782, 'MANAGER'),
	(7964, 'MARTIN', 'HIKMAN', NULL, 'CLERK'),
	(7974, 'APRIL', 'HICKMAN', 7782, 'SALESMAN');

Membuat Direktori Proyek

Buat direktori root project bernama python-flask-mysql-excel-report sesuai lokasi yang Anda pilih

Pos terkait

• Hasilkan Laporan CSV dari MySQL menggunakan Python Flask
• Baca Excel menggunakan Python
• Tulis Excel menggunakan Python

Kami mungkin tidak menyebutkan nama direktori root proyek di bagian selanjutnya, tetapi kami akan berasumsi bahwa kami membuat file sehubungan dengan direktori root proyek

Konfigurasi Labu

Kami di sini mengonfigurasi aplikasi melalui kerangka labu. Buat file bernama aplikasi. py dengan kode di bawah ini

from flask import Flask

app = Flask(__name__)

Konfigurasi Basis Data

Kami membuat db di bawah ini. skrip py Python untuk menyiapkan konfigurasi database MySQL untuk menghubungkan ke database dan menyimpan informasi pengguna ke dalam tabel karyawan di bawah database roytuts

Kita perlu mengonfigurasi koneksi database dengan modul flask dan itulah sebabnya kita telah mengimpor modul aplikasi dan menyiapkan konfigurasi MySQL dengan modul flask

Pastikan untuk mengubah nilai konfigurasi database sesuai dengan pengaturan database Anda

from app import app
from flaskext.mysql import MySQL

mysql = MySQL()
 
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'roytuts'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

Mengonfigurasi URL dan Mengambil Catatan

Sekarang kita akan mengonfigurasi URL untuk merender file template untuk front-end atau UI yang akan ditampilkan ke pengguna akhir

Kami juga akan mengambil data dari tabel database MySQL karyawan dan menulis ke file excel yang dapat diunduh

Buat skrip Python bernama main. py dengan kode sumber berikut

import io
import xlwt
import pymysql
from app import app
from db import mysql
from flask import Flask, Response, render_template

@app.route('/')
def upload_form():
	return render_template('download.html')

@app.route('/download/report/excel')
def download_report():
	conn = None
	cursor = None
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		
		cursor.execute("SELECT emp_id, emp_first_name, emp_last_name, emp_designation FROM employee")
		result = cursor.fetchall()
		
		#output in bytes
		output = io.BytesIO()
		#create WorkBook object
		workbook = xlwt.Workbook()
		#add a sheet
		sh = workbook.add_sheet('Employee Report')
		
		#add headers
		sh.write(0, 0, 'Emp Id')
		sh.write(0, 1, 'Emp First Name')
		sh.write(0, 2, 'Emp Last Name')
		sh.write(0, 3, 'Designation')
		
		idx = 0
		for row in result:
			sh.write(idx+1, 0, str(row['emp_id']))
			sh.write(idx+1, 1, row['emp_first_name'])
			sh.write(idx+1, 2, row['emp_last_name'])
			sh.write(idx+1, 3, row['emp_designation'])
			idx += 1
		
		workbook.save(output)
		output.seek(0)
		
		return Response(output, mimetype="application/ms-excel", headers={"Content-Disposition":"attachment;filename=employee_report.xls"})
	except Exception as e:
		print(e)
	finally:
		cursor.close() 
		conn.close()

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

Dalam skrip Python di atas, kami mengambil data dari database MySQL

Kami membuat objek Excel WorkBook. Kami menambahkan lembar yang disebut "Laporan Karyawan" ke dalamnya. Kami menambahkan tajuk ke lembar. Kami mengulangi baris data yang diambil dan menulis ke sheet satu per satu

Kami menyimpan data ke dalam array byte. Kita perlu seek(0)_ untuk mengatur posisi file saat ini di awal

Akhirnya kami mengirimkan data sebagai lampiran dalam tanggapan sehingga pengguna dapat mengunduhnya dalam file bernama employee_report. xls

Berkas Templat

Template atau file tampilan disimpan ke dalam direktori template. templates adalah direktori standar dalam framework Flask, tempat Anda harus meletakkan file tampilan atau template

<!doctype html>
<title>Python Flask File Generate Excel Report from MySQL</title>
<h2>Generate Excel Report from MySQL</h2>

<p>
	<a href="{{ url_for('.download_report') }}">Generate Excel Report</a>
</p>

Halaman tampilan ini ditampilkan dengan tautan dan mengklik tautan ini akan menghasilkan file excel dan meminta pengguna untuk menyimpan file tersebut

Menguji Aplikasi

Sekarang buka direktori root project dan jalankan perintah python main.py atau cukup main.py, server Anda akan dimulai pada port default 5000

Jika Anda ingin mengubah port maka Anda dapat mengubah baris 

from flask import Flask

app = Flask(__name__)

from flask import Flask

app = Flask(__name__)

Bagaimana cara mengekspor data dari SQLite ke Excel dengan Python?

Bagaimana cara mengekspor data dari Python ke Excel?

Bagaimana cara mengekspor output MySQL ke Excel?

Bagaimana cara mengekspor database dengan Python?