Lo-Fi Python

When Microsoft Was Uncool and How They Flipped Apple

I began paying attention to what was relevant in the tech scene around 2014, in my 20s. Back then, I was just getting started studying Python. It was an interesting time in tech. The term "Big Data" was getting tossed around a lot, but the pandas library hadn't yet reached mass adoption in data circles like it now has today. People were still talking about Hadoop + Map Reduce. (RIP)

In the 2010s, it didn't take much perusing online to find people in the Python community bashing Microsoft. If tech companies were a high school, Apple was the cool kid everybody wanted to know, Microsoft was the kid who nobody liked and everyone made fun of. Understandably, the Windows operating system didn't mesh with Python programming as well as Linux or Mac OS. By 2024, Microsoft gained their mojo back, or found the mojo they never had. Having used Windows a lot at my last job, I recognize the OS and its Python implementation have flaws. I still got my work done and had no problems, without complaining. I continued to play around on Windows and write Python on it even though people trashed it online. I'm glad I did!

How did Microsoft flip Apple? Steve Ballmer left the company in 2014, yielding to Satya Nadella as CEO. Since then, the company culture shifted miraculously. In the Python community, they have made a huge impact by investing in the language. They constantly release free Python + AI courses, and integrated Excel with Python. Guido, the creator of Python is employed full-time, working on improving the Python language. That tells you a lot of how much has changed since Python's BDFL is still working there after 3 years. Microsoft's culture change propelled it into the 2020s with newfound momentum. With some timely bets, they saw the AI revolution coming and capitalized first.

If someone feels this way in 2024, they probably don't want to admit: Microsoft is Apple in 2012, and Apple is Microsoft in 2012.

What is funny to see is that nowadays, fewer people are bashing Microsoft. I used to see it regularly, people teeing off online, "writing Python on Windows is such a terrible experience for XYZ thing, why is Windows so awful??"" I see less of those people posting such thoughts now. Maybe they're still out there. If someone feels this way in 2024, they probably don't want to admit: Microsoft is Apple in 2012, and Apple is Microsoft in 2012. I posit they switched places in respective coolness among tech circles. People realized Apple is not the friend of developers or society in general. They are self-serving to a vicious degree. Apple is focused on maintaining their walled garden on iOS.

Microsoft is now a better advocate for techies and Python development. Sure, some people prefer to code on Macs, more power to them. Linux is typically the favorite of the three and it is awesome. It's also not released by a for profit corporation which is uber cool to developers.

Apple is also less cool due to their battle with Epic Games and insistence on 30% rake for in-app purchases on iOS. Not to mention an unwillingness to change their policies to appease stricter European Union regulations for things like 3rd party app stores.

Microsoft is integrating AI deep into their products. Apple, after being slow on the uptake to AI, followed Microsoft's lead to invest in OpenAI and roll its AI chat to iPhones. Who is the leader here? In terms of "What have you done for me lately?", it's Microsoft. In terms of who supports open and free information, it's Microsoft. Who's cool now?

Inserting New Rows Into A Microsoft Access Database With Python and pyodbc

I recently automated the loading of data into a Microsoft Access database with pyodbc, a Python library for connecting to databases. ODBC stands for Open Database Connectivity. It can be used for a variety of Database Management Systems outside of Access also.

First, install libraries with pip. Enter in terminal or command prompt:

python -m pip install pyodbc
python -m pip install pandas

Next, check available Microsoft Access drivers on your computer. Enter the below statements into the Python interpreter:

1
2
3

python
>>> import pyodbc
>>> [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]

Drivers for Access & Many Other Data Sources

The driver is the engine that allows you to connect to a specific type of database. The drivers available vary depending on your machine.

The two most common drivers for Access are Microsoft Access Driver (.mdb) and Microsoft Access Driver (.mdb, *.accdb). My computer only had *.mdb, which has been deprecated. My Access database was a .mdb file, so I was able to use this driver as shown below. Read more on Access drivers here.

"Drivers exist for all major DBMSs, many other data sources like address book systems and Microsoft Excel, and even for text or comma-separated values (CSV) files."  - Wikipedia

Database Data Types

I set all of the field data types to "Short Text" because I'm passing strings as SQL parameters below. Uploading as other data types may require additional formatting. To edit the data types of your table, open the table and select "Design View" under the "Home" tab. It got the job done for me!

Inserting new rows into a Microsoft Access Database:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

import pandas as pd
import pyodbc


def df_to_row_tuples(df):
    """Use list comprehension to format df rows as a list of tuples:
    rows = [('Garfield','Orange','Eat'),('Meowth','White','Scratch')]
    """
    df = df.fillna('')
    rows = [tuple(cell) for cell in df.values]
    return rows

"""Rows are not added to DB until they are committed.
Pass each row tuple as a SQL parameter (?,?,?).
cursor.execute docs: https://www.mcobject.com/docs/Content/Programming/Python/Classes/Cursor/execute.htm
"""
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Add_Path\To_DB\Here\Your_DB.mdb;')
cursor = conn.cursor()
sql = ''' INSERT INTO Cats (Name, Color, Move)
          VALUES(?,?,?) '''
df = pd.read_csv('Cat Data.csv')
rows = df_to_row_tuples(df)
for row in rows:
    cursor.execute(sql, row)
conn.commit()

Conclusion

Running the above in command prompt uses pyodbc and SQL to add dataframe rows to a Microsoft Access DB table named "Cats". Passing each row as a SQL parameter has two benefits:

1. It handles strings with single quotes (') and loads them to the DB.
2. It protects against SQL injection attacks.

Access Limitation Disclaimer

Access topped out just shy of 10 million rows in my use case, when records stopped getting added to my database. So keep that in mind if you're thinking about using Access to store your data.

Supplementary Resources

Insert Values into MS Access Table using Python

pyodbc documentation

Microsoft Documentation pyodbc example

The Python Cursor Class

Psycopg Cursor Class Documentation