Python ghi danh sách vào SQLite

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 con và chỉ nhập các phần dữ liệu mà bạn cần

Trong bài học sau, chúng ta sẽ xem một số phương pháp 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[]; . Trong khi kết nối đang 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 một đối tượng con trỏ bằng lệnh .cursor[]. Sau đó, con trỏ đã sẵn sàng để thực hiện tất cả các loại thao tác 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 SELECT. Truy vấn được trả về dưới dạng một bộ hoặc một bộ gồm nhiều bộ. Thêm câu lệnh WHERE để 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 khung dữ liệu. Lưu ý rằng bạn có thể sử dụng các lệnh/cú pháp SQL giống như chúng ta đã sử dụng trong bài học SQLite. Dưới đây là một ví dụ về việc sử dụng gấu trúc cùng với sqlite

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 so với SQLite

Lưu trữ dữ liệu của bạn trong cơ sở dữ liệu SQLite có thể cải thiện hiệu suất đáng kể khi đọc/ghi 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 tập dữ liệu tăng lên [xem ví dụ]

Thách thức - SQL

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

2. Tạo một khung dữ liệu chứa tổng số quan sát [số lượng] được thực hiện trong tất cả các năm và tổng trọng số quan sát cho từng trang web, được sắp xếp theo ID trang web

Lưu trữ dữ liệu. Tạo bảng mới bằng Pandas

Chúng tôi cũng có thể sử dụng 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 thực hiện 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 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, sau đó lưu dữ liệu đó vào bảng riêng để chúng tôi có thể tự 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ách thức - Lưu công việc của bạn

1. Đối với mỗi thử thách 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 bảng của riêng chúng trong cơ sở dữ liệu cổng thông tin

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

   Làm cách nào để chèn danh sách vào SQLite Python?

   Ví dụ về Python để chèn một hàng vào bảng SQLite .

   Kết nối với SQLite từ Python. .

   Xác định truy vấn Chèn SQL. .

   Nhận đối tượng con trỏ từ kết nối. .

   Thực hiện truy vấn chèn bằng phương thức exec[]. .

   Cam kết thay đổi của bạn. .

   Nhận số hàng bị ảnh hưởng. .

   Xác minh kết quả bằng truy vấn SQL SELECT

   Làm cách nào để chèn danh sách vào cơ sở dữ liệu SQLite?

   SQLite INSERT – chèn một hàng vào bảng .

   Đầu tiên, chỉ định tên của bảng mà bạn muốn chèn dữ liệu sau từ khóa INSERT INTO

   Thứ hai, thêm danh sách các cột được phân tách bằng dấu phẩy sau tên bảng. Danh sách cột là tùy chọn. .

   Thứ ba, thêm danh sách các giá trị được phân tách bằng dấu phẩy sau từ khóa GIÁ TRỊ

   Tôi có thể lưu danh sách trong SQLite không?

   Thông thường, bạn làm điều này bằng cách xâu chuỗi danh sách [với repr[]], sau đó lưu chuỗi . Khi đọc chuỗi từ cơ sở dữ liệu, hãy sử dụng eval[] để tạo lại danh sách. Hãy cẩn thận, mặc dù bạn chắc chắn rằng không có dữ liệu do người dùng tạo nào có thể vào cột hoặc eval[] là một rủi ro bảo mật.

   Làm cách nào để tạo bảng trong sqlite3 bằng Python?

   Thiết lập kết nối hoặc tạo đối tượng kết nối với cơ sở dữ liệu bằng hàm connect[] của module sqlite3

   Tạo một đối tượng Con trỏ bằng cách gọi phương thức con trỏ [] của đối tượng Kết nối

   Bảng biểu mẫu sử dụng câu lệnh CREATE TABLE với phương thức exec[] của lớp Cursor