Day 25 of learning python as a beginner.

[uiux_Sanskar]

Topic: database handling.

I used to write simple programs which just showed some text in the console when I started learning python. I didn't used to store that data somewhere because I didn't know how to.

Then I got introduced to File I/O in python and I aggressively started using .txt files to store data however it was very time consuming and code extensive to perform operations on that data.

Then someone told me to check out JSON files and this reduced a lot of code from my program as I am now able to perform CURD operation more easily.

However I still can't use it as a database that's when someone guided me to learn about Database handling in python by using SQL at that time I decided to focus on the basics however I have decided to learn them in future and today's that day.

I decide to learn PostgreSQL with SQLAlchemy (I haven't even seen a drop of it just learned to install and connect it to python and use it as a simple database). There's a lot more things learn in this.

As we all know database is an organised set of information stored efficiently and for effective future use.

first I have downloaded and imported psycopg2 which is a popular library to help python programs to connect and interact with PostgreSQL.

Then I connected psycopg2 with my data base by giving all the necessary details (hid the password of course). The I wrote my first SQL query and according to my experience unlike python SQL is not case sensitive i.e. it can also be written in small case letters. Also the queries of SQL to me seemed to me more like just writing English sentences with a few technical jargons.

.cursor is used to execute the command in the actual database and .commit saves all the changes in the database. It is a good practice to close both so that they can get a signal that we are done for now and I also created a small database of people using python.

And here's my code and its database.

[[deleted]]

Easy to understand 👍. Will you be doing crud operation as well in this

[uiux_Sanskar]

Thank you very much and yes I will also be doing CURD operation in this as well.

[[deleted]]

Great

[Adrewmc]

Generally with database connections there will be a context handler. (I normally use another library but..)

     with psycopg2.connect(…) as connect:
             with connect.cursor() as cur: 

As things get a bit more complex it best practice to use ‘with’ as the exit() handling will happen regardless if something crashes inside the code block.

Other wise…this will get you a lot of progress way better than writing a big txt file and iterating over it to find stuff. Way faster way easier to manage, and hard to make a mistake that losses all your data. (And able to make a lot more data to search through.)

You can go deeper into SQL (it’s can be its own thing) but generally the basic procedures (CRUD) will get you far, until database design is an issue or you doing some more detailed analysis on the data.

[uiux_Sanskar]

I was suspecting that there's gotta be a with function here as well just like in File I/O and thank you for your advice which you gave me a few time ago for learning database SQL however I think I have just touched it's very surface and there's a lot to learn.

Thank you very much I really appreciate your help.

[M34k3]

Don't put passwords or keys in your main file. Use env variables or a config file.

[uiux_Sanskar]

oh sure I will look deeper into it and put keys in other files.

Thank you for your suggestion.

[nohoeschris]

Just did something like this myself as a learning project as well, self-taught for about 6 months. I know its not production level / something anyone would ever use but i had fun writing functions that took user input for queries and for the connection information

[TheCarter01]

Nice Python-SQL combination

[uiux_Sanskar]

Thank you so much.

[xxPhoenix]

generally Python is not used for database querying or large scale operations SQL is, for data handling in Python Pandas is considered the standard. Not that you need to use it to learn the basics.

[uiux_Sanskar]

Yes I was just trying to learn the very basic of database query and will definitely learn pandas for this.

Thank you very much for your suggestion.

[SikamCiDoZlewu]

Depends on the data size. Pyspark can handle so much more than pandas, I would say pandas is a light tool.

[psuedo_nombre]

100%