Hướng dẫn how read data from sqlite3 in python? - làm thế nào để đọc dữ liệu từ sqlite3 trong python?

Tổng quan

Giảng dạy: 20 phút Bài tập: 25 phút 20 min
Exercises: 25 min

Nội dung chính Show

• Tổng quan
• Python và SQL
• Mô -đun sqlite3
• Truy cập dữ liệu được lưu trữ trong SQLite bằng Python và Pandas
• Lưu trữ dữ liệu: CSV vs SQLite
• Thử thách - SQL
• Lưu trữ dữ liệu: Tạo các bảng mới bằng gấu trúc
• Thử thách - Tiết kiệm công việc của bạn
• Những điểm chính
• Làm thế nào tìm nạp dữ liệu từ sqlite3 trong python?
• Làm thế nào để Python kết nối với cơ sở dữ liệu SQLite3?
• Làm cách nào để xem một bảng trong sqlite3 trong python?
• Làm cách nào để chọn dữ liệu cụ thể trong sqlite?

Câu hỏi

• Điều gì sẽ xảy ra nếu dữ liệu của tôi được lưu trữ trong cơ sở dữ liệu SQL? Tôi có thể quản lý chúng với Python không?

• Làm thế nào tôi có thể viết dữ liệu từ Python để được sử dụng với SQL?

Mục tiêu

• Sử dụng mô -đun SQLite3 để tương tác với cơ sở dữ liệu SQL.

• Truy cập dữ liệu được lưu trữ trong SQLite bằng Python.

• Mô tả sự khác biệt trong tương tác với dữ liệu được lưu trữ dưới dạng tệp CSV so với SQLite.

• Mô tả lợi ích của việc truy cập dữ liệu bằng cơ sở dữ liệu so với tệp CSV.

Python và SQL

Khi bạn mở CSV trong Python và gán nó cho một tên biến, bạn đang sử dụng bộ nhớ máy tính của mình để lưu biến đó. Truy cập dữ liệu từ cơ sở dữ liệu như SQL không chỉ hiệu quả hơn mà còn cho phép bạn tập hợp và chỉ nhập các phần của dữ liệu bạn cần.

Trong bài học sau, chúng tôi sẽ thấy một số cách tiếp cận có thể được thực hiện để làm như vậy.

Mô -đun sqlite3

Mô -đun SQLite3 cung cấp giao diện đơn giản để tương tác với cơ sở dữ liệu SQLite. Một đối tượng kết nối được tạo bằng cách sử dụng sqlite3.connect(); Kết nối phải được đóng vào cuối phiên với lệnh .close(). Mặc dù kết nối được mở, bất kỳ tương tác nào với cơ sở dữ liệu đều yêu cầu bạn tạo đối tượng con trỏ bằng lệnh .cursor(). Con trỏ sau đó đã sẵn sàng để thực hiện tất cả các loại hoạt động với .execute().

import sqlite3

# Create a SQL connection to our SQLite database
con = sqlite3.connect("data/portal_mammals.sqlite")

cur = con.cursor()

# The result of a "cursor.execute" can be iterated over by row
for row in cur.execute('SELECT * FROM species;'):
    print(row)

# Be sure to close the connection
con.close()

Truy vấn

Một trong những cách phổ biến nhất để tương tác với cơ sở dữ liệu là truy vấn: truy xuất dữ liệu dựa trên một số tham số tìm kiếm. Sử dụng chuỗi câu lệnh Chọn. Truy vấn được trả về dưới dạng một tuple hoặc một bộ dữ liệu. Thêm một câu lệnh để lọc kết quả của bạn dựa trên một số tham số.

import sqlite3

# Create a SQL connection to our SQLite database
con = sqlite3.connect("data/portal_mammals.sqlite")

cur = con.cursor()

# Return all results of query
cur.execute('SELECT plot_id FROM plots WHERE plot_type="Control"')
cur.fetchall()

# Return first result of query
cur.execute('SELECT species FROM species WHERE taxa="Bird"')
cur.fetchone()

# Be sure to close the connection
con.close()

Truy cập dữ liệu được lưu trữ trong SQLite bằng Python và Pandas

Sử dụng gấu trúc, chúng tôi có thể nhập kết quả của truy vấn SQLite vào DataFrame. Lưu ý rằng bạn có thể sử dụng cùng các lệnh / cú pháp SQL mà chúng tôi đã sử dụng trong bài học SQLite. Một ví dụ về việc sử dụng gấu trúc cùng với SQLite dưới đây:

import pandas as pd
import sqlite3

# Read sqlite query results into a pandas DataFrame
con = sqlite3.connect("data/portal_mammals.sqlite")
df = pd.read_sql_query("SELECT * from surveys", con)

# Verify that result of SQL query is stored in the dataframe
print(df.head())

con.close()

Lưu trữ dữ liệu: CSV vs SQLite

Lưu trữ dữ liệu của bạn trong cơ sở dữ liệu SQLite có thể cung cấp các cải tiến hiệu suất đáng kể khi đọc/viết so với CSV. Sự khác biệt về hiệu suất trở nên đáng chú ý hơn khi kích thước của bộ dữ liệu tăng lên (xem ví dụ các điểm chuẩn này).

Thử thách - SQL

1. Tạo một truy vấn có chứa dữ liệu khảo sát được thu thập từ năm 1998 - 2001 để quan sát giới tính nam nam hay nữ, bao gồm chi quan sát và loài và loại trang web cho mẫu. Có bao nhiêu hồ sơ được trả lại?

2. Tạo một khung dữ liệu chứa tổng số quan sát (đếm) được thực hiện cho tất cả các năm và tổng trọng số quan sát cho mỗi trang web, được đặt hàng bởi ID trang web.

Lưu trữ dữ liệu: Tạo các bảng mới bằng gấu trúc

Chúng tôi cũng có thể chúng tôi gấu trúc để tạo các bảng mới trong cơ sở dữ liệu SQLite. Ở đây, chúng tôi chạy chúng tôi làm lại một bài tập mà chúng tôi đã làm trước đây với các tệp CSV bằng cơ sở dữ liệu SQLite của chúng tôi. Trước tiên chúng tôi đọc trong dữ liệu khảo sát của mình, sau đó chỉ chọn những kết quả khảo sát cho năm 2002, và sau đó lưu nó vào bảng riêng của nó để chúng tôi có thể tự mình làm việc với nó sau này.

import pandas as pd
import sqlite3

con = sqlite3.connect("data/portal_mammals.sqlite")

# Load the data into a DataFrame
surveys_df = pd.read_sql_query("SELECT * from surveys", con)

# Select only data for 2002
surveys2002 = surveys_df[surveys_df.year == 2002]

# Write the new DataFrame to a new SQLite table
surveys2002.to_sql("surveys2002", con, if_exists="replace")

con.close()

Thử thách - Tiết kiệm công việc của bạn

1. Đối với mỗi thách thức trong khối thử thách trước đó, hãy sửa đổi mã của bạn để lưu kết quả vào các bảng của riêng họ trong cơ sở dữ liệu cổng thông tin.

2. Một số lý do bạn có thể muốn lưu kết quả của các truy vấn của bạn trở lại cơ sở dữ liệu là gì? Một số lý do bạn có thể tránh làm điều này là gì.

Những điểm chính

• SQLite3 cung cấp giao diện giống SQL để đọc, truy vấn và viết cơ sở dữ liệu SQL từ Python.

• SQLITE3 có thể được sử dụng với gấu trúc để đọc dữ liệu SQL cho khung dữ liệu pandas quen thuộc.

• Pandas và SQLite3 cũng có thể được sử dụng để chuyển giữa các định dạng CSV và SQL.