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.

Glad it helped!

@@SigmaCoding How to connect with Amazon Aurora Mysql server using PyODBC and not SQLalchemy or PyMysql

You're welcome!

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.

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 ?

Glad you liked it!

Do you have the code?

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.

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.

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.

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 ;))

:)

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

It can be found here: www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255

A NULL value is just None inside of Python. So if you want the value to be NULL then just pass through None.

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 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

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?

What do you get instead or is it just returning an error?

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

Potentially, what version of PYODBC did you install the 32 bit version or 64 bit version?

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.

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")

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'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.

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.

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?

I have mac m1 apple chip

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.

It's just import pyodbc. That assumes you have it installed though.

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?

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.