Is there a way to save SQL table as a Python dataframe?-CodePudding

I am trying to save the table on databricks (bitbucket is a source).

I can't figure out how to give a name to it the same way you do in Python (I do not have a lot of experience with Sql). For instance, if in python we: "df = pd.read_table("C:\Users...." and then we use it.

%sql
SELECT column1, column2 FROM table WHERE column3='apple';

I have this code, but I don't know how to continue to work with it using Python.

CodePudding user response：

you need to use a connection

import pandas as pd
import sqlite3

# Read sqlite query results into a pandas DataFrame
con = sqlite3.connect(r"c:\my\database.sqlite")
apple_df = pd.read_sql_query("SELECT column1, column2 FROM table WHERE column3='apple'", con)

CodePudding user response：

I would suggest using mysql.connect

Here is an example code:

import pandas as pd
import mysql.connect as mc

con = mc.connect(host='localhost', user='root', passwd='<your_password>', database='<database_name>')

DataFrame = pd.read_sql('<SQL Querries>', con)

the code below is used to open a connection to mysql

con = mc.connect(host='localhost', user='root', passwd='<your_password>', database='<database_name>')

you can check if the connection was successful using the below code

if con.is_connected():
    print('connected....')
else:
    print('connection failed....')