#789

Committed 18 Sep 2023 04:32PM UTC coverage: 74.685% (-5.7%) from 80.421%

Build # #789

Build Type

push

circle-ci

Committed by Jiashen Cao

Commit Message

reformat

Run Details

8842 of 11839 relevant lines covered (74.69%)

0.75 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

32.0

/evadb/third_party/databases/sqlite/sqlite_handler.py

# coding=utf-8
# Copyright 2018-2023 EvaDB
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
import sqlite3

import pandas as pd

from evadb.third_party.databases.types import (
    DBHandler,
    DBHandlerResponse,
    DBHandlerStatus,
)


class SQLiteHandler(DBHandler):
    def __init__(self, name: str, **kwargs):
        """
        Initialize the handler.
        Args:
            name (str): name of the DB handler instance
            **kwargs: arbitrary keyword arguments for establishing the connection.
        """
        super().__init__(name)
        self.database = kwargs.get("database")
        self.connection = None

    def connect(self):
        """
        Set up the connection required by the handler.
        Returns:
            DBHandlerStatus
        """
        try:
            self.connection = sqlite3.connect(
                database=self.database, isolation_level=None  # Autocommit mode.
            )
            return DBHandlerStatus(status=True)
        except sqlite3.Error as e:
            return DBHandlerStatus(status=False, error=str(e))

    def disconnect(self):
        """
        Close any existing connections.
        """
        if self.connection:
            self.connection.close()

    def get_sqlalchmey_uri(self) -> str:
        return f"sqlite:///{self.database}"

    def check_connection(self) -> DBHandlerStatus:
        """
        Check connection to the handler.
        Returns:
            DBHandlerStatus
        """
        if self.connection:
            return DBHandlerStatus(status=True)
        else:
            return DBHandlerStatus(status=False, error="Not connected to the database.")

    def get_tables(self) -> DBHandlerResponse:
        """
        Return the list of tables in the database.
        Returns:
            DBHandlerResponse
        """
        if not self.connection:
            return DBHandlerResponse(data=None, error="Not connected to the database.")

        try:
            query = "SELECT name AS table_name FROM sqlite_master WHERE type = 'table'"
            tables_df = pd.read_sql_query(query, self.connection)
            return DBHandlerResponse(data=tables_df)
        except sqlite3.Error as e:
            return DBHandlerResponse(data=None, error=str(e))

    def get_columns(self, table_name: str) -> DBHandlerResponse:
        """
        Returns the list of columns for the given table.
        Args:
            table_name (str): name of the table whose columns are to be retrieved.
        Returns:
            DBHandlerResponse
        """
        if not self.connection:
            return DBHandlerResponse(data=None, error="Not connected to the database.")
        """
        SQLite does not provide an in-built way to get the column names using a SELECT statement.
        Hence we have to use the PRAGMA command and filter the required columns.
        """
        try:
            query = f"PRAGMA table_info('{table_name}')"
            pragma_df = pd.read_sql_query(query, self.connection)
            columns_df = pragma_df[["name", "type"]].copy()
            columns_df.rename(columns={"type": "dtype"}, inplace=True)
            return DBHandlerResponse(data=columns_df)
        except sqlite3.Error as e:
            return DBHandlerResponse(data=None, error=str(e))

    def _fetch_results_as_df(self, cursor):
        try:
            # Handling case-sensitive databases like SQLite can be tricky. Currently,
            # EvaDB converts all columns to lowercase, which may result in issues with
            # these databases. As we move forward, we are actively working on improving
            # this aspect within Binder.
            # For more information, please refer to https://github.com/georgia-tech-db/evadb/issues/1079.

            res = cursor.fetchall()
            res_df = pd.DataFrame(
                res,
                columns=[desc[0].lower() for desc in cursor.description]
                if cursor.description
                else [],
            )
            return res_df
        except sqlite3.ProgrammingError as e:
            if str(e) == "no results to fetch":
                return pd.DataFrame({"status": ["success"]})
            raise e

    def execute_native_query(self, query_string: str) -> DBHandlerResponse:
        """
        Executes the native query on the database.
        Args:
            query_string (str): query in native format
        Returns:
            DBHandlerResponse
        """
        if not self.connection:
            return DBHandlerResponse(data=None, error="Not connected to the database.")
        try:
            cursor = self.connection.cursor()
            cursor.execute(query_string)
            return DBHandlerResponse(data=self._fetch_results_as_df(cursor))
        except sqlite3.Error as e:
            return DBHandlerResponse(data=None, error=str(e))

1	# coding=utf-8
2	# Copyright 2018-2023 EvaDB
3	#
4	# Licensed under the Apache License, Version 2.0 (the "License");
5	# you may not use this file except in compliance with the License.
6	# You may obtain a copy of the License at
7	#
8	# http://www.apache.org/licenses/LICENSE-2.0
9	#
10	# Unless required by applicable law or agreed to in writing, software
11	# distributed under the License is distributed on an "AS IS" BASIS,
12	# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13	# See the License for the specific language governing permissions and
14	# limitations under the License.
15	import sqlite3	1✔
16
17	import pandas as pd	1✔
18
19	from evadb.third_party.databases.types import (	1✔
20	DBHandler,
21	DBHandlerResponse,
22	DBHandlerStatus,
23	)
24
25
26	class SQLiteHandler(DBHandler):	1✔
27	def __init__(self, name: str, **kwargs):	1✔
28	"""
29	Initialize the handler.
30	Args:
31	name (str): name of the DB handler instance
32	**kwargs: arbitrary keyword arguments for establishing the connection.
33	"""
34	super().__init__(name)	1✔
35	self.database = kwargs.get("database")	1✔
36	self.connection = None	1✔
37
38	def connect(self):	1✔
39	"""
40	Set up the connection required by the handler.
41	Returns:
42	DBHandlerStatus
43	"""
44	try:	×
45	self.connection = sqlite3.connect(	×
46	database=self.database, isolation_level=None # Autocommit mode.
47	)
48	return DBHandlerStatus(status=True)	×
49	except sqlite3.Error as e:
50	return DBHandlerStatus(status=False, error=str(e))
51
52	def disconnect(self):	1✔
53	"""
54	Close any existing connections.
55	"""
56	if self.connection:	×
57	self.connection.close()	×
58
59	def get_sqlalchmey_uri(self) -> str:	1✔
60	return f"sqlite:///{self.database}"	×
61
62	def check_connection(self) -> DBHandlerStatus:	1✔
63	"""
64	Check connection to the handler.
65	Returns:
66	DBHandlerStatus
67	"""
68	if self.connection:	×
69	return DBHandlerStatus(status=True)	×
70	else:
71	return DBHandlerStatus(status=False, error="Not connected to the database.")	×
72
73	def get_tables(self) -> DBHandlerResponse:	1✔
74	"""
75	Return the list of tables in the database.
76	Returns:
77	DBHandlerResponse
78	"""
79	if not self.connection:	×
80	return DBHandlerResponse(data=None, error="Not connected to the database.")	×
81
82	try:	×
83	query = "SELECT name AS table_name FROM sqlite_master WHERE type = 'table'"	×
84	tables_df = pd.read_sql_query(query, self.connection)	×
85	return DBHandlerResponse(data=tables_df)	×
86	except sqlite3.Error as e:
87	return DBHandlerResponse(data=None, error=str(e))
88
89	def get_columns(self, table_name: str) -> DBHandlerResponse:	1✔
90	"""
91	Returns the list of columns for the given table.
92	Args:
93	table_name (str): name of the table whose columns are to be retrieved.
94	Returns:
95	DBHandlerResponse
96	"""
97	if not self.connection:	×
98	return DBHandlerResponse(data=None, error="Not connected to the database.")	×
99	"""	×
100	SQLite does not provide an in-built way to get the column names using a SELECT statement.
101	Hence we have to use the PRAGMA command and filter the required columns.
102	"""
103	try:	×
104	query = f"PRAGMA table_info('{table_name}')"	×
105	pragma_df = pd.read_sql_query(query, self.connection)	×
106	columns_df = pragma_df[["name", "type"]].copy()	×
107	columns_df.rename(columns={"type": "dtype"}, inplace=True)	×
108	return DBHandlerResponse(data=columns_df)	×
109	except sqlite3.Error as e:
110	return DBHandlerResponse(data=None, error=str(e))
111
112	def _fetch_results_as_df(self, cursor):	1✔
113	try:	×
114	# Handling case-sensitive databases like SQLite can be tricky. Currently,
115	# EvaDB converts all columns to lowercase, which may result in issues with
116	# these databases. As we move forward, we are actively working on improving
117	# this aspect within Binder.
118	# For more information, please refer to https://github.com/georgia-tech-db/evadb/issues/1079.
119
120	res = cursor.fetchall()	×
121	res_df = pd.DataFrame(	×
122	res,
123	columns=[desc[0].lower() for desc in cursor.description]
124	if cursor.description
125	else [],
126	)
127	return res_df	×
128	except sqlite3.ProgrammingError as e:
129	if str(e) == "no results to fetch":
130	return pd.DataFrame({"status": ["success"]})
131	raise e
132
133	def execute_native_query(self, query_string: str) -> DBHandlerResponse:	1✔
134	"""
135	Executes the native query on the database.
136	Args:
137	query_string (str): query in native format
138	Returns:
139	DBHandlerResponse
140	"""
141	if not self.connection:	×
142	return DBHandlerResponse(data=None, error="Not connected to the database.")	×
143	try:	×
144	cursor = self.connection.cursor()	×
145	cursor.execute(query_string)	×
146	return DBHandlerResponse(data=self._fetch_results_as_df(cursor))	×
147	except sqlite3.Error as e:
148	return DBHandlerResponse(data=None, error=str(e))

georgia-tech-db / eva / #789

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous