Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
What About SQL?
#1
I am about to make another attempt to get into Python.

And it seems to me the first hurdle is going to be SQL. The type of app I intend to write will certainly require writing data to some kind of database and I think that means SQL for Python?

I remember just before I left off my last attempt at Python being enmeshed in learning to install, set up, make databases, query them and all sorts of stuff. Quite involved. Requiring installing Python packages I think, too.

I work, or did work, via Spyder and there were issues with it that further complicated matters.

So I don't look forward to wading through all that again so I thought I'd enquire about the whole database thing and Python today (because it's over a year since I left off that attempt).

Must I still follow the same path or is there a sort of 'modern' and more user friendly way to code with database use? What would be the recommended approach today?
Reply
#2
Think of programming language as kitchen equipment.
( Knives, Potato Piller, Cutting board, 6" knife, 10" bread knife, pots, and pans. Sometimes the tools are similar, but the difference between the tools might make you chose one or the other.)

SQL Structured Query Language is a language built so you can interact with the databases. What does it mean?

Let say.... you have a two-list.

In list one, you have all the movies you have in the house. and in the table there is
Title|Year|Leading Actor1|Leading Actor2|Genere|Year

In the last two, you have the list of the top 100 movies you should watch before you die.

If you want to find out which movies do I have( Table1) that belong in the AFI must watch list( Table2) >>> You are comparing list 1 from list 2 and extracting movies only movies exist in both lists. using SQL you can find out the answer really fast.
Able to Merge Table 1 and Table 2 and filtering base on certain criteria is call "Join" Function...

- You can ask questions like.

- (Assuming you watched all the movies you own) Show me all the movies I left to watch. But only Sci-Fi movies made between 1970~1980 ( movies listed on B but not on A Genere = Sci-fi Years 1970 ~ 1980)

- Show me all the movies on List B where Clint Eastwood was the lead actor.
etc.


I work as an Analyst and sometimes I need to compare a large set of data vs another data set, I do not write queries out, but the concept can be applied to many different software that deals with spreadsheets, and databases. Even Microsoft Excel has a function where you can Join tables and values. Its call get data and transform, If I never took a course on SQL, I would not even know what Left merge and right merge.. is

Since most data is stored in tables.. merging in on the fly is important. ( Your Amazon.com shopping history is technically a merged table of... ( Customer Information, Inventory, and purchase information ) and SQL concept can be useful in many problem-solving. SQL == Database data operation, extract data, transform the data, and load the data...
https://www.sqlshack.com/sql-examples-fo...ent-usage/


Python on the other hand... is a programing language. There are things you can do in python, you can not do in SQL because SQL is built for Query Operation. Programing Language is built so you can build programs. That is to say... python will talks to hardware and instruct what to do.

I learned [SQL] first. It took me 2-3 days and I took a course online. because I was familiar with excel spreadsheets, learning SQL was easy, AND it had an immediate impact on my work. After learning how to create tables on the fly, It was getting really annoying to get the input data so I can use it in my tool, so I learned [Rest API] So I can pull in the data. Now I'm learning [python 3] because I want to use python in Power BI... but I need to complete the entire book, so I know which std functions I can pull in...

SQL is easier to learn because it is a language made for a specific purpose, so the functions are all related to DB operation. and it is highly readable.
Reply
#3
Python has many SQL packages, see: https://pypi.org/search/?q=sql

One of my favourites is SqlAlchemy , and specifically the Object Relational Mapper (ORM). see: https://docs.sqlalchemy.org/en/14/orm/

I wrote a tutorial some time ago, that is an introduction to SqlAlchemy, it can be found here: https://python-forum.io/Thread-SqlAlchem...-Data-Load
Reply
#4
Not to mention that relational isn't the only kind of database. You don't say what your app is for, where it's going to run, etc., so it's difficult to be more specific.

In general, though, yes, you're going to need a third party library to interact with a database and have to do some setup of the database (e.g. creating tables if you're talking SQL). Using libraries is the user friendly way to do things - they hide away all the complexities of things for you. As an application developer, you want to spend your time on the app's functionality, not learning all the intimate details of your infrastructure.
buran likes this post
Reply
#5
To keep things simple while you learn I suggest you try SQLite: https://sqlite.org/index.html
It's built into most python distributions. It lacks the power features of tools like Postgresql but still has full SQL abilities. It can run on a server but the whole DB can also can be contained in one file right on your PC so it's easy to "Access" (pardon the pun).

You can monitor and modify your DB with the simple to use DB Browser: https://sqlitebrowser.org/

Of course there's a good python module: https://docs.python.org/3/library/sqlite...le-sqlite3

Annnnd, some great tutorials to learn it: https://www.tutorialspoint.com/sqlite/sqlite_python.htm
Reply
#6
We can likely be more helpful if we know a little more about your project. Are you thinking SQL because you think that is the only option? Because you are familiar with SQL?

There are lots of options and some of them have been listed here. MySQL, SQLlite (built in), SQLAlchemy are all good in the SQL realm. But, if you are just looking for a data storage platform, maybe json is a better fit. Not a fan of Pickle, but that is an option. Writing to an Excel spreadsheet. So many options, need to know what your familiarity with each of these is and what your project may be.
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020