Are you trying to create a database from an excel file? Here is a quick python tutorial that will teach you how you can create a SQLite database from an excel file.
Convert Excel file into database table in Python
The easiest way to convert an excel file into a database table in python is to use df.to_sql[] function. df.to_sql[] in python convert a data frame into SQLite database. But first, you have to convert your excel file to dataframe.
Follow these steps to convert an Excel file into Sqlie database
Step No 1: Convert Excel file into Dataframe
The first step in the process of conversion of an Excel file to SQLite database is to convert excel file into a data frame. The best way to convert an Excel file into a data frame is to use the read_excel[] function. The read_excle[] function in the python pandas module converts an excel file into a pandas dataframe.
import pandas as pd
import sqlite3
df = pd.read_excel['excel_file.xls']
print[df]Step No 2: Convert Dataframe to SQL database
Once we have the pandas data frame we can use the df.to_sql[] function to convert dataframe to a SQL database.
Below is the code to convert excel file to sqlite database using pandas.
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
Output of the code:
inserted successfully
Convert Multiple Excel Workbooks to a Database table in Python
This is the best method to convert an excel file having multiple workbooks. First, get the name of the workbooks and then insert them into the database.
Check the following code to convert excel file to SQLite database.
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]Summary and Conclusion
We have learned how we can convert excel files to a database tables in python. These two are the best way to do the job done. If you have any questions please let me know in the comment section.
Ảnh của Twitter: @jankolario trên unplash
Chuyển đổi bảng tính thành bảng cơ sở dữ liệu có thể truy vấn
Giới thiệu
Một cơ sở dữ liệu quan hệ Một tập hợp các bảng dữ liệu - bộ các hàng và cột lưu trữ các phần dữ liệu riêng lẻ - có thể được kết nối với nhau. Theo cách này, một cơ sở dữ liệu quan hệ không hoàn toàn không giống với một sổ làm việc Excel với các bộ dữ liệu liên quan được lưu trữ trên nhiều bảng tính. Với suy nghĩ đó, bài đăng này di chuyển qua một ví dụ sử dụng Python để chuyển đổi bảng tính Excel thành cơ sở dữ liệu có thể được truy vấn bằng ngôn ngữ truy vấn có cấu trúc [SQL].relational databaseis a collection of data tables — sets of rows and columns that store individual pieces of data — that can be connected to each other. In this way, a relational database is not totally dissimilar from an Excel workbook with related datasets stored across multiple worksheets. With that thought in mind, this post moves through an example using Python to transform an Excel spreadsheet into a database that can be queried using Structured Query Language [SQL].
Dữ liệu
Ví dụ trong bài đăng này sử dụng dữ liệu từ bộ dữ liệu Superstore-Sales, có thể được tìm thấy ở đây. Bộ dữ liệu này được lưu trữ dưới dạng sổ làm việc Excel và chứa dữ liệu giao dịch bán hàng ví dụ được lưu trữ trên bốn tờ sau: bán hàng, đơn đặt hàng, khách hàng và sản phẩm.
Đọc dữ liệu trong Python
Đầu tiên, nếu bạn chưa cài đặt gói xlrd bằng cách sử dụng condaor
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
Bây giờ, hãy để bắt đầu một phiên Python và nhập Pandas và SQLite3 bằng cách sử dụng
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
Trong mỗi đoạn mã, tham số đầu tiên trong
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
import pandas as pd
import sqlite3
db = sqlite3.connect['sqlite.db']
dfs = pd.read_excel['excel_file.xls', sheet_name=None]
for table, df in dfs.items[]:
df.to_sql[table, db]
print[f'{df} inserted successfully']
Hãy cùng xem một vài hàng đầu tiên của mỗi khung dữ liệu chỉ để đảm bảo mọi thứ đều có vẻ chính xác.
Tuyệt quá! Bây giờ, hãy để Lừa làm cho cơ sở dữ liệu đó.
Tạo cơ sở dữ liệu SQLite
Có nhiều sản phẩm cơ sở dữ liệu quan hệ có sẵn và sản phẩm cụ thể chúng tôi sẽ sử dụng ở đây là SQLite. Đây là một công cụ cơ sở dữ liệu SQL nhẹ có thể được sử dụng để tạo cơ sở dữ liệu được lưu trữ dưới dạng tệp trên một máy tính cá nhân. Chúng ta có thể bắt đầu một đối tượng kết nối cơ sở dữ liệu SQLite mới và gán đối tượng này cho một biến. Dưới đây, tôi đặt tên cho biến này
inserted successfully0.SQLite. This is a lightweight SQL database engine that can be used to create databases stored as files on one’s personal computer. We can initiate a new SQLite database connection object and assign this object to a variable. Below, I named this variable
inserted successfully0.
db_conn = sqlite3.connect["data/superstore.db"]Khi mã này ở trên được thực thi, một tệp mới có tên Superstore.db sẽ được tạo trong thư mục dữ liệu [giả sử Superstore.db chưa tồn tại trong thư mục này]. Tệp Superstore.db mới được tạo là cơ sở dữ liệu SQLite trống tại thời điểm này [nghĩa là, nó không có bảng] .________ 20 cũng chỉ vào cơ sở dữ liệu Superstore.db và có thể được coi là giao diện để làm việc với cơ sở dữ liệu này. Mát mẻ! Nhưng chờ đợi, làm thế nào để chúng ta thực sự chạy mã SQL sẽ tạo các bảng dữ liệu? Rất vui vì bạn đã hỏi. Bây giờ chúng ta cần thiết lập một cursorobject, đây là một công cụ thực thi mã SQL so với cơ sở dữ liệu quan tâm. Ở đây, con trỏ được gán cho một biến tôi đặt tên
inserted successfully2.cursorobject, which is a tool that executes SQL code against the database of interest. Here, the cursor is assigned to a variable I named
inserted successfully2.
c = db_conn.cursor[]Bước tiếp theo là tạo các bảng sẽ được bao gồm trong cơ sở dữ liệu, mặc dù điều quan trọng là phải nhấn mạnh rằng bước tiếp theo này để tạo các bảng sẽ dẫn đến các bảng trống. Sau đó, chúng tôi sẽ điền vào các bảng của mình với dữ liệu từ bốn khung dữ liệu được tạo trước đó. Tuy nhiên, trước khi chúng ta di chuyển xa hơn, hãy để chúng ta xem mã SQL thực tế sẽ được sử dụng để tạo bảng bán hàng để hiểu rõ hơn về quy trình tạo bảng.
Dòng số 1 trong mã trên cung cấp một lệnh để tạo một bảng có tên Sales và Dòng #2 Tiết8 Tạo 7 cột mới trong bán hàng: SalesID, OrderID, ProductID, Salesid, Sales, số lượng, giảm giá và lợi nhuận. Kiểu dữ liệu cho mỗi cột được chỉ định ở bên phải tên cột tương ứng. Thông tin thêm về các kiểu dữ liệu SQLite có thể được tìm thấy ở đây. Lưu ý rằng thứ tự các cột khớp với thứ tự của các cột từ khung dữ liệu được liên kết.
Thứ tự của các cột trong câu lệnh
inserted successfully3 là có chủ ý vì điều này đảm bảo rằng các giá trị thích hợp từ khung dữ liệu đi đến cột dự định trong bảng cơ sở dữ liệu. Ví dụ, nếu tôi đã biến Dòng cột đầu tiên và SalesID thứ hai, các giá trị SalesID sẽ được ghi vào cột OrderID trong bảng bán hàng và cột SalesID trong bảng này sẽ chứa các giá trị OrderID.
Dòng số 9 thiết lập khóa chính của bảng, là một cột chứa các giá trị xác định duy nhất mỗi hàng. Trong bảng bán hàng, SalesID đáp ứng các yêu cầu cho khóa chính vì không có hai hàng nào có cùng giá trị SalesID.primary key, which is a column that contains values that uniquely identify each row. In the sales table, SalesID satisfies the requirements for a primary key because no two rows have the same SalesID value.
Ảnh chụp nhanh của bảng Sales SalesDòng số 10 trong bộ mã trước đó thiết lập đầu tiên trong hai khóa nước ngoài để bán hàng. Khóa nước ngoài là một cột trong một bảng là khóa chính trong một bảng khác. Chẳng hạn, lưu ý rằng các hàng khác nhau có thể chia sẻ cùng một giá trị OrderID trong bảng bán hàng, điều này không đủ điều kiện cho OrderID là khóa chính trong bảng này. Tuy nhiên, trong bảng đơn đặt hàng, mỗi hàng có chứa một thứ tự duy nhất. Như vậy, OrderID có thể đóng vai trò là khóa chính cho các đơn đặt hàng.foreign keys for sales. A foreign key is a column in one table that is the primary key in another table. For instance, notice that different rows can share the same OrderID value in the sales table, which disqualifies OrderID from being the primary key in this table. However,in the orders table each row does contain a unique OrderIDvalue. As such, OrderID can serve as the primary key for orders.
Hãy để Lừa trở về mã thực tế trong dòng số 10 trong câu lệnh
inserted successfully3 trước đó. Phần đầu tiên của mã này, ________ 25, thiết lập rằng cột thứ tự trong doanh số là khóa nước ngoài. Phần thứ hai,
inserted successfully6 sau đó chỉ định khóa và khóa chính mà OrderID đề cập đến. Dòng số 11 từ câu lệnh
inserted successfully3 theo cùng một logic cho cột ProductID.
Bằng cách chỉ định các khóa chính và nước ngoài, chúng tôi có thể tạo một bản đồ trên mạng cho thấy các bảng trong cơ sở dữ liệu liên quan đến nhau như thế nào. Ví dụ, một người quen thuộc với các khái niệm SQL nhưng không quen thuộc với dữ liệu cụ thể này có thể xem xét tuyên bố
inserted successfully3 tạo ra bảng bán hàng và nhận ra rằng dữ liệu từ các bảng bán hàng và đơn đặt hàng có thể được kết hợp với nhau, hoặc kết hợp với nhau bằng cách khớp với Các giá trị trong các cột OrderID từ cả hai bảng. Để minh họa ngắn gọn về cách tham gia các tác phẩm, hãy xem xét cách chúng ta có thể thêm OrderDate vào bảng bán hàng bằng cách tham gia vào các thứ tự, như được minh họa dưới đây.joined, by matching on values in the OrderID columns from both tables. To briefly illustrate how joining works, consider how we can add OrderDate to the sales table by “joining” on OrderID, as illustrated below.Một minh chứng trực quan cơ bản về cách tham gia hoạt động
Kết thúc cơ sở dữ liệu
Với một cái nhìn tổng quan cơ bản về quy trình tạo bảng được thiết lập, hãy để tạo ra tất cả các bảng cơ sở dữ liệu. Đây là nơi chúng ta có thể đặt con trỏ,
inserted successfully2 để làm việc. Chúng tôi có thể chạy
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]Bây giờ là lúc để điền vào các bảng trong cơ sở dữ liệu với dữ liệu liên quan từ bốn khung dữ liệu mà chúng tôi đã tạo trước đây. May mắn thay, bước này có thể được thực hiện một cách dễ dàng khi sử dụng gandas,
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]Tham số đầu tiên trong mỗi dòng mã ở trên xác định tên của bảng cơ sở dữ liệu mà các giá trị từ khung dữ liệu sẽ được viết và tham số sau đây chỉ định kết nối cơ sở dữ liệu thực tế. Tiếp theo,
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]Sự kết luận
Bây giờ chúng tôi có một cơ sở dữ liệu SQLite đã sẵn sàng để đi! Bạn có thể truy vấn tệp cơ sở dữ liệu Superstore.db với Shell dòng lệnh SQLite hoặc phần mềm cơ sở dữ liệu khác hỗ trợ SQLite, chẳng hạn như DBVisualizer. Bạn cũng có thể chạy các truy vấn trên cơ sở dữ liệu bằng cách sử dụng
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]pd.read_sql["SELECT * FROM sales LIMIT 5", db_conn]Khi bạn hoàn thành làm việc trên cơ sở dữ liệu này trong Python, bạn có thể đóng kết nối bằng cách chạy
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]db_conn = sqlite3.connect["data/superstore.db"]và chạy truy vấn bằng cách sử dụng
import sqlite3
import pandas as pd
con=sqlite3.connect['database.db']
wb=pd.ExcelFile['excel_file.xls']
for sheet in wb.sheet_names:
df=pd.read_excel['excel_file.xls',sheet_name=sheet]
df.to_sql[sheet,con, index=False,if_exists="replace"]
con.commit[]
con.close[]Một từ chia tay làm rõ trước khi chúng tôi đóng. Khi bắt đầu bài đăng này, tôi đã đề cập rằng cấu trúc của một sổ làm việc Excel tương tự như cơ sở dữ liệu SQL ở chỗ cả hai có thể bao gồm nhiều bảng liên quan. Cuối cùng, bài tập này nhằm chứng minh làm thế nào một sổ làm việc Excel có thể dễ dàng biến thành một tệp cơ sở dữ liệu SQLite. Tuy nhiên, chúng tôi chỉ làm trầy xước bề mặt của thiết kế cơ sở dữ liệu và tôi muốn nhấn mạnh rằng một cơ sở dữ liệu được thiết kế tốt không chỉ là một tập hợp các bảng. Thật vậy, quá trình chuẩn hóa hóa, nhằm mục đích loại bỏ các dự phòng dữ liệu và thúc đẩy tính toàn vẹn của dữ liệu, là rất quan trọng trong thiết kế cơ sở dữ liệu quan hệ. Xin lưu ý rằng cơ sở dữ liệu mà chúng tôi tạo có thể được chuẩn hóa thêm. Nếu bạn quan tâm đến việc tìm hiểu thêm về cơ sở dữ liệu quan hệ và cơ sở lý luận đằng sau việc lưu trữ dữ liệu trên nhiều bảng, tôi khuyên bạn nên đọc thêm về chuẩn hóa.normalization, which aims to remove data redundancies and promote data integrity, is very important in relational database design. Be aware that the database that we created could be normalized further. If you are interested in learning more about relational databases and the rationale behind storing data across multiple tables I do recommend reading more about normalization.
Được rồi, điều đó sẽ làm điều đó cho bài viết này. Cảm ơn đã đọc và vui lòng liên hệ nếu bạn có bất kỳ câu hỏi và/hoặc phản hồi mang tính xây dựng.