Python can be used in database applications.
One of the most popular NoSQL database is MongoDB.
MongoDB stores data in JSON-like documents, which makes the database very flexible and scalable.
To be able to experiment with the code examples in this tutorial, you will need access to a MongoDB database.
You can download a free MongoDB database at
https://www.mongodb.com.
Or get started right away with a MongoDB cloud service at
https://www.mongodb.com/cloud/atlas.
You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.
Consider you have a “users” table and a “products” table:
{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}
{ id: 154, name: 'Chocolate Heaven' },
{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }
These two tables can be combined by using users’ fav field and products’ id field. Continue reading Python MySQL Join
You can limit the number of records returned from the query, by using the “LIMIT” statement:
Select the 5 first records in the “customers” table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
You can update existing records in a table by using the “UPDATE” statement:
Overwrite the address column from “Valley 345” to “Canyon 123”:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.
Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
You can delete an existing table by using the “DROP TABLE” statement:
Delete the table “customers”:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DROP TABLE customers"
mycursor.execute(sql)
You can delete records from an existing table by using the “DELETE FROM” statement:
Delete any record where the address is “Mountain 21”:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")
Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.
Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record(s) that should be deleted. If you omit the WHERE clause, all records will be deleted!
Use the ORDER BY statement to sort the result in ascending or descending order.
The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword.
Sort the result alphabetically by name: result:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
When selecting records from a table, you can filter the selection by using the “WHERE” statement:
Select record(s) where the address is “Park Lane 38”: result:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address ='Park Lane 38'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
To select from a table in MySQL, use the “SELECT” statement:
Select all records from the “customers” table, and display the result:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Note: We use the fetchall() method, which fetches all rows from the last executed statement.
To fill a table in MySQL, use the “INSERT INTO” statement.
Insert a record in the “customers” table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.