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.
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.
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 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?
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 ?
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?
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.
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.
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
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..
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..
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.
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
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.
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.
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
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?
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.
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?
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.
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,.
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?
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 ;))
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.
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?
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.
@@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.
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")
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
@@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.
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.
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.
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.