Import Excel Tables To SQL Server (Or Any Database) Without A Single Line Of SQL Using Python

if I have a SQL user like myself which constantly needing to import data by Excel file to SQL Server then you'll find this video extremely useful in this video we'll create a very handy Python program to dynamically import tables for Excel file to SQL server or any database we are writing a single line of single statement alright so here let me go into my project folder in this folder I have this Excel file dummy open the Excel file in this file I have three tabs any tab is going to represent its own table that means that I have three tables that I'm going to import to my single server database when I run the Python program and for demonstration purpose I'm going to import the tables to this my demo database alright so currently there's no tables reside in this database so it's completely empty all right so go ahead in the Longshore terminal because we need to first of all install the python packages that we'll be using to make the program to work right so there are three libraries that I will be using one is the penis Library which is a very powerful data analysis python package followed by the pi odbc python package which is a very popular library that provides a very simple and efficient way to interact with database using the odbc API in the cell library that we'll be using is the SQL Alchemy which is an object additional mapping python package which provides a set of high-level tools for working with relational databases using python alright so here might Seminole I'm going to type pip install then I'm going to type the package's name and the first one is going to be paint dust and the second one is going to be the py py odbc library and this one is going to be the SQL Alchemy python package and pass enter and once the python libraries are installed open your code editor I'm using field school and create a blank python script alright so here for the input statement can only make the phone a little bit bigger all right so for the input statement I'm going to import payment as PD then we're going to import py py odbc as odbc then from the SQL Alchemy module I'm going to import the create engine function and this function is used to create a connection to your database now from the secret Alchemy die engine module I'm going to import this URL class and the URL class is used to construct the connection string now once we import the libraries we can now go ahead and prepare the variables now there are three things I will need once the server address let me name this to server name now if you don't know how to get the server name now in SQL Server we can run the select followed by two s symbols server name in rounds and that will send the server address now hear me uh insert the use my demo statement to connect to the My Demo database now here to connect to my SQL Server database I'm going to create the connection URL first using the url.create method now here we need to specify the database type and for SQL Server is going to be Ms SQL I also need to specify the python library that we'll be using to connect to our database now here I'm using py pyodbc despite the name difference essentially py odbc and POI odbc those two are the same libraries except that with the py py ODB Library I don't have that much issue when I'm using the py py odbc python package now going back to the url.create method now you need to construct the connection string using the query parameter now here to pass the connection string so first we need to create additionally now for the key it's going to be odbc connect engine need to replace that with server name now for the value we need to provide the driver type and it's going to be let me maximize the window it's going to be a single server followed by the server address followed by the database that want to connect to now to actually connect to the database we need to use the create engine function and we pass the connection URL and set the module that I want to use to connect to the database now once we establish a database connection the next thing we need to do here is we need to import the Excel file right so here my Excel file is going to be stuck historical data dot Excel SX then I'm going to use pandas.rexcel method and I'm going to load the Excel file and to load every single Excel worksheets I need to set the sheet name to none right so here let me go ahead around this code block and I'll walk you to the step individually all right so here if I print the DF Excel option now it's going to return as a dictionary so here for the first item is going to be Amazon info by Microsoft and Google is not stream but I know it's there so if we print the keys now from the DF Excel option we have three items Amazon Google and Microsoft now the keys is going to be the Washi name aim for the value is going to be the data frame that in each worksheet now if I run the values function and it's going to return three data film data ties to each worksheet now to import those three tables into SQL Server we simply just need to array each dictionary object using the items method now the items method is going to return the key and value so for the value we know it's going to be the data frame object and the key is going to be the version name now just in case if a washer does not contain any data I want to use the this if statement if the diaphragm is empty they want to skip this iteration then go to the next item otherwise from the data frame object this is a function code to a SQL method answer very very powerful function to work in a database now if we look at the documentation stream if we look at the if axis parameter so here we have three options we can set the parameter either as fill to replace a pin Phil means that if the table exists then it's going to do an exception and replace is going to replace the existing table the pin is going to basically add the records to the existing table all right so here are using the to SQL method I need to set the table name then we're going to provide the database connection followed by how we want to handle the scenario when a table idex is and because I don't want to import the index I'm going to set the index to false now in terms of the data type so SQL Alchemy is going to try best to identify the data type based on each account's value and the library doesn't get right all the time so just in case if I want to mainly set the data type for a specific field so we can go to the documentation and here all the supported data type which I'll link the link in the description below so you can reference the additional documentation page now let's say I want to specify let's see all right so let's go on to set the dates field as far chart data type all right so going back to the input statement I'm going to import the SQL Alchemy Library by itself all right so going back to the to SQL method and here we can use this D type parameter to specify accounts data type right so my field name is going to be date so I'm going to change this to date now for the data type we can reference the data type list using SQL Alchemy types followed by the data type object in this case I'm using voucher now inside the virtual class I can set the Quilted limit using the length and parameter and I'll set the character element to 10. all right so this is basically everything we need to write to create this Python program and as you can see that there's only 20 lines of code now here let me go in the terminate this session and I'll put a script in the in my SQL Server window side by side all right so here I'm going to plus F5 to run the python script now just like the script is finished now if I go back to my SQL Server management Studio now here I'm going to refresh my tables folder and here are the three tables that I uploaded using the Python program now just for verification let me go ahead and select the records found just one of the tables now if I run the select statement and here's the table output and if we look at the column data types now for the day column I know I set the data type to file chart with the character size 210. now for everything else based on the value secret Alchemy is going to uh like I said before it's going to try its best to identify the data type all right so I'm just going to be able so I'm going to cover in this video and hope you guys find this video useful and feel free to post a question or feedback in the comment section below and if you enjoyed this video please don't forget to give this video a like and click on the Subscribe button and I'll see you guys next time bye

Channel: Jie Jenn

Views: 2,118

Keywords: sqlalchemy, sql server, python, database, data import

Id: QWa7qDomi1A

Length: 11min 1sec (661 seconds)

Published: Thu Apr 06 2023