import os

#def for finding sql_file

def locate_sql_file(folderlocation, starts_with):

print('OK, let me search for the latest file, starting with: ' +str(starts_with))

try:

for f_name in os.listdir(folderlocation):

try:

if f_name.startswith(starts_with):

filename_w_ext = str(os.path.basename(f_name))

print("File found: " + filename_w_ext)

sql_location = folderlocation + "\\" + filename_w_ext

return sql_location

except:

print('Oops...could not find the SQL-script in folder:\n'+folderlocation)

except:

print('Oops...could not find the SQL-script in folder:\n'+folderlocation)

import locale

import io

#Def for guessing the encoding of the SQL file

def guess_encoding(file):

"""guess the encoding of the given file"""

with io.open(file, "rb") as f:

data = f.read(5)

if data.startswith(b"\xEF\xBB\xBF"): # UTF-8 with a "BOM"

return "utf-8-sig"

elif data.startswith(b"\xFF\xFE") or data.startswith(b"\xFE\xFF"):

return "utf-16"

else: # guessing utf-8 doesn't work in Windows, so we just give it a try:

try:

with io.open(file, encoding="utf-8") as f:

return "utf-8"

except:

return locale.getdefaultlocale()[1]

import textwrap

#def for creating query string

def create_query_string(sql_file):

with open(sql_file, 'r', encoding=guess_encoding(sql_file)) as f_in:

lines = f_in.read()

# remove common leading whitespace from all lines

query_string = textwrap.dedent("""{}""".format(lines))

return query_string

print('Found the file and created a converted string.')

import pypyodbc

#Connect to SQL server

def open_connection():

try:

global con

print('Trying to connect to SQL Server....')

con = pypyodbc.connect('Driver={SQL Server};'

'Server=servername;' #enter your servername here

'Database=databasename;' #enter your databasename here

'Trusted_Connection=yes;')

print('Connection made!')

except:

print('Could not connect!')

Python - Read query and fetch data from SQL server

This function uses the open connection and the single SQL-string to fetch the data and create a Pandas DataFrame.

import pandas as pd

def create_df():

try:

df = pd.read_sql_query(sql_script, con)

print('Query executed and data imported')

except:

print('Maybe another day')

Python - Close the connection to SQL server

That's all, no further explanation needed.

#def Close Connection with SQL server

def close_connection():

try:

con.close()

print('Connection closed')

except:

print('Connection was never open or already closed')

Python x SQL - Bringing it All together

Time to put it to the test. Just fire all the created functions and you're good to go!

#folder with SQL-files

sql_folder = r"\\user\path\folderwithSQLfiles"

#find SQL-file if the filename contains the given string value.

#e.g. this filename could be 'Salesdata_2019100915'

sql_file = locate_sql_file(sql_folder, "Salesdata_20") #This is were the '_YYMMDD-notation' comes in handy.

#and get the code in a string

sql_script = create_query_string(sql_file)

#connect to SQL_server

open_connection()

#CREATE DATAFRAME FROM SQL SERVER

create_df()

#close the connection with SQL server

close_connection()

Tags

How to fetch data from sql to python? How to connect Python to SQL? How to establish a connection between Python and SQL Server? How to get data from SQL Server in Python? How to get data from SQL Server in a Pandas DataFrame? How to import data into Python from SQL Server?