This was a very helpful video and super intuitive I just want to say thank you for staying up till 1:00 a.m. to explain this so I don't have to! Much appreciated ❤️
Just a thought for any deletes you will perform. If you first do a Select, you can visually confirm that you will be deleting the right data. Do Select....where.... If you are satisfied with the selected data, just change the statement from select to delete from..... keeping the where. I started doing this after accidentally losing a table full of data I was playing with. It is a good habit to get into. The fact that you are using commits helps, but if you don't realize the selection, you can, out of habit or forgetfulness, execute a commit before you realize your mistake.
Do you have any idea how to grab all the queries from a file, across multiple files and for inspection of the queries text, not the tables returned by the queries?
Very useful, thanks. I wrote some python code to take in a log file and parse some info from it using pandas. It turned out the log file had been exported from MS Access so I want to update it to work with the log file or access file as an input and this looks perfect to get the database into Pandas, and then the previous code should still work to parse the info in the table.
Hi, when I try to use pandas I get this error, any ideas? UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
Thanks for dropping by. A "UserWarning" usually doesn't cause too much harm and your program should still run. Can you share the code where you got this warning message?
@@pythoninoffice6568 Thanks for replying, I didn't notice that I was able to get the query and it was just a warning, do you know how do I get rid of the warning? I am using Python 3.10 on Windows
Hope I'm interpreting your question correctly - you mean take the new table name from excel and use it as an input to create a new table in Access? You'll have to figure out a way to get the table name from Excel. Maybe you want to just use the sheet name as the table name? Or you could come up with your own naming convention. let's say you store the name into a variable named "table_name", then when you create a new table inside Access, use an f-string to construct the SQL statement: f'create table {table_name} (col1, col2, col3)' This way you can pass the table_name value into the SQL statement. Then create a table with the desired names. Hope that helps, if anything is unclear let me know!
@@pythoninoffice6568 its kind off correct the question is that after create table we write the name of the new table.I want take the new table's name as an input.Hope the clarifaction helps and ur answer is useful too for a new feature.
@@highschoolboiiii7936 Thanks for clarifying. I think my answer will stay the same: In order to create a table we have to have a name for it, and you can come up with that naming convention first, then store the table name inside a variable. This way you can later use the f-string to construte SQL for creating the table, and you still have the name stored in this variable for later / other input that you want to use. Hope that makes sense, let me know.
Hi! Thanks for your question! If I understand you correctly, you are referring to the queries inside MS Access? The pyodbc library allows us to make a connection to the Access database but not really "controlling" it. i.e. We can't interact with the MS Access program. Therefore, I don't think you can select the query inside an Access file. From my understanding, the "query" functionality is meant for less technical people who can use a drag & drop method to analyze data. The real querying part is probably still achieved by SQL under the hood. If you have an existing Access database that contains some queries that you want to use, I suggest you can replicate that query inside Python, then you can run it without needing to worry about the "query" inside Access. Hope this helps, let me know if you need any clarification!
I like this video, but I get an error ProgrammingError: ('The SQL contains 21 parameter markers, but 77 parameters were supplied', 'HY000') when I try to upload a dataframe to access. Not sure where I am going wrong.
Long story short, keep only 1 python version if you can, will save you headaches down the road :) When you have multiple python versions installed, "pip install" is usually going to use the first python version (and pip) registered with your computer... To find out what version that is: in the command prompt type "pip --version" which is going to show you the python associated with the current pip. Let's call this python_v1. The easiest way is to use this python_v1 to run your code since it already has the pyodbc if you have installed it before. However, if you must use another python version (that doesn't have pyodbc yet) to run your code, you need to find the installation folder for the other version, let's call it python_v2. Inside this python_v2 installation folder, go to the "Scripts" subfolder, where you'll see the pip programs. copy this folder path, head back to command prompt, paste the folder path, then add "\pip install pyodbc". It looks something like this: >C:\Users\jay\AppData\Local\Programs\Python\Python310-32\Scripts\pip install pyodbc Doing the above will make sure you use the other python version and the associated pip for the installation for your other python versions. Hope that helps.
This was a very helpful video and super intuitive I just want to say thank you for staying up till 1:00 a.m. to explain this so I don't have to! Much appreciated ❤️
Perfect explanation, very useful for me.
Just a thought for any deletes you will perform. If you first do a Select, you can visually confirm that you will be deleting the right data.
Do Select....where....
If you are satisfied with the selected data, just change the statement from select to delete from..... keeping the where.
I started doing this after accidentally losing a table full of data I was playing with. It is a good habit to get into.
The fact that you are using commits helps, but if you don't realize the selection, you can, out of habit or forgetfulness, execute a commit before you realize your mistake.
That's a good tip! It's good to confirm what you want to delete first!
Do you have any idea how to grab all the queries from a file, across multiple files and for inspection of the queries text, not the tables returned by the queries?
Hi very helpful video! Just a small question, what if i want to export a different object than tables like an MS Access report . How can I do that?
Very useful, thanks.
I wrote some python code to take in a log file and parse some info from it using pandas.
It turned out the log file had been exported from MS Access so I want to update it to work with the log file or access file as an input and this looks perfect to get the database into Pandas, and then the previous code should still work to parse the info in the table.
Access can be user friendly for non-coders, but huge disadvantage if you have large amount of data :)
Hi, when I try to use pandas I get this error, any ideas? UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
Thanks for dropping by.
A "UserWarning" usually doesn't cause too much harm and your program should still run. Can you share the code where you got this warning message?
@@pythoninoffice6568 Thanks for replying, I didn't notice that I was able to get the query and it was just a warning, do you know how do I get rid of the warning? I am using Python 3.10 on Windows
Can you please paste your code here so I can take a look?
Thank you
how do i take the new table name as a input and create a new table??
Hope I'm interpreting your question correctly - you mean take the new table name from excel and use it as an input to create a new table in Access?
You'll have to figure out a way to get the table name from Excel. Maybe you want to just use the sheet name as the table name? Or you could come up with your own naming convention.
let's say you store the name into a variable named "table_name", then when you create a new table inside Access, use an f-string to construct the SQL statement:
f'create table {table_name} (col1, col2, col3)'
This way you can pass the table_name value into the SQL statement. Then create a table with the desired names.
Hope that helps, if anything is unclear let me know!
@@pythoninoffice6568 its kind off correct the question is that after create table we write the name of the new table.I want take the new table's name as an input.Hope the clarifaction helps and ur answer is useful too for a new feature.
@@highschoolboiiii7936 Thanks for clarifying. I think my answer will stay the same:
In order to create a table we have to have a name for it, and you can come up with that naming convention first, then store the table name inside a variable. This way you can later use the f-string to construte SQL for creating the table, and you still have the name stored in this variable for later / other input that you want to use.
Hope that makes sense, let me know.
@@pythoninoffice6568 cursor.execute (f'create table{Table_Name}') my syntaxits showing an syntax error
hello python in office i need some help teh reply doesnt work its showing a syntax error.can i please get some assistance
Hi!, in the access file we have Query option.
How to select that object in python?
Hi!
Thanks for your question! If I understand you correctly, you are referring to the queries inside MS Access?
The pyodbc library allows us to make a connection to the Access database but not really "controlling" it. i.e. We can't interact with the MS Access program. Therefore, I don't think you can select the query inside an Access file.
From my understanding, the "query" functionality is meant for less technical people who can use a drag & drop method to analyze data. The real querying part is probably still achieved by SQL under the hood.
If you have an existing Access database that contains some queries that you want to use, I suggest you can replicate that query inside Python, then you can run it without needing to worry about the "query" inside Access.
Hope this helps, let me know if you need any clarification!
@@pythoninoffice6568
Thanks a bunch, you have cleared all my doubts....
My access is 32 bit and python is 64....they arent connecting..what to do/???
You have to use the same version for both Access and Python. In your case it's probably easier to reinstall another Python but the 32-bit version.
form where i get the connection string
Sorry for the late response. The connection string is just the file path.
sir pyodbc access database connect error pyodbc interface error default sql driver error access database connection issue
Can you post your connection string and the exact error message?
i want to learn python
Learning Python is fun and it will be your life saver once you know how to use it! Just do it!
I like this video, but I get an error ProgrammingError: ('The SQL contains 21 parameter markers, but 77 parameters were supplied', 'HY000') when I try to upload a dataframe to access. Not sure where I am going wrong.
Post your code here and I can help take a look.
@@pythoninoffice6568 I figured it out, but I used executemany instead of execute. Now, the problem I have is in access not the code.
Modulenotfound: no Module named: „pyodbc“
I needed to download ace driver 2010 because 2016 didn’t worked
And I had two python versions installed
Try install the library first. In a command prompt window type:
pip install pyodbc
@@pythoninoffice6568 I did. multiply times like I said in the other comments I had multeple pythons version
Long story short, keep only 1 python version if you can, will save you headaches down the road :)
When you have multiple python versions installed, "pip install" is usually going to use the first python version (and pip) registered with your computer... To find out what version that is: in the command prompt type "pip --version" which is going to show you the python associated with the current pip. Let's call this python_v1.
The easiest way is to use this python_v1 to run your code since it already has the pyodbc if you have installed it before.
However, if you must use another python version (that doesn't have pyodbc yet) to run your code, you need to find the installation folder for the other version, let's call it python_v2.
Inside this python_v2 installation folder, go to the "Scripts" subfolder, where you'll see the pip programs. copy this folder path, head back to command prompt, paste the folder path, then add "\pip install pyodbc". It looks something like this: >C:\Users\jay\AppData\Local\Programs\Python\Python310-32\Scripts\pip install pyodbc
Doing the above will make sure you use the other python version and the associated pip for the installation for your other python versions.
Hope that helps.