Thanks a lot for this video. It was very helpful in setting up the connection as I was struggling with it for a long time. You explained everything in an easy to understand manner. Thanks again!!
Yeah, this library is excellent and gives you access to so many tools. The only challenge I've seen so far with it is the type of driver you need to work with. When you have both 64-bit and 32-bit applications, things can get wacky.
I’m trying this method to import data from a excel file with 15 columns in a sql table with same 15 columns. I’m getting an error “string index out of range” in the line values = (row[0], row[1].....row[14]). How to fix this?
Hey , thanks a lot for this video.It helped me alot. Though i not able to retrieve an image saved in sql server as image format .I would really appreciate some help. Thanks . Good work Dude!
hey!! great video! just a quick question though, what if I have a large table in json and crazy amount of columns, any easier way that coding every column?
Hi, Thanks for the video, I created a access database to store value fetched from xpaths, i have to delete the stored values in my table to use the table freshly for the next run in a loop after storing the data in a excel source file. I used 'TRUNCATE TABLE table_name'', but its showing invalid syntax near Table. What can i do here? please help..
if my data is not in a matrix (list with list elements) but in a dataframe, do I need to convert my pandas dataframe to a matrix like in your example in order to insert the data into a SQL Server table?
Per the docs, "Connections are automatically closed when they are deleted (typically when they go out of scope) so you should not normally need to call [conn.close()], but you can explicitly close the connection if you wish. " and similarly for cursors: "Cursors are closed automatically when they are deleted (typically when they go out of scope), so calling [csr.close()] is not usually necessary."
If I understand correctly, what you're saying is that there is no need to close the connection? See, that's strange because on my work laptop, when I do anything related to our SQL database using PYODBC, I always have to close out the connection. If I don't, I have all these "sessions" that are still left open on the server. Now I may be doing something wrong on my end that's causing the session not to end, or maybe I'm misinterpreting what the server is telling me. However, it's strange that they say you don't need to close anything explicitly.
cnxn = pyodbc.connect() Can you explain why connect() is a method? From the camel casing, it looks like a function. Also within the module its also defined like a function. Not sure why its being instantiated like an object of a class. Thanks
Thanks for the video. It was super useful. Can you please help with adding a whole dataframe to SQL Server table? Table already setup in SQL server. I see some dataframe.To_SQL method but can't get it to work.
i have a python dash app deployed on Azure apps. When it accesses SQL Server it leaves the connection open. Yes i explicitly closed, shut of connection pools as well
Thanks for the video. I followed the same exact steps and got an "invalid escape sequence \s" on the line where you set the server variable. how are you not getting that same error?
Great video, thank you. Was hoping to hear more about how/why the 'for row in cursor' works given the cursor object returns something along the lines of so not totally clear why or how the for loop is able to access the rows just b/c the cur.execute(select *) statement is included. printing cursor variable even after the cur.execute is performed doesn't return a single line with the array.
Well keep in mind that it's not technically an array in python. It's a Cursor object, and that cursor object just so happens to have a mechanism that allows you to iterate through it. My guess is there is probably a "__getitem__" method which allows you to loop through the returned values. I know this might sound confusing, but the giveaway is the fact you're seeing a "Cursor" object. That to me says it's not a list, so it could have its own "__repr__" method and stuff like that.
Hello, quick question here I'm making a gui where the user enters the server name and then I'll list all databases etc how to I config the connection like mydb = pyodbc.connect(server="";) and then in a function I'll get the input from the user and configure the connection to that server like mydb.config(server=userinput.get()) seems that pyodbc has no attribute config so what can I use instead
I'm a little confused about what you're trying to do. Are you trying to ask for the Server name and then use that information to query all the databases in that server to then populate that in the GUI? If that is the case, here is the connection string and query. # Connection String. cnxn = pyodbc.connect(driver = 'Driver', server='server', database='master', trusted_connection='yes') cursor = cnxn.cursor() cursor.execute("SELECT name FROM sys.databases")
Hey thanks for the video! I am running into this issue: return pyodbc.connect( pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)') I dont quite understand what I should fix based of of this error.
Additional question, query output is not displaying column names. I want to export the query output in pandas dataframe. Do I need to insert the columns names in pd manually, or there is a way to populate column names with query result ?
I have 8 columns and while i pass all the values , it gives me error: "The SQL contains 0 parameter markers, but 8 parameters were supplied', 'HY000".. Please help..
In essence, all you would do is read the file and then execute the query inside of it. However, be careful because it will need to be formatted a certain way sometimes. For example, if you want to just read they file and store the "query string" in a variable it would look something like this: sql_file = "my_sql_query.sql" sql_query = open(sql_file, mode='r', encoding='utf-8-sig').read() print(sql_query) Also note the encoding, you may or may not need this depending on how the file is, but for safety I usually put it in there.
So you notated the server name here for your server in your example. However, what’s the alternative I’f say you’re using SQLite which is a serverless, and pretty much SQLite dumps into simple file copies? How you pass it through and connect to SQLite?
So keep in mind I've never actually connected to an SQLite database using PYODBC but supposedly you can from reading the documentation. Here is how the code will look: cnxn = pyodbc.connect("Driver=SQLite3 ODBC Driver;Database=sqlite.db") Here is the link to where I got the code from github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQLite He does mention you may not even need the driver at all. However, he doesn't give an example as to how the code will change if you don't reference the driver.
Hi, Thanks for the video. I am now trying to set up the pyodbc and make connection with SQL server on MACos. I already installed odbc driver but when I ran 'pyodbc.drivers()' on jupyter nootbook, it returned empty list []. Do you know how to solve this problem?
For macOS the PYODBC library is very different compared to windows. I think you can still connect to a SQL Server instance but I don't think you can specify any driver because macOS doesn't install any. You might want to look at this: github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX
Technically you don't have to specify the column names in a insert query, you just then have to make sure that the info is in the correct order when you insert it.
Hello friend. I am getting error after finish coding - pyodbc.ProgrammingError: ("A TVP's rows must be Sequence objects.", 'HY000'). And using pycharm app. Could you help me please?
Hey man awesome video. im just having trouble actually using pyodbc. i did eveything like you did. but it doesnt see the attributes of the pyodbc. Any suggestions?
Hey, how can I get the PK id after insertion data? sql = "INSERT INTO FORM SELECT " + str(form.FK_Customer) + " , '" + form.Description_Form + "' , 1019,getdate() SELECT @@IDENTITY AS ID" row = cursor.execute(sql) for r in row: print(r) cursor.commit() I am trying to use the code above, but it is not working
What's the error you're getting? Also, is it the insert query that's not working or the select query? I can't tell if they're two separate queries that you run independently or together. Also, be careful using a GetDate() function in the query. What exactly is that doing, and you verified that the table can accept the date data type, right? Dates can cause all sorts of messes if not used correctly. Like the use of the "@@Identity" system function.
first i would like to thank you but i am having some simple error after defining connection string ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Not yet, and PYODBC is weird with this topic because even if you use the "executemany()" method, like they specify in the documentation. What you'll find is it still inserts it row by row. The work around I do is to create a string where I do a bulk insert of tuples, to "bypass" it.
hello, I followed and tried your tutorial but im getting and error in the part of assigning data to values. the error is string index out of range. let me know your thoughts. thanks
@@anjofamini5697 Yes I've seen sqlachemy and to_sql approach. My problem is that I have to use SQL server as DB, and I couldn't fix the out of range problem. Thanks Bro for replying ;))
Thanks man, I had a mistake when I tried to put the ip in the server parameter. I have a doubt, which is better SQL Server Native Client 11.0 or ODBC Driver 17 for SQL Server?
Awesome Video ! Question ? plttrackingconn = pyodbc.connect("Driver={SQL Server};" "Server=10.98.97.101;" "Database=Tracking;" "Trusted_Connection=yes;" "ApplicationIntent=ReadOnly;") 'Tracking' is in an availability group and is currently accessible for connections when the application intent is set to read only. Not working for me
Is it possible use SQL database Without Installing Microsoft SQL Server Management Studio using python? For example i must install MSQL SS, create server, make database, create tables... Can i do that without install MSQL SS and using only python?
I think what you're talking about is an in-memory database, something similar to what SQL Alchemy offers. I don't think PYODBC offers such functionality,.
Hi @SigmaCoding , I'm trying to connect to Microsoft SQL production database using pyodbc , but I'm getting an error "[DBNETLIB]SSL Security error (18) (SQLDriverConnect) ][DBNETLIB]ConnectionOpen (SECDoClientHandshake())" ,I tried searching it everywhere but I couldn't find the useful. While connecting to Microsoft SQL Testing database , I'm easily connecting to it and getting the required output in my Dataframe, please help......................
I tried many things bro , but still not able to do so, I will email you the solutions I tried . Please help me to solve this , I had almost written 1000 lines of code for my automation use case using pandas ,it starts with connecting to testing DB and then fetching 5 input tables and using them for further operation . So as of now for production , exporting DB data to excel and then reading this using pandas and continuing my project.
No in particular reason, it was just the first library I was exposed to at my job that interacted with ODBC. I've heard of pypyobdc and have heard good things about it, it's basically pyodbc just in pure python.
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; \ SERVER=' + server + '; \ DATABASE=' + database +';\ Trusted_Connection=yes;') Im getting this error for my connection string above: File "", line 20 SERVER=' + server + '; \
@@SigmaCoding found answer, you helped. now another problem is I run this everyday. But only want to save 2 days record. Meaning when i run at tomorrow, i need to delete record from yesterday. You could shed some light on this? thanks
@@JonathanJournal Lot's of ways to approach this, but the most simple one is that when you insert your data, you add a timestamp for when added. What this will do is provide a way to see what data was entered when. All you would do is every time you startup this script is have a "DELETE" query that will delete records that match the previous day's date. That means you'll need to add a "WHERE" condition that will filter the records to be removed. Just keep in mind, once it's deleted, it's deleted; there is no going back. In other words, make sure you really want to delete historical data.
@@JonathanJournal If you want to write a Pandas data frame to SQL Server, there are a few ways to achieve this. However, will you be writing to an existing table, or will this data frame create a new SQL table? 1. Use SQL Alchemy. In this library, you can create you use PYODBC in tandem with sqlalchemy to establish a connection. Then you use the Pandas data frame "to_sql" method to write the data frame to the table. Here is an example that will create a new table and show the difference between appending and replacing: # import the libraries import sqlalchemy import pyodbc # create a connection, might be different if you're using a trusted connection. engine = sqlalchemy.create_engine("mssql+pyodbc://:@") # Insert data to a new table df.to_sql("my_new_table", engine) # Append data to an existing table. df.to_sql("my_existing_table", engine, if_exists = 'append') # Replace table and then insert data. df.to_sql("my_exisiting_table", engine, if_exists = 'replace') 2. Loop through the data frame and insert directly: In this case, you create a connection as you do in any of the examples, but then we will specify an insert statement that we will execute for each row in the data frame. Imagine I had a data frame with 3 columns first_name, last_name, and age. # here's my data frame my_df = pd.read_excel("my_excel.xlsx") # loop through each row, in my data frame. for index, row in df.iterrows(): cursor.execute("INSERT INTO dbo.my_table ([first_name], [last_name], [age]) VALUES (?, ?, ?)", (row['first_name'], row[last_name'], row['age']) # commit the insert conn.commit() # close the cursor cursor.close() # close the connection conn.close() 3. Do a bulk insert To do a bulk insert, you would have to convert the data frame to a numpy recordset. However, you must be very careful with this as numpy has datatypes that SQL server can't handle, so you'll need to make sure that recordset has the proper data types. I've only done this once, and I can't find that example I did, but it is possible. You just have to do some planning. If you need help during the process feel free to reach out through email at coding.sigma@gmail.com.
Hi, maybe you have an example using UID (username) and PWD (password)? I'm using ODBC Driver 13 for SQL Server and can't pass DOMAIN\user because I have the error: Error: ('28000', "[28000] [unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'DOMAin\\username'. (18456) (SQLDriverConnect)") the main issue is: backslash is not possible to pass to connection string? this' my connection: cnxn= pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+user+';PWD='+ password) I'd really appreciated your comments Regards,
I would recommend you do the following: # to fix the double backslash wrap it in r'', meaning the raw string. my_server = r"ServerName" # declare the rest of your variables my_database = "DatabaseName" my_userid = "UserID" my_password = "Password" # connect to the server, using the key word alternative approach. connSqlServer = pyodbc.connect(driver='{ODBC Driver 13 for SQL Server}', server=my_server, database=my_database, uid=my_userid, pwd=my_password) Let me know if that fixes the issue for you.
@@ebinjose1015 All you would do is just change the query you execute. For example, this would change from this. *cursor.execute('SELECT * FROM td_price_data')* To this. *cursor.execute('SELECT Date FROM td_price_data')* You're just changing the SQL query that's all.
@@SigmaCoding I think he was asking how to return the column headers in the result set, so the first row would be all the column headers/names, and then each subsequent row would be the result set.
@@bryantTheFatBadger Gotcha in that case to get all the column names you would want to write the following for the cursor: columns = [column[0] for column in cursor.description] This would create a list of column names for you. After that, the code would be the same.
Question, when you setup the table in the database did you specify an identity column? One with a primary key that won't allow for duplicates? Or are you asking how would you do that using PYODBC?
@@SigmaCoding my primary key is not unique, it is duplicated in one table.example, many data are under one series number. Therefore, i dont know how to code prevent duplicated data when new entry coming in with using PYODBC?
@@jovischuah4031 Okay, so you'll need to devise a way for generating a unique primary key each time the data is added. Are there any columns that you can use that can serve as the primary key? Like a DateTime or something like that? Also, to insert multiple rows at once in a "quick" fashion, you need to set an attribute first. You need to add the following line: cursor.fast_executemany = True and then call the following: cursor.executemany(insert_query, values) where the insert query is the query string you define in your code, and the values are the values you wish to insert.
@@jovischuah4031 You'll need to create a unique key somehow then. You can generate one or combine multiple columns so you can create one. The end goal is just to create one.
Excellent video. I followed along and was able to connect and executed SQL. Thank you for sharing!
This video was the most useful resource I found about pyodbc. Thank you!
Thanks a lot for this video. It was very helpful in setting up the connection as I was struggling with it for a long time. You explained everything in an easy to understand manner. Thanks again!!
Glad it helped!
@@SigmaCoding How to connect with Amazon Aurora Mysql server using PyODBC and not SQLalchemy or PyMysql
thanks brother.....you are amazing.....been trying to connect azure sql with django from while....finally its done....thanks
Liked and subscribed. You are amazing at teaching.
Thanks Buddy, great video !
WOW AMAZING!! I learned so much, especially all the nice tips like showing all your drviers before connections.
Yeah, this library is excellent and gives you access to so many tools. The only challenge I've seen so far with it is the type of driver you need to work with. When you have both 64-bit and 32-bit applications, things can get wacky.
Thank you so much. Excellent explanation and worked example :)
You're welcome!
Great video!!
Very useful Sr. Thank you.
finally. I could run this. Thanks alot
Helped a lot thanks
Awesome stuff !!!!!!!!
:)
Thanks for this
You are amazing!!!!!!
good one. Thanks !
Glad you liked it!
great, thanks a lot
I’m trying this method to import data from a excel file with 15 columns in a sql table with same 15 columns. I’m getting an error “string index out of range” in the line values = (row[0], row[1].....row[14]). How to fix this?
Hey , thanks a lot for this video.It helped me alot. Though i not able to retrieve an image saved in sql server as image format .I would really appreciate some help. Thanks . Good work Dude!
Thanks.
hey!! great video! just a quick question though, what if I have a large table in json and crazy amount of columns, any easier way that coding every column?
Grt video and if you could create a video on exporting a data frame to sql server through python
Hi, I am struggling with executing a MS sql job using PYOdbc. Can you please help on how to call sql job using pyodbc?
Hi, Thanks for the video,
I created a access database to store value fetched from xpaths, i have to delete the stored values in my table to use the table freshly for the next run in a loop after storing the data in a excel source file. I used 'TRUNCATE TABLE table_name'', but its showing invalid syntax near Table.
What can i do here? please help..
Do we know how to authenticate using Azure AD vs Windows credentials (as per the video) when connecting to Azure SQL?
if my data is not in a matrix (list with list elements) but in a dataframe, do I need to convert my pandas dataframe to a matrix like in your example in order to insert the data into a SQL Server table?
Per the docs,
"Connections are automatically closed when they are deleted (typically when they go out of scope) so you should not normally need to call [conn.close()], but you can explicitly close the connection if you wish.
"
and similarly for cursors:
"Cursors are closed automatically when they are deleted (typically when they go out of scope), so calling [csr.close()] is not usually necessary."
If I understand correctly, what you're saying is that there is no need to close the connection? See, that's strange because on my work laptop, when I do anything related to our SQL database using PYODBC, I always have to close out the connection.
If I don't, I have all these "sessions" that are still left open on the server. Now I may be doing something wrong on my end that's causing the session not to end, or maybe I'm misinterpreting what the server is telling me. However, it's strange that they say you don't need to close anything explicitly.
@@SigmaCoding I always close connection. Like garbage collection may be automatic but better have a safe practice. BTW, great explanation.
Great vid!
Just a question, do you bother with rollback at all? Or does de odbc deal with it automaticly?
Also if you have a pandas df not list,
you need to use df.values.tolist()
to convert to list, and than df = df[1::] to remove headers from list
cnxn = pyodbc.connect()
Can you explain why connect() is a method? From the camel casing, it looks like a function. Also within the module its also defined like a function. Not sure why its being instantiated like an object of a class. Thanks
I am trying to run a merge query with Pandas and pyodbc and Im not sure how to go about doing that any help?
Thanks for the video. It was super useful. Can you please help with adding a whole dataframe to SQL Server table? Table already setup in SQL server. I see some dataframe.To_SQL method but can't get it to work.
i have a python dash app deployed on Azure apps. When it accesses SQL Server it leaves the connection open.
Yes i explicitly closed, shut of connection pools as well
Thanks for the video. I followed the same exact steps and got an "invalid escape sequence \s" on the line where you set the server variable. how are you not getting that same error?
Hi, the data fetched from mssql are coming with (") and ' and , included how to avoid it
thanks for the video
But when I have a dataframe with a lot of columns, it's not pleasant to use this method to insert. It would take hours...
Great video, thank you. Was hoping to hear more about how/why the 'for row in cursor' works given the cursor object returns something along the lines of so not totally clear why or how the for loop is able to access the rows just b/c the cur.execute(select *) statement is included. printing cursor variable even after the cur.execute is performed doesn't return a single line with the array.
Well keep in mind that it's not technically an array in python. It's a Cursor object, and that cursor object just so happens to have a mechanism that allows you to iterate through it. My guess is there is probably a "__getitem__" method which allows you to loop through the returned values.
I know this might sound confusing, but the giveaway is the fact you're seeing a "Cursor" object. That to me says it's not a list, so it could have its own "__repr__" method and stuff like that.
How to check if before insertion if record already exists to avoid duplication ?
Hi I am getting error installation of pyodbc how can I solve this error it requires c++ compiler
Hello, quick question here
I'm making a gui where the user enters the server name and then I'll list all databases etc
how to I config the connection like
mydb = pyodbc.connect(server="";)
and then in a function I'll get the input from the user and configure the connection to that server like
mydb.config(server=userinput.get())
seems that pyodbc has no attribute config so what can I use instead
I'm a little confused about what you're trying to do. Are you trying to ask for the Server name and then use that information to query all the databases in that server to then populate that in the GUI? If that is the case, here is the connection string and query.
# Connection String.
cnxn = pyodbc.connect(driver = 'Driver', server='server', database='master', trusted_connection='yes')
cursor = cnxn.cursor()
cursor.execute("SELECT name FROM sys.databases")
Hey thanks for the video! I am running into this issue:
return pyodbc.connect(
pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
I dont quite understand what I should fix based of of this error.
Very nice and detail explanation! Appreciating efforts! What if it is SQL authentication credentials require instead of windows authentication?
Additional question, query output is not displaying column names. I want to export the query output in pandas dataframe. Do I need to insert the columns names in pd manually, or there is a way to populate column names with query result ?
I keep getting invalid object name, I ran the query in the microsoft sql manager no problem, but in python I get the error.
I have 8 columns and while i pass all the values , it gives me error: "The SQL contains 0 parameter markers, but 8 parameters were supplied', 'HY000".. Please help..
Do you have the code?
How can I connect to SQL and read a file that contains SQL Insert statement and write the same to tables already existing in SQL.
In essence, all you would do is read the file and then execute the query inside of it. However, be careful because it will need to be formatted a certain way sometimes. For example, if you want to just read they file and store the "query string" in a variable it would look something like this:
sql_file = "my_sql_query.sql"
sql_query = open(sql_file, mode='r', encoding='utf-8-sig').read()
print(sql_query)
Also note the encoding, you may or may not need this depending on how the file is, but for safety I usually put it in there.
I installed the pyodbc on a MAC M1 but I can not see any drivers in for loop:
import pyodbc
for driver in pyodbc.drivers():
print(driver))
any idea?
When I run the print(drivers) section it does not return anything, not even an error. Does this mean I do not have any drivers installed?
Potentially, what version of PYODBC did you install the 32 bit version or 64 bit version?
if i want to insert data from api, how do i do that? thank you
So you notated the server name here for your server in your example.
However, what’s the alternative I’f say you’re using SQLite which is a serverless, and pretty much SQLite dumps into simple file copies? How you pass it through and connect to SQLite?
So keep in mind I've never actually connected to an SQLite database using PYODBC but supposedly you can from reading the documentation. Here is how the code will look:
cnxn = pyodbc.connect("Driver=SQLite3 ODBC Driver;Database=sqlite.db")
Here is the link to where I got the code from github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQLite
He does mention you may not even need the driver at all. However, he doesn't give an example as to how the code will change if you don't reference the driver.
After connecting how to make it as a dataframe.
Please tell me
Hi, Thanks for the video. I am now trying to set up the pyodbc and make connection with SQL server on MACos. I already installed odbc driver but when I ran 'pyodbc.drivers()' on jupyter nootbook, it returned empty list []. Do you know how to solve this problem?
For macOS the PYODBC library is very different compared to windows. I think you can still connect to a SQL Server instance but I don't think you can specify any driver because macOS doesn't install any.
You might want to look at this: github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX
what if we have 100 columns do we have to write all columns name in insert into Statement.
Technically you don't have to specify the column names in a insert query, you just then have to make sure that the info is in the correct order when you insert it.
Hello friend. I am getting error after finish coding - pyodbc.ProgrammingError: ("A TVP's rows must be Sequence objects.", 'HY000'). And using pycharm app. Could you help me please?
Great Video!. Can you please clarify, how windows authentication will work when we connect to SQL server hosted in Azure VM.
Excellent tutorial!! I don't see the excel drivers within pyodbc. How do I install Excel or any other missing pyodbc drivers?
It can be found here: www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255
Which version of visual studio c++ compiler required and what is prerequisite of this code run
I honestly couldn't tell you at this point I made this video a while ago so I don't remember. What error are you getting though?
Hey man awesome video. im just having trouble actually using pyodbc. i did eveything like you did. but it doesnt see the attributes of the pyodbc. Any suggestions?
What do you get instead or is it just returning an error?
How to pass Null as parameter. To fetch columns which have null values?
A NULL value is just None inside of Python. So if you want the value to be NULL then just pass through None.
Hey, how can I get the PK id after insertion data? sql = "INSERT INTO FORM SELECT " + str(form.FK_Customer) + " , '" + form.Description_Form + "' , 1019,getdate() SELECT @@IDENTITY AS ID"
row = cursor.execute(sql)
for r in row:
print(r)
cursor.commit()
I am trying to use the code above, but it is not working
What's the error you're getting? Also, is it the insert query that's not working or the select query? I can't tell if they're two separate queries that you run independently or together.
Also, be careful using a GetDate() function in the query. What exactly is that doing, and you verified that the table can accept the date data type, right? Dates can cause all sorts of messes if not used correctly. Like the use of the "@@Identity" system function.
first i would like to thank you
but i am having some simple error after defining connection string
('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Thank you for the video! Do you have a video showing how to load a whole dataframe into a table rather than row by row?
Not yet, and PYODBC is weird with this topic because even if you use the "executemany()" method, like they specify in the documentation. What you'll find is it still inserts it row by row. The work around I do is to create a string where I do a bulk insert of tuples, to "bypass" it.
use python pandas
hello, I followed and tried your tutorial but im getting and error in the part of assigning data to values. the error is string index out of range.
let me know your thoughts. thanks
btw, may data source is a dataframe
@@anjofamini5697 Hi Anji. did y solve the problem please ? thanks
@@omardagdoug6409 , yep, but I used other kind approach. using sqlalchemy
@@anjofamini5697 Yes I've seen sqlachemy and to_sql approach. My problem is that I have to use SQL server as DB, and I couldn't fix the out of range problem.
Thanks Bro for replying ;))
Thanks man, I had a mistake when I tried to put the ip in the server parameter. I have a doubt, which is better SQL Server Native Client 11.0 or ODBC Driver 17 for SQL Server?
It depends, for me I seem to have less issues with the SQL Server Native Client and that seems to work more broadly across different systems.
@@SigmaCoding Thanks man. And in the case of ip? is it possible to put the ip in the variable server?
@@SigmaCoding Is SQL Server Native Client 11.0 free or licensed?
Awesome Video !
Question ?
plttrackingconn = pyodbc.connect("Driver={SQL Server};"
"Server=10.98.97.101;"
"Database=Tracking;"
"Trusted_Connection=yes;"
"ApplicationIntent=ReadOnly;")
'Tracking' is in an availability group and is currently accessible for connections when the application intent is set to read only.
Not working for me
this method INSERT very slowly, can you create a video on how to do fast_executemany
Is it possible use SQL database Without Installing Microsoft SQL Server Management Studio using python? For example i must install MSQL SS, create server, make database, create tables... Can i do that without install MSQL SS and using only python?
I think what you're talking about is an in-memory database, something similar to what SQL Alchemy offers. I don't think PYODBC offers such functionality,.
@@SigmaCoding Thanks, I'll try with SQL Alchemy
How to connect with Amazon Aurora Mysql server using PyODBC and not SQLalchemy or PyMysql
Hi @SigmaCoding , I'm trying to connect to Microsoft SQL production database using pyodbc , but I'm getting an error "[DBNETLIB]SSL Security error (18) (SQLDriverConnect) ][DBNETLIB]ConnectionOpen (SECDoClientHandshake())" ,I tried searching it everywhere but I couldn't find the useful. While connecting to Microsoft SQL Testing database , I'm easily connecting to it and getting the required output in my Dataframe, please help......................
Hi Nikhil, are you using window's authentication to login or are you providing a username and password? Also is this a local server?
@@SigmaCoding windows authentication and yes on local
I tried many things bro , but still not able to do so, I will email you the solutions I tried . Please help me to solve this , I had almost written 1000 lines of code for my automation use case using pandas ,it starts with connecting to testing DB and then fetching 5 input tables and using them for further operation . So as of now for production , exporting DB data to excel and then reading this using pandas and continuing my project.
@@nikhilpunde6410 Alright, just email me at coding.sigma@gmail.com
@@SigmaCoding sent you sir
Hi. Just wondering, why do you choose pyodbc over pypyodbc?
No in particular reason, it was just the first library I was exposed to at my job that interacted with ODBC. I've heard of pypyobdc and have heard good things about it, it's basically pyodbc just in pure python.
@@SigmaCoding Sorry a random question out of nowhere, do you live in Taiwan by any chance?
@@SeeCoolThings I do not, I am currently living in San Diego.
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; \
SERVER=' + server + '; \
DATABASE=' + database +';\
Trusted_Connection=yes;')
Im getting this error for my connection string above:
File "", line 20
SERVER=' + server + '; \
^
SyntaxError: EOL while scanning string literal
Thanks a lot bro.. i wasted one full day just because of insert query ....
Can you please tell how to import python pyodbc library in Jupyter Notebook!
It's just import pyodbc. That assumes you have it installed though.
how to deal with csv, for example your sales_list is an csv file, and insert all row?
Are you talking about doing a bulk insert? Meaning you insert multiple rows at once?
@@SigmaCoding found answer, you helped. now another problem is I run this everyday. But only want to save 2 days record. Meaning when i run at tomorrow, i need to delete record from yesterday. You could shed some light on this? thanks
@@JonathanJournal Lot's of ways to approach this, but the most simple one is that when you insert your data, you add a timestamp for when added. What this will do is provide a way to see what data was entered when. All you would do is every time you startup this script is have a "DELETE" query that will delete records that match the previous day's date. That means you'll need to add a "WHERE" condition that will filter the records to be removed. Just keep in mind, once it's deleted, it's deleted; there is no going back. In other words, make sure you really want to delete historical data.
@@SigmaCoding how do you deal with dataframe, and write to microsoft sql server?
@@JonathanJournal If you want to write a Pandas data frame to SQL Server, there are a few ways to achieve this. However, will you be writing to an existing table, or will this data frame create a new SQL table?
1. Use SQL Alchemy.
In this library, you can create you use PYODBC in tandem with sqlalchemy to establish a connection. Then you use the Pandas data frame "to_sql" method to write the data frame to the table. Here is an example that will create a new table and show the difference between appending and replacing:
# import the libraries
import sqlalchemy
import pyodbc
# create a connection, might be different if you're using a trusted connection.
engine = sqlalchemy.create_engine("mssql+pyodbc://:@")
# Insert data to a new table
df.to_sql("my_new_table", engine)
# Append data to an existing table.
df.to_sql("my_existing_table", engine, if_exists = 'append')
# Replace table and then insert data.
df.to_sql("my_exisiting_table", engine, if_exists = 'replace')
2. Loop through the data frame and insert directly:
In this case, you create a connection as you do in any of the examples, but then we will specify an insert statement that we will execute for each row in the data frame. Imagine I had a data frame with 3 columns first_name, last_name, and age.
# here's my data frame
my_df = pd.read_excel("my_excel.xlsx")
# loop through each row, in my data frame.
for index, row in df.iterrows():
cursor.execute("INSERT INTO dbo.my_table ([first_name], [last_name], [age]) VALUES (?, ?, ?)", (row['first_name'], row[last_name'], row['age'])
# commit the insert
conn.commit()
# close the cursor
cursor.close()
# close the connection
conn.close()
3. Do a bulk insert
To do a bulk insert, you would have to convert the data frame to a numpy recordset. However, you must be very careful with this as numpy has datatypes that SQL server can't handle, so you'll need to make sure that recordset has the proper data types. I've only done this once, and I can't find that example I did, but it is possible. You just have to do some planning.
If you need help during the process feel free to reach out through email at coding.sigma@gmail.com.
Hi, maybe you have an example using UID (username) and PWD (password)? I'm using ODBC Driver 13 for SQL Server and can't pass DOMAIN\user because I have the error:
Error: ('28000', "[28000] [unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'DOMAin\\username'. (18456) (SQLDriverConnect)")
the main issue is: backslash is not possible to pass to connection string?
this' my connection:
cnxn= pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+user+';PWD='+ password)
I'd really appreciated your comments
Regards,
I would recommend you do the following:
# to fix the double backslash wrap it in r'', meaning the raw string.
my_server = r"ServerName"
# declare the rest of your variables
my_database = "DatabaseName"
my_userid = "UserID"
my_password = "Password"
# connect to the server, using the key word alternative approach.
connSqlServer = pyodbc.connect(driver='{ODBC Driver 13 for SQL Server}',
server=my_server,
database=my_database,
uid=my_userid,
pwd=my_password)
Let me know if that fixes the issue for you.
How to select a table with it's column name in python ?
I'm a little confused by the question. Are you asking how to select a specific column from the table?
@@SigmaCoding yes, because I am able to see the dataset without column names.
@@ebinjose1015 All you would do is just change the query you execute.
For example, this would change from this.
*cursor.execute('SELECT * FROM td_price_data')*
To this.
*cursor.execute('SELECT Date FROM td_price_data')*
You're just changing the SQL query that's all.
@@SigmaCoding I think he was asking how to return the column headers in the result set, so the first row would be all the column headers/names, and then each subsequent row would be the result set.
@@bryantTheFatBadger Gotcha in that case to get all the column names you would want to write the following for the cursor:
columns = [column[0] for column in cursor.description]
This would create a list of column names for you. After that, the code would be the same.
hi , is anybody here know how do code prevent duplicated data?
Question, when you setup the table in the database did you specify an identity column? One with a primary key that won't allow for duplicates? Or are you asking how would you do that using PYODBC?
@@SigmaCoding my primary key is not unique, it is duplicated in one table.example, many data are under one series number. Therefore, i dont know how to code prevent duplicated data when new entry coming in with using PYODBC?
one more question is how to speed up insert query(10 thousand rows++)?could you give me some hint?
@@jovischuah4031 Okay, so you'll need to devise a way for generating a unique primary key each time the data is added. Are there any columns that you can use that can serve as the primary key? Like a DateTime or something like that?
Also, to insert multiple rows at once in a "quick" fashion, you need to set an attribute first. You need to add the following line:
cursor.fast_executemany = True
and then call the following:
cursor.executemany(insert_query, values)
where the insert query is the query string you define in your code, and the values are the values you wish to insert.
@@jovischuah4031 You'll need to create a unique key somehow then. You can generate one or combine multiple columns so you can create one. The end goal is just to create one.
my pyodbc drivers is empty
Please help
I have mac m1 apple chip
i think it's Oracle Data Base Connectivity