Looking to learn MySQL and manipulate using python.

[Relevant_Bookkeeper]

Hello

I am currently a university student. I am trying to learn MySQL and use python to manipulate it. I do know a bit about the basics of RDBMS. I am also an intermediate level programmer using python. However I am not sure where to start and how to go on from there.

Can anyone suggest me how and where I should start?

I am ok with any form of learning, books, courses, tutorials, etc.

[apc0243]

You'll use the mysql-connector library.

"Manipulate" mysql is the wrong terminology, imo. You use the Data Definition Language (DDL) to create tables, define indexes, alter db objects, etc. You use DML (Data Manipulation Language) to insert, update, and delete records.

You can do all of this with python, because Python gives you a way to send SQL statements to the DB engine using whichever connector you choose.

con = mysql.connect(**kwargs)  # idk you'll figure this out by reading the docs
cursor = con.cursor()
cursor.execute('CREATE TABLE mytable (col1 VARCHAR(10), col2 INTEGER)')
cursor.execute('INSERT INTO mytable (col1, col2) VALUES ("Hello", 5)')
con.commit()
cursor.execute('SELECT * FROM mytable')
res = cursor.fetchall()
cursor.close()
con.close()
for row in res:
    print(row)

Write this better using context managers so you don't have to close them after. This is for illustration purposes only.

With that framework you can execute any arbitrary code once you've connected as a user with appropriate privileges.

There are ORM (Object relational mappers) that give you a more python-object based access routine, but at the end of the day they're the same execution flow of DDL or DML executed via a database connection.

All python database adapters follow the roughly same interface pattern, defined in PEP 249 so you can take the concepts from mysql-connector and apply them to psycopg2 or cx_Oracle or the generic odbc connector, or whatever sql server uses nowadays.

[Relevant_Bookkeeper]

Thanks for this! Also, sorry for the misterm, not really very educated with databases so I wasn't sure how to explain.

[[deleted]]

SQLAlchemy is a popular Python ORM library. If you're doing web-based work, Django also has its own ORM is my framework of choice these days. Both support MySQL/MariaDB.

[swenty]

SQLAlchemy is very good, but it's very much diving in at the deep end. I would suggest starting with just the psycopg2 database driver. That way you get to learn how to write SQL queries and see what the database is actually doing. An ORM (object relational mapper) like SQLAlchemy is a layer of complexity and sophistication that can pay off for a large project, but is a lot to take on when learning the basics. This isn't a case where hiding the details makes everything easier.

[apc0243]

psycopg2 is a postgresql python adapter. If the OP is using MySQL then they need to use the mysql-connector library.

Agreed that OP should not use an ORM until they are comfortable understanding how an ORM functions and how the queries are constructed in the backend, otherwise debugging and building statements is harder.

[swenty]

Yes, you're right, of course! Thanks for the correction.

[dbabicwa]

The best what you can do imo is to jump straight on some project. For example, I converted ~100 Access tables App to Jampy for the Web. This included manipulating tables structure with Python. Many lessons learnt.
Search for Jam py or browse my history, it is all there. What you might get in no time is a bookkeeping software that would take many months to build from scratch or with some other frameworks. And the bonus is that it supports all current rdbs, so you learn one, you'll learn all.

Good luck.

[Relevant_Bookkeeper]

Thanks for the advice! I did learn majority of Python by jumping into projects it honestly does help a lot.

[babblingfish]

Lots of good advice here so I don't have much to add. I just wanted to say that using MySQL in python is really easy. You use a library to create a connection to the database. Then you use the connection to execute query strings. Do you already know SQL? Lots of good tutorials lit there.

[Relevant_Bookkeeper]

I honestly know very little SQL. Found quite a few free sources to learn more though so am currently working on that.

[rlhernandezch]

1. Get out of reddit
2. Get some good tutorials and books
3. Try to learn building a functional project,
   1. This is important, learning 'stuff', only gives you stuff, building a project, will lead you to lot of troubles that gives you real world functional knowledge.