Python xuất bảng mysql sang excel

Trong phần đầu về xuất tập dữ liệu lớn, chúng ta sẽ thấy một số cách thực hiện và bây giờ là lúc để xem cách thực hiện với Python

Kết nối với MySQL từ Python

Trước khi tiếp tục, bạn có thể đọc tại đây cách kết nối với MySQL từ Python, nơi bạn cũng sử dụng một lớp mà tôi dùng để kết nối vì lớp này làm những gì tôi thích. Nhưng với lối vào này, mình sẽ kết nối trực tiếp với MySQL bằng thư viện PyMySQL

Bạn chỉ cần nhập thư viện, thiết lập thông tin chính xác cho kết nối và… là xong

import pymysql

conn = pymysql.connect[host='localhost', port=3306, user='root', passwd='pass', db='']
print ['Conected!']
conn.close[]

Để đếm thời gian, tôi đã tạo lớp học của riêng mình

import time
class Cronometro[]:
def __init__[self, tarea = '']:
self.__inicio = time.time[]
self.tarea = tarea
def iniciar[self]:
# Tiempo de inicio de ejecución.
self.__inicio = time.time[]

def finalizar[self]:
# Devolvemos el tiempo de ejecución.
tmp = time.time[]
tmp2 = self.__inicio
self.__inicio = tmp
x = self.tarea + ' --> ' if len[self.tarea] > 0 else ''

return x + str[round[tmp - tmp2,2]] + " segundos"

Phải làm gì khi truy xuất hàng nghìn và hàng nghìn hàng

Máy khách MySQL, chẳng hạn như PyMySQL, hoạt động rất tốt để kết nối với cơ sở dữ liệu. sau khi thực hiện một câu truy vấn, kết quả được lưu vào bộ nhớ, trước khi trao lại quyền điều khiển cho chương trình [sau khi công việc cơ sở dữ liệu kết thúc]. Nhưng khi bộ đệm quá lớn, hiệu suất sẽ giảm xuống. Tại đây, bạn có thể đọc sự cố và giải pháp bằng cách sử dụng “bộ kết quả phát trực tuyến“, với việc sử dụng các con trỏ chẳng hạn như SSCursor

Trong PyMySQL, bạn có thể truy cập vào các con trỏ như thế này

cur = conn.cursor[pymysql.cursors.SSCursor]

Tiếp theo, điều rất quan trọng là tìm nạp từng hàng [fetchone] thay vì tất cả cùng một lúc [fetchall]

Quy trình xuất dữ liệu sang CSV

Nếu bạn muốn xuất bản ghi sang CSV, thì [tôi nghĩ] bắt buộc phải sử dụng gói CSV. Hướng dẫn tốt nhất bạn có thể làm theo để sử dụng nó là. http. //www. trăn cho người mới bắt đầu. com/lập trình hệ thống/sử dụng-the-csv-module-in-python/. Đối với nhu cầu của tôi, tôi chỉ cần mở tệp để viết, liên kết tệp đó với gói csv, sử dụng csv. writerow để viết và sau khi hoàn thành, hãy đóng tệp

ofile = open[file_name,'wb']
csv_writer = csv.writer[ofile, delimiter=delimiter, quotechar=quotechar,quoting=quoting]
csv_writer.writerow[datos]
ofile.close[]

Trình ghi CSV có một số tùy chọn, vì vậy hãy nhớ đặt tùy chọn bạn cần

Trước khi tiếp tục, sẽ rất hữu ích nếu bạn đọc câu hỏi này trên StackOverflow về cách lưu trữ tập bản ghi [trong trường hợp này là sử dụng SQLite] dưới dạng tệp CSV và bạn có manh mối chính xác cho câu trả lời đầu tiên

Bây giờ, chúng ta đã có quy trình sẵn sàng và mã là thế này

def write_sql_to_file[file_name, sql, with_header=True, delimiter=',',quotechar='"',quoting=csv.QUOTE_NONNUMERIC, con_sscursor=False]:
    cur = conn.cursor[pymysql.cursors.SSCursor] if con_sscursor else conn.cursor[]
    cur.execute[sql]
    cabecera= [campo[0] for campo in cur.description]
    ofile = open[file_name,'wb']
    csv_writer = csv.writer[ofile, delimiter=delimiter, quotechar=quotechar,quoting=quoting]
    if with_header:
        csv_writer.writerow[cabecera]
    if con_sscursor:
         while True:
            x = cur.fetchone[]
            if x:
                csv_writer.writerow[x]
            else:
                break
    else:
        for x in cur.fetchall[]:
            csv_writer.writerow[x]
    cur.close[]
    ofile.close[]

Tôi đã chuẩn bị một truy vấn để xuất một lượng lớn dữ liệu, vì vậy, hãy xem thời gian sử dụng SSCursor và không có SScursor. Tôi đã chèn đồng hồ bấm giờ vào hai điểm để xem hiệu suất. một dành cho thời gian truy vấn và một dành cho việc tạo tệp CSV

Kết quả, đối với cùng một truy vấn, nhưng tăng lượng dữ liệu cần xuất là

Bài kiểm tra 1 Bài kiểm tra 2 Bài kiểm tra 3

Kết luận, lấy tập bản ghi bằng SSCursor nhanh hơn so với việc không sử dụng nó, nhưng tệp CSV được ghi nhanh hơn bằng cách tìm nạp thay vì tìm nạp từng cái một. Tôi cần tiếp tục điều tra về vấn đề này

Giả sử bạn muốn xuất bảng sau [được gọi là 'dbo. product‘ table] từ SQL Server sang CSV bằng Python

product_idproduct_nameprice1Máy ​​tính8002TV12003Máy in1504Bàn4005Ghế1206Máy tính bảng300

Dưới đây là các bước mà bạn có thể làm theo

Các bước để xuất Bảng máy chủ SQL sang CSV bằng Python

Bước 1. Cài đặt gói Pyodbc

Nếu bạn chưa làm như vậy, hãy cài đặt gói pyodbc bằng lệnh bên dưới [trong Windows]

pip install pyodbc

Bạn có thể kiểm tra hướng dẫn sau để biết hướng dẫn cài đặt gói trong Python bằng cách sử dụng pip

Bước 2. Kết nối Python với máy chủ SQL

Có một số mục mà bạn có thể truy xuất trước khi kết nối Python với SQL Server, bao gồm

• Tên máy chủ
• Tên cơ sở dữ liệu

Ví dụ: giả sử rằng chúng tôi được cung cấp thông tin bên dưới

• Tên máy chủ là. RON\SQLEXPRESS
• Tên cơ sở dữ liệu là. test_database

Do đó, mã để kết nối Python với SQL Server sẽ như sau [lưu ý rằng bạn sẽ cần điều chỉnh mã để phản ánh thông tin cơ sở dữ liệu và máy chủ của mình]

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

Bạn có thể xem lại hướng dẫn sau để biết các bước hoàn chỉnh để kết nối Python với SQL Server

Bước 3. Xuất bảng máy chủ SQL sang CSV bằng Python

Đối với bước cuối cùng, bạn có thể sử dụng gói Pandas để xuất bảng từ SQL Server sang CSV

Có thể bạn sẽ cần

• Để cài đặt gói Pandas nếu bạn chưa cài đặt. Bạn có thể cài đặt gói Pandas bằng lệnh này. pip cài đặt gấu trúc
• Truy vấn để lấy kết quả được xuất. Ví dụ của chúng tôi, truy vấn là. chọn * từ test_database. dbo. sản phẩm
• Đường dẫn nơi tệp CSV sẽ được lưu. Ví dụ của chúng tôi, đường dẫn là. C. \Users\Ron\Desktop\exported_data. csv

Khi bạn đã truy xuất thông tin trên, bạn sẽ cần thêm cú pháp sau vào mã

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

Đặt tất cả các thành phần lại với nhau

________số 8

Chạy mã bằng Python [được điều chỉnh theo đường dẫn và thông tin kết nối cơ sở dữ liệu của bạn] và tệp CSV của bạn sẽ được xuất sang vị trí đã chỉ định

Khi bạn mở tệp, bạn sẽ thấy dữ liệu này

product_idproduct_nameprice1Máy ​​tính8002TV12003Máy in1504Bàn4005Ghế1206Máy tính bảng300

Bạn cũng có thể muốn xem hướng dẫn sau để biết các bước nhập tệp CSV vào SQL Server bằng Python