Database querying and Python-Flask
Querying MySQL/PostgreSQL/Maria db databases: SELECT , INSERT, DELETE , UPDATE statement
Hi so first I hope you have formed the connection string between MySQL and python, be it MySQL or PostgreSQL or Maria db.
Now we have to query through our database using the python script. Let’s look at how it’s done.
In the MySQL Workbench , I have a database called platform, here I have a table called license_details.
Where I have the columns as you can see:
license_id, driver_name,driver_birthdate, driver_address, driver_tel_no, driver_blood_group
Now say we want to select the data in driver_name column.
answer=mycursor.execute("SELECT driver_name FROM license_details where license_id=%s",(licenseKey,))
for row in answer:
Assign the license no of the driver whose data you want to collect. Here we are using a variable called answer to catch the data send from our MySQL database. We are using a for loop to loop through the all the different rows in our license_details. At python script we catch the data in driver_name column using the name variable. Using the answer and mycursor.execute statement we are assigning the data to the answer variable. Using mycursor.fetchall() statement we are fetching the MySQL data to our python script, so remember not to forget it.
mycursor.execute("INSERT into license_details(license_id,driver_name) values(%s,%s)",(licenseKey,dName,))
Now we are inserting data into our license_details table in mySQL. At the python script assign variables as licenseKey, and dName and insert values.Then finally commit the changes to the database. Also can insert values this way.
mycursor.execute("INSERT into license_details(license_id,driver_name) values(%s,%s)",(F7890125,'Jenny',))
Now let’s update a table, changing the driver’s age, she was 19 then, 20 years now. Then commit to the database.
mycursor.execute("UPDATE license_details SET driver_age=driver_age+1 where license_id=%s",(F7890125),)
mycursor.execute(“UPDATE zone_capacity SET zone_capacity = zone_capacity + 1 where zone_port=%s”,(zone_port,))
Now let’s delete a record from license_details table
mycursor.execute("DELETE FROM license_details where license_id=%s",(F7890125),)
Have fun coding…..