Import CSV file to SQL Server Using Python | SQL | Python
Вставка
- Опубліковано 16 вер 2024
- Import CSV file to SQL Server Using Python | SQL | Python
In this video, I will teach you how to insert a CSV file to an SQL server using python
GitHub JupyterNotebook: github.com/sid...
GitHub Data: github.com/sid...
Large Language Model (LLM) - LangChain
LangChain: • LangChain Tutorial for...
Large Language Model (LLM) - LlamaIndex
LlamaIndex: • LlamaIndex Tutorial fo...
Machine Learning Model Deployment
ML Model Deployment: • ML Model Deployment us...
Spark with Python (PySpark)
PySpark: https: • PySpark with Python
Data Preprocessing (scikit-learn)
Data Preprocessing Python: • Data Preprocessing Python
Social Media Links
UA-cam: / statswire
Twitter (X) : / statswire
#llm #ai #genai #generativeai #statswire #pyspark #python #pythonprogramming #pythontutorial #machinelearning #datascience
#Python #SQL #Insert
Nice Explanation , Easy to Understand the code
You're welcome!
Thank You!!! The real MVP!!!, I was trying for more than 5 hours to do this in postgres. And it worked just needed to change ? for %s :)
I'm glad you found it useful
Thank you!
You're welcome!
When I try to insert into my table I get "NameError: name 'cursor' is not defined". I have cursor defined on cell 3 as such "cursor = conn.cursor()"...what am I missing?
I figured it out...I wasn't defining the connection in the same cell...interesting.
@@ronito2030 Great! You have solved it on your own. Congrats!
Great Work !!! Helpt me a lot.
Thank you
Very Helpfull video..!
Thank you Jay
Very helpful. Thank you
You're welcome!
Thanks for your tutorial
You're welcome
after typing pip install pyodbc in command prompt, i am getting a msg as 'pip' is not recognized as an internal or external command,
operable program or batch file. Plz help me in this
This is a very normal error. You just have to set the environment variable.
@StatsWire Hi. Can you please tell me from where you are reading fetching csv file? I am doing one programme in which I will upload excel files and then excel data needs to be add in azure sql in which table is already created. Can you please throw some light how i can do that? or any help would be grated.
Hi, please check this out: learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver16
Hello. I wan't to bulk load a huge csv dataset into sql workbench which has several columns. What would be a better way?
You can also do the bulk update. You can get it online.
Very nice tutorial
Thank you
i am getting error as ora-01036 illiegal variable name or number while inserting into table. my table is already present in database having all column types as nvarchar. dataframe column types are also string. please help.
Can you please paste exact error?
Hi, I'm keep getting this error "ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()." what it can be?
The problem arises because Pandas Series objects can contain multiple elements, and applying a boolean condition to the entire Series results in ambiguity.
You need to choose the appropriate method based on your desired logic and the specific requirements of your code.
When I run this the pip command to install pyodbc, it doesn't work. So I checked the version of python I had and it says I don't have python installed. Is this because I didn't install a separate version from the juptyer notebooks? Do I have to install another version? The Python version in juptyer notebooks I have is 3.9.7. I want to follow the tutorial and try it out.
No, you don't have to install python separately for jupyternotebook. I think you have not set the environment variable to access your python from CMD. First set the environment variable in python. You can find many videos on youtube for that
hello bro I'm facing an error as below mentioned could you please help me ::: OperationalError Traceback (most recent call last)
Cell In[30], line 1
----> 1 connection = pyodbc.connect(
2 Trusted_connected='Yes',
3 Driver='{ODBC Driver 17 for SQL Server}',
4 server='SQLEXPRESSO1',
5 Database='test'
6 )
7 cursor=connection.cursor()
The error is likely due to a typo or incorrect parameter in the pyodbc.connect() function call.
Hi , I got an error while executing the cursor query "InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')". Pls help me out, thanks
Hi, could you please use the below link to see what is the reason
stackoverflow.com/questions/46045834/pyodbc-data-source-name-not-found-and-no-default-driver-specified
Will this method work with other SQL workbenches? I’m using a Mac so not using SSMS.
Yes it will work with workbenches
Hi did you change your code at around 7:07, and if so what did you change it to? I' struggling to get this part working. Thanks
Hi, I don't have the exact code but you can use the below code and you won't face any errors for sure
import pandas as pd
import pyodbc
# Import CSV
data = pd.read_csv (r'C:\Users\Ron\Desktop\Test\products.csv')
df = pd.DataFrame(data)
# Connect to SQL Server
conn = pyodbc.connect('Driver={SQL Server};'
'Server=RON\SQLEXPRESS;'
'Database=test_database;'
'Trusted_Connection=yes;')
cursor = conn.cursor()
# Create Table
cursor.execute('''
CREATE TABLE products (
product_id int primary key,
product_name nvarchar(50),
price int
)
''')
# Insert DataFrame to Table
for row in df.itertuples():
cursor.execute('''
INSERT INTO products (product_id, product_name, price)
VALUES (?,?,?)
''',
row.product_id,
row.product_name,
row.price
)
conn.commit()
HI sir, if we have 100+ columns how can we type all those, other than import option is there anyother thing?
Yes, there are other options that you can get in the official documentation.
I keep getting this error on the last row of the INSERT statement, not matter what that last row is
Parameter 5 (""): The supplied value is not a valid instance of data type float
Please use the below code:
import pandas as pd
import pyodbc
# Import CSV
data = pd.read_csv (r'C:\Users\Ron\Desktop\Test\products.csv')
df = pd.DataFrame(data)
# Connect to SQL Server
conn = pyodbc.connect('Driver={SQL Server};'
'Server=RON\SQLEXPRESS;'
'Database=test_database;'
'Trusted_Connection=yes;')
cursor = conn.cursor()
# Create Table
cursor.execute('''
CREATE TABLE products (
product_id int primary key,
product_name nvarchar(50),
price int
)
''')
# Insert DataFrame to Table
for row in df.itertuples():
cursor.execute('''
INSERT INTO products (product_id, product_name, price)
VALUES (?,?,?)
''',
row.product_id,
row.product_name,
row.price
)
conn.commit()
Nice
Thank you
Hi Sir,
Getting error while inserting values into table in ' for loop ' as " 'Pandas' object has no attribute __(column_name)__ "
You can use the below code:
import pandas as pd
import pyodbc
# Import CSV
data = pd.read_csv (r'C:\Users\Ron\Desktop\Test\products.csv')
df = pd.DataFrame(data)
# Connect to SQL Server
conn = pyodbc.connect('Driver={SQL Server};'
'Server=RON\SQLEXPRESS;'
'Database=test_database;'
'Trusted_Connection=yes;')
cursor = conn.cursor()
# Create Table
cursor.execute('''
CREATE TABLE products (
product_id int primary key,
product_name nvarchar(50),
price int
)
''')
# Insert DataFrame to Table
for row in df.itertuples():
cursor.execute('''
INSERT INTO products (product_id, product_name, price)
VALUES (?,?,?)
''',
row.product_id,
row.product_name,
row.price
)
conn.commit()
@@StatsWire Thank You Sir, Will try this
@@user-cf9sm1kp6y You're welcome!
hi, nice video!
i got an error: ProgrammingError: ('Invalid parameter type. param-index=36 param-type=dict', 'HY105') do you know how to solve it?
Hello, thank you. If your values are numpy array of numpy.int64 then convert them to int. Here is an example
a = numpy.array([10, 11, 12], dtype=numpy.int64)
params = (1, 1, int(a[1]), 1, 1, 1)
crsr.execute(sql, params)
Thanks
You're welcome
how do we do this for 300+ CSVs with header row each ?
I think you can use a for loop or something
Is there any way to use python to write a csv directly to the database without the need of a dataframe?
This is the way to write a csv because we are creating a table of rows and columns
Hi,
We are using different machines to insert data on tables using python..
As per ms sql I can't insert more than 999 rows in single inert query.
Also bulk insert(csv) works on server only..
It that any method to overcome this???
Sorry, I did not get your point.
@@StatsWire can we insert large data without using bulk insert method
@@aravinddx976 No, you will have to use bulk
Hi, i got an error like "AttributeError: 'builtin_function_or_method' object has no attribute 'execute'
you need to check your object
@@StatsWire Hi, thanks, i have resolved it
@@ponvigneshshanmugam9686 Great
Is it a ETL process ?
Kind of you can say
hi i wanted to know how to download microsoft sql for my use?
Hello, you can download it from the below link:
ua-cam.com/video/N3B3OonC2AU/v-deo.html
any way to do it in postgress ?
Hello, I have not tried it yet but there must be a way to do it.
('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [2]. (2) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
Please check the connection string
What if we have multiple csv files
Yes, you can import, I will make a video on that. Thanks for the suggestion!
@@StatsWire make it fast
@@nikhilnikki4807 Sure
@@StatsWire when can I expect a video
@@nikhilnikki4807 This weekend
hello sir i need response on this
Got error in cursor.execute stmt
Can you paste the error so I can help you
Attribute error ..
Can you provide more information
Show Code in description
Ok, I will give you the link of jupyternotebook once I upload it on github
@@StatsWire Is there a link of this code?
@@TONY-sf2ut Hi, I don't have the exact code but you can use the below code and you won't face any errors for sure
import pandas as pd
import pyodbc
# Import CSV
data = pd.read_csv (r'C:\Users\Ron\Desktop\Test\products.csv')
df = pd.DataFrame(data)
# Connect to SQL Server
conn = pyodbc.connect('Driver={SQL Server};'
'Server=RON\SQLEXPRESS;'
'Database=test_database;'
'Trusted_Connection=yes;')
cursor = conn.cursor()
# Create Table
cursor.execute('''
CREATE TABLE products (
product_id int primary key,
product_name nvarchar(50),
price int
)
''')
# Insert DataFrame to Table
for row in df.itertuples():
cursor.execute('''
INSERT INTO products (product_id, product_name, price)
VALUES (?,?,?)
''',
row.product_id,
row.product_name,
row.price
)
conn.commit()
@@StatsWire Thank you so much!
@@TONY-sf2ut You're welcome!
when i run pyodbc.driver it didn't appear aql server like you. Why?
Are you gettting any error?
I have the same problem I only get these drivers:
['SQL Server',
'Microsoft Access Driver (*.mdb, *.accdb)',
'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)',
'Microsoft Access Text Driver (*.txt, *.csv)']@@StatsWire
InterfaceError Traceback (most recent call last)
Cell In[37], line 1
----> 1 conn = pyodbc.connect(
2 Trusted_Connected = 'Yes',
3 Driver = {'ODBC Driver 17 for SQL Server'},
4 Server = 'DHINO-PIZZLE',
5 Database = 'Employee_DB'
6 )
8 cursor = conn.cursor()
InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Please try like this:
import pyodbc
conn_str = (
"Driver={ODBC Driver 17 for SQL Server};"
"Server=DHINO-PIZZLE;"
"Database=Employee_DB;"
"Trusted_Connection=Yes;"
)
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
Hi, Thanks for the video.
I got an error code with
DataError Traceback (most recent call last)
Input In [61], in ()
1 for row in df.itertuples():
----> 2 cursor.execute('''
Here is my code:
for row in df.itertuples():
cursor.execute('''
INSERT INTO CM4D.dbo.Test (No, id, title, overview, release_date, popularity, voteaverage, votecount)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
''',
row.No,
row.id,
row.title,
row.overview,
row.release_date,
row.popularity,
row.voteaverage,
row.votecount
)
conn.commit()
Thank you in advance