在Google Colab中查询SQLite数据库文件 [英] Querying SQLite database file in Google Colab

问题描述

print ('Files in Drive:')

!ls drive/AI

云端硬盘中的文件

database.sqlite
Reviews.csv
Untitled0.ipynb
fine_food_reviews.ipynb
Titanic.csv

当我在Google Colab中运行上述代码时，驱动器中显然存在我的sqlite文件.但是，每当我对此文件运行查询时，它都会显示

When I run the above code in Google Colab, clearly my sqlite file is present in my drive. But whenever I run some query on this file, it says

# using the SQLite Table to read data.
con = sqlite3.connect('database.sqlite') 

#filtering only positive and negative reviews i.e. 
# not taking into consideration those reviews with Score=3
filtered_data = pd.read_sql_query("SELECT * FROM Reviews WHERE Score !=3",con)

DatabaseError:在sql'SELECT * FROM Reviews WHERE上执行失败得分！= 3':没有这样的表格:评论

DatabaseError: Execution failed on sql 'SELECT * FROM Reviews WHERE Score != 3 ': no such table: Reviews

推荐答案

在下面，您将找到解决Colab VM上 db设置，表创建，数据插入和数据查询.在单个笔记本单元中执行所有代码段.

Below you will find code that addresses the db setup on the Colab VM, table creation, data insertion and data querying. Execute all code snippets in individual notebook cells.

但是请注意，此示例仅显示了如何在非持久Colab VM上执行代码.如果要将数据库保存到GDrive，则必须先安装Gdrive(源):

Note however that this example only shows how to execute the code on a non-persistent Colab VM. If you want to save your database to GDrive you will have to mount your Gdrive first (source):

from google.colab import drive
drive.mount('/content/gdrive')

和导航到适当的文件目录.

第1步:创建数据库

import sqlite3

conn = sqlite3.connect('SQLite_Python.db')  # You can create a new database by changing the name within the quotes
c = conn.cursor() # The database will be saved in the location where your 'py' file is saved

# Create table - CLIENTS
c.execute('''CREATE TABLE SqliteDb_developers
             ([id] INTEGER PRIMARY KEY, [name] text, [email] text, [joining_date] date, [salary] integer)''')

conn.commit()

测试数据库是否成功创建:

Test whether the DB was created successfully:

!ls

输出:

sample_data SQLite_Python.db

sample_data SQLite_Python.db

---

步骤2:将数据插入数据库

import sqlite3

try:
    sqliteConnection = sqlite3.connect('SQLite_Python.db')
    cursor = sqliteConnection.cursor()
    print("Successfully Connected to SQLite")

    sqlite_insert_query = """INSERT INTO SqliteDb_developers
                          (id, name, email, joining_date, salary) 
                           VALUES (1,'Python','[e-mail]','2020-01-01',1000)"""


    count = cursor.execute(sqlite_insert_query)
    sqliteConnection.commit()
    print("Record inserted successfully into SqliteDb_developers table ", cursor.rowcount)
    cursor.close()

except sqlite3.Error as error:
    print("Failed to insert data into sqlite table", error)
finally:
    if (sqliteConnection):
        sqliteConnection.close()
        print("The SQLite connection is closed")

输出:

已成功连接到SQLite

Successfully Connected to SQLite

记录已成功插入SqliteDb_developers表1

Record inserted successfully into SqliteDb_developers table 1

SQLite连接已关闭

The SQLite connection is closed

---

第3步:查询数据库

import sqlite3

conn = sqlite3.connect("SQLite_Python.db")

cur = conn.cursor()
cur.execute("SELECT * FROM SqliteDb_developers")

rows = cur.fetchall()

for row in rows:
  print(row)

conn.close()

输出:

(1，'Python'，'[e-mail]'，'2020-01-01'，1000)

(1, 'Python', '[e-mail]', '2020-01-01', 1000)

这篇关于在Google Colab中查询SQLite数据库文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！