CHAPTER 16
PYTHON My SQL
Python can be used in database applications.
One of the most popular databases is MySQL.
MySQL Database
To be able to experiment with the code examples in this tutorial, you should have MySQL installed on your computer.
You can download a free MySQL database at https://www.mysql.com/downloads/.
________________________________________
Install MySQL Driver
Python needs a MySQL driver to access the MySQL database.
In this tutorial we will use the driver "MySQL Connector".
We recommend that you use PIP to install "MySQL Connector".
PIP is most likely already installed in your Python environment.
Navigate your command line to the location of PIP, and type the following:
Download and install "MySQL Connector":
C:\Users\Your Name\AppData\Local\Programs\Python\Python36-32\Scripts>python -m pip install mysql-connector
Now you have downloaded and installed a MySQL driver.
________________________________________
Test MySQL Connector
To test if the installation was successful, or if you already have "MySQL Connector" installed, create a Python page with the following content:
demo_mysql_test.py:
import mysql.connector
If the above code was executed with no errors, "MySQL Connector" is installed and ready to be used.
________________________________________
Create Connection
Start by creating a connection to the database.
Use the username and password from your MySQL database:
demo_mysql_connection.py:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword"
)
print(mydb)
Now you can start querying the database using SQL statements.
Python MySQL Create Database
Creating a Database
To create a database in MySQL, use the "CREATE DATABASE" statement:
Example
create a database named "mydatabase":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")
If the above code was executed with no errors, you have successfully created a database.
________________________________________
Check if Database Exists
You can check if a database exist by listing all databases in your system by using the "SHOW DATABASES" statement:
Example
Return a list of your system's databases:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
Or you can try to access the database when making the connection:
Example
Try connecting to the database "mydatabase":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
If the database does not exist, you will get an error.
Python MySQL Create Table
Creating a Table
To create a table in MySQL, use the "CREATE TABLE" statement.
Make sure you define the name of the database when you create the connection
Example
Create a table named "customers":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
If the above code was executed with no errors, you have now successfully created a table.
________________________________________
Check if Table Exists
You can check if a table exist by listing all tables in your database with the "SHOW TABLES" statement:
Example
Return a list of your system's databases:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
print(x)
Primary Key
When creating a table, you should also create a column with a unique key for each record.
This can be done by defining a PRIMARY KEY.
We use the statement "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for each record. Starting at 1, and increased by one for each record.
Example
Create primary key when creating the table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
If the table already exists, use the ALTER TABLE keyword:
Example
Create primary key on an existing table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
Python MySQL Insert Into Table
Insert Into Table
To fill a table in MySQL, use the "INSERT INTO" statement.
Example
Insert a record in the "customers" table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="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.
Insert Multiple Rows
To insert multiple rows into a table, use the executemany() method.
The second parameter of the executemany() method is a list of tuples, containing the data you want to insert:
Example
Fill the "customers" table with data:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
('Peter', 'Lowstreet 4'),
('Amy', 'Apple st 652'),
('Hannah', 'Mountain 21'),
('Michael', 'Valley 345'),
('Sandy', 'Ocean blvd 2'),
('Betty', 'Green Grass 1'),
('Richard', 'Sky st 331'),
('Susan', 'One way 98'),
('Vicky', 'Yellow Garden 2'),
('Ben', 'Park Lane 38'),
('William', 'Central st 954'),
('Chuck', 'Main Road 989'),
('Viola', 'Sideway 1633')
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "was inserted.")
Get Inserted ID
You can get the id of the row you just inserted by asking the cursor object.
Note: If you insert more that one row, the id of the last inserted row is returned.
Example
Insert one row, and return the ID:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Michelle", "Blue Village")
mycursor.execute(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)
Python MySQL Select From
Select From a Table
To select from a table in MySQL, use the "SELECT" statement:
Example
Select all records from the "customers" table, and display the result:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="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. Selecting Columns
To select only some of the columns in a table, use the "SELECT" statement followed by the column name(s):
Example
Select only the name and address columns:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT name, address FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Using the fetchone() Method
If you are only interested in one row, you can use the fetchone() method.
The fetchone() method will return the first row of the result:
Example
Fetch only one row:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchone()
print(myresult)
Python MySQL Where
Select With a Filter
When selecting records from a table, you can filter the selection by using the "WHERE" statement:
Example
Select record(s) where the address is "Park Lane 38": result:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="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)
Wildcard Characters
You can also select the records that starts, includes, or ends with a given letter or phrase.
Use the % to represent wildcard characters:
Example
Select records where the address contains the word "way":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address LIKE '%way%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Prevent SQL Injection
When query values are provided by the user, you should escape the values.
This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.
The mysql.connector module has methods to escape query values:
Example
Escape query values by using the placholder %s method:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Python MySQL Order By
Sort the Result
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.
Example
Sort the result alphabetically by name: result:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="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)
ORDER BY DESC
Use the DESC keyword to sort the result in a descending order.
Example
Sort the result reverse alphabetically by name:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Python MySQL Delete From
Delete Record
You can delete records from an existing table by using the "DELETE FROM" statement:
Example
Delete any record where the address is "Mountain 21":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="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!
Prevent SQL Injection
It is considered a good practice to escape the values of any query, also in delete statements.
This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.
The mysql.connector module uses the placeholder %s to escape values in the delete statement:
Example
Escape values by using the placeholder %s method:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")
Python MySQL Drop Table
Delete a Table
You can delete an existing table by using the "DROP TABLE" statement:
Example
Delete the table "customers":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DROP TABLE customers"
mycursor.execute(sql)
Drop Only if Exist
If the the table you want to delete is already deleted, or for any other reason does not exist, you can use the IF EXISTS keyword to avoid getting an error.
Example
Delete the table "customers" if it exists:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS customers"
mycursor.execute(sql)
Python MySQL Update Table
Update Table
You can update existing records in a table by using the "UPDATE" statement:
Example
Overwrite the address column from "Valley 345" to "Canyoun 123":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="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!
________________________________________
Prevent SQL Injection
It is considered a good practice to escape the values of any query, also in update statements.
This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.
The mysql.connector module uses the placeholder %s to escape values in the delete statement:
Example
Escape values by using the placholder %s method:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
Python MySQL Limit
Limit the Result
You can limit the number of records returned from the query, by using the "LIMIT" statement:
Example
Select the 5 first records in the "customers" table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Start From Another Position
If you want to return five records, starting from the third record, you can use the "OFFSET" keyword:
Example
Start from position 3, and return 5 records:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Python MySQL Join
Join Two or More Tables
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:
users
{ 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:}
products
{ 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.
Example
Join users and products to see the name of the users favorite product:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT \
users.name AS user, \
products.name AS favorite \
FROM users \
INNER JOIN products ON users.fav = products.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Note: You can use JOIN instead of INNER JOIN. They will both give you the same result. LEFT JOIN
In the example above, Hannah, and Michael were excluded from the result, that is because INNER JOIN only shows the records where there is a match.
If you want to show all users, even if they do not have a favorite product, use the LEFT JOIN statement:
Example
Select all users and their favorite product:
sql = "SELECT \
users.name AS user, \
products.name AS favorite \
FROM users \
LEFT JOIN products ON users.fav = products.id"
________________________________________
RIGHT JOIN
If you want to return all products, and the users who have them as their favorite, even if no user have them as their favorite, use the RIGHT JOIN statement:
Example
Select all products, and the user(s) who have them as their favorite:
sql = "SELECT \
users.name AS user, \
products.name AS favorite \
FROM users \
RIGHT JOIN products ON users.fav = products.id"
Note: Hannah and Michael, who have no favorite product, are not included in the result.
CHAPTER 17
PYTHON MONGO DB
Python MongoDB
Python can be used in database applications.
One of the most popular NoSQL database is MongoDB.
________________________________________
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.
________________________________________
PyMongo
Python needs a MongoDB driver to access the MongoDB database.
In this tutorial we will use the MongoDB driver "PyMongo".
We recommend that you use PIP to install "PyMongo".
PIP is most likely already installed in your Python environment.
Navigate your command line to the location of PIP, and type the following:
Download and install "PyMongo":
C:\Users\Your Name\AppData\Local\Programs\Python\Python36-32\Scripts>python -m pip install pymongo
Now you have downloaded and installed a mongoDB driver.
________________________________________
Test PyMongo
To test if the installation was successful, or if you already have "pymongo" installed, create a Python page with the following content:
demo_mongodb_test.py:
import pymongo
If the above code was executed with no errors, "pymongo" is installed and ready to be used.
Python MongoDB Create Database
Creating a Database
To create a database in MongoDB, start by creating a MongoClient object, then specify a connection URL with the correct ip address and the name of the database you want to create.
MongoDB will create the database if it does not exist, and make a connection to it.
Example
Create a database called "mydatabase":
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
Important: In MongoDB, a database is not created until it gets content!
MongoDB waits until you have created a collection (table), with at least one document (record) before it actually creates the database (and collection).
________________________________________
Check if Database Exists
Remember: In MongoDB, a database is not created until it gets content, so if this is your first time creating a database, you should complete the next two chapters (create collection and create document) before you check if the database exists!
You can check if a database exist by listing all databases in you system:
Example
Return a list of your system's databases:
print(myclient.list_database_names())
Or you can check a specific database by name:
Example
Check if "mydatabase" exists:
dblist = myclient.list_database_names()
if "mydatabase" in dblist:
print("The database exists.")
Python MongoDB Create Collection
A collection in MongoDB is the same as a table in SQL databases.
Creating a Collection
To create a collection in MongoDB, use database object and specify the name of the collection you want to create.
MongoDB will create the collection if it does not exist.
Example
Create a collection called "customers":
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
Important: In MongoDB, a collection is not created until it gets content!
MongoDB waits until you have inserted a document before it actually creates the collection.
________________________________________
Check if Collection Exists
Remember: In MongoDB, a collection is not created until it gets content, so if this is your first time creating a collection, you should complete the next chapter (create document) before you check if the collection exists!
You can check if a collection exist in a database by listing all collections:
Example
Return a list of all collections in your database:
print(mydb.list_collection_names())
Or you can check a specific collection by name:
Example
Check if the "customers" collection exists:
collist = mydb.list_collection_names()
if "customers" in collist:
print("The collection exists.")
Python MongoDB Insert Document
A document in MongoDB is the same as a record in SQL databases.
Insert Into Collection
To insert a record, or document as it is called in MongoDB, into a collection, we use the insert_one() method.
The first parameter of the insert_one() method is a dictionary containing the name(s) and value(s) of each field in the document you want to insert.
Example
Insert a record in the "customers" collection:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
mydict = { "name": "John", "address": "Highway 37" }
x = mycol.insert_one(mydict)
Return the _id Field
The insert_one() method returns a InsertOneResult object, which has a property, inserted_id, that holds the id of the inserted document.
Example
Insert another record in the "customers" collection, and return the value of the _id field:
mydict = { "name": "Peter", "address": "Lowstreet 27" }
x = mycol.insert_one(mydict)
print(x.inserted_id)
If you do not specify an _id field, then MongoDB will add one for you and assign a unique id for each document.
In the example above no _id field was specified, so MongoDB assigned a unique _id for the record (document).
________________________________________
Insert Multiple Documents
To insert multiple documents into a collection in MongoDB, we use the insert_many() method.
The first parameter of the insert_many() method is a list containing dictionaries with the data you want to insert:
Example
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
mylist = [
{ "name": "Amy", "address": "Apple st 652"},
{ "name": "Hannah", "address": "Mountain 21"},
{ "name": "Michael", "address": "Valley 345"},
{ "name": "Sandy", "address": "Ocean blvd 2"},
{ "name": "Betty", "address": "Green Grass 1"},
{ "name": "Richard", "address": "Sky st 331"},
{ "name": "Susan", "address": "One way 98"},
{ "name": "Vicky", "address": "Yellow Garden 2"},
{ "name": "Ben", "address": "Park Lane 38"},
{ "name": "William", "address": "Central st 954"},
{ "name": "Chuck", "address": "Main Road 989"},
{ "name": "Viola", "address": "Sideway 1633"}
]
x = mycol.insert_many(mylist)
#print list of the _id values of the inserted documents:
print(x.inserted_ids)
The insert_many() method returns a InsertManyResult object, which has a property, inserted_ids, that holds the ids of the inserted documents.
________________________________________
Insert Multiple Documents, with Specified IDs
If you do not want MongoDB to assign unique ids for you document, you can specify the _id field when you insert the document(s).
Remember that the values has to be unique. Two documents cannot have the same _id.
Example
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
mylist = [
{ "_id": 1, "name": "John", "address": "Highway 37"},
{ "_id": 2, "name": "Peter", "address": "Lowstreet 27"},
{ "_id": 3, "name": "Amy", "address": "Apple st 652"},
{ "_id": 4, "name": "Hannah", "address": "Mountain 21"},
{ "_id": 5, "name": "Michael", "address": "Valley 345"},
{ "_id": 6, "name": "Sandy", "address": "Ocean blvd 2"},
{ "_id": 7, "name": "Betty", "address": "Green Grass 1"},
{ "_id": 8, "name": "Richard", "address": "Sky st 331"},
{ "_id": 9, "name": "Susan", "address": "One way 98"},
{ "_id": 10, "name": "Vicky", "address": "Yellow Garden 2"},
{ "_id": 11, "name": "Ben", "address": "Park Lane 38"},
{ "_id": 12, "name": "William", "address": "Central st 954"},
{ "_id": 13, "name": "Chuck", "address": "Main Road 989"},
{ "_id": 14, "name": "Viola", "address": "Sideway 1633"}
]
x = mycol.insert_many(mylist)
#print list of the _id values of the inserted documents:
print(x.inserted_ids)
Python MongoDB Find
In MongoDB we use the find and findOne methods to find data in a collection.
Just like the SELECT statement is used to find data in a table in a MySQL database.
Find One
To select data from a collection in MongoDB, we can use the find_one() method.
The find_one() method returns the first occurrence in the selection.
Example
Find the first document in the customers collection:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
x = mycol.find_one()
print(x)
________________________________________
Find All
To select data from a table in MongoDB, we can also use the find() method.
The find() method returns all occurrences in the selection.
The first parameter of the find() method is a query object. In this example we use an empty query object, which selects all documents in the collection.
No parameters in the find() method gives you the same result as SELECT * in MySQL.
Example
Return all documents in the "customers" collection, and print each document:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
for x in mycol.find():
print(x)
________________________________________
Return Only Some Fields
The second parameter of the find() method is an object describing which fields to include in the result.
This parameter is optional, and if omitted, all fields will be included in the result.
Example
Return only the names and addresses, not the _ids:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
for x in mycol.find({},{ "_id": 0, "name": 1, "address": 1 }):
print(x)
You are not allowed to specify both 0 and 1 values in the same object (except if one of the fields is the _id field). If you specify a field with the value 0, all other fields get the value 1, and vice versa:
Example
This example will exclude "address" from the result:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
for x in mycol.find({},{ "address": 0 }):
print(x)
Example
You get an error if you specify both 0 and 1 values in the same object (except if one of the fields is the _id field):
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
for x in mycol.find({},{ "name": 1, "address": 0 }):
print(x)
Python MongoDB Query
Filter the Result
When finding documents in a collection, you can filter the result by using a query object.
The first argument of the find() method is a query object, and is used to limit the search.
Example
Find document(s) with the address "Park Lane 38":
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
myquery = { "address": "Park Lane 38" }
mydoc = mycol.find(myquery)
for x in mydoc:
print(x)
________________________________________
Advanced Query
To make advanced queries you can use modifiers as values in the query object.
E.g. to find the documents where the "address" field starts with the letter "S" or higher (alphabetically), use the greater than modifier: {"$gt": "S"}:
Example
Find documents where the address starts with the letter "S" or higher:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
myquery = { "address": { "$gt": "S" } }
mydoc = mycol.find(myquery)
for x in mydoc:
print(x)
________________________________________
Filter With Regular Expressions
You can also use regular expressions as a modifier.
Regular expressions can only be used to query strings.
To find only the documents where the "address" field starts with the letter "S", use the regular expression {"$regex": "^S"}:
Example
Find documents where the address starts with the letter "S":
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
myquery = { "address": { "$regex": "^S" } }
mydoc = mycol.find(myquery)
for x in mydoc:
print(x)
Python MongoDB Sort
Sort the Result
Use the sort() method to sort the result in ascending or descending order.
The sort() method takes one parameter for "fieldname" and one parameter for "direction" (ascending is the default direction).
Example
Sort the result alphabetically by name:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
mydoc = mycol.find().sort("name")
for x in mydoc:
print(x)
________________________________________
Sort Descending
Use the value -1 as the second parameter to sort descending.
sort("name", 1) #ascending
sort("name", -1) #descending
Example
Sort the result reverse alphabetically by name:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
mydoc = mycol.find().sort("name", -1)
for x in mydoc:
print(x)
Python MongoDB Delete Document
Delete Document
To delete one document, we use the delete_one() method.
The first parameter of the delete_one() method is a query object defining which document to delete.
Note: If the query finds more than one document, only the first occurrence is deleted.
Example
Delete the document with the address "Mountain 21":
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
myquery = { "address": "Mountain 21" }
mycol.delete_one(myquery)
________________________________________
Delete Many Documents
To delete more than one document, use the delete_many() method.
The first parameter of the delete_many() method is a query object defining which documents to delete.
Example
Delete all documents were the address starts with the letter S:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
myquery = { "address": {"$regex": "^S"} }
x = mycol.delete_many(myquery)
print(x.deleted_count, " documents deleted.")
________________________________________
Delete All Documents in a Collection
To delete all documents in a collection, pass an empty query object to the delete_many() method:
Example
Delete all documents in the "customers" collection:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
x = mycol.delete_many({})
print(x.deleted_count, " documents deleted.")
Python MongoDB Drop Collection
Delete Collection
You can delete a table, or collection as it is called in MongoDB, by using the drop() method.
Example
Delete the "customers" collection:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
mycol.drop()
The drop() method returns true if the collection was dropped successfully, and false if the collection does not exist.
Python MongoDB Update
Update Collection
You can update a record, or document as it is called in MongoDB, by using the update_one() method.
The first parameter of the update_one() method is a query object defining which document to update.
Note: If the query finds more than one record, only the first occurrence is updated.
The second parameter is an object defining the new values of the document.
Example
Change the address from "Valley 345" to "Canyon 123":
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
myquery = { "address": "Valley 345" }
newvalues = { "$set": { "address": "Canyon 123" } }
mycol.update_one(myquery, newvalues)
#print "customers" after the update:
for x in mycol.find():
print(x)
________________________________________
Update Many
To update all documents that meets the criteria of the query, use the update_many() method.
Example
Update all documents where the address starts with the letter "S":
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
myquery = { "address": { "$regex": "^S" } }
newvalues = { "$set": { "name": "Minnie" } }
x = mycol.update_many(myquery, newvalues)
print(x.modified_count, "documents updated.")
Python MongoDB Limit
Limit the Result
To limit the result in MongoDB, we use the limit() method.
The limit() method takes one parameter, a number defining how many documents to return.
Consider you have a "customers" collection:
Customers
{'_id': 1, 'name': 'John', 'address': 'Highway37'}
{'_id': 2, 'name': 'Peter', 'address': 'Lowstreet 27'}
{'_id': 3, 'name': 'Amy', 'address': 'Apple st 652'}
{'_id': 4, 'name': 'Hannah', 'address': 'Mountain 21'}
{'_id': 5, 'name': 'Michael', 'address': 'Valley 345'}
{'_id': 6, 'name': 'Sandy', 'address': 'Ocean blvd 2'}
{'_id': 7, 'name': 'Betty', 'address': 'Green Grass 1'}
{'_id': 8, 'name': 'Richard', 'address': 'Sky st 331'}
{'_id': 9, 'name': 'Susan', 'address': 'One way 98'}
{'_id': 10, 'name': 'Vicky', 'address': 'Yellow Garden 2'}
{'_id': 11, 'name': 'Ben', 'address': 'Park Lane 38'}
{'_id': 12, 'name': 'William', 'address': 'Central st 954'}
{'_id': 13, 'name': 'Chuck', 'address': 'Main Road 989'}
{'_id': 14, 'name': 'Viola', 'address': 'Sideway 1633'}
Example
Limit the result to only return 5 documents:
import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
mycol = mydb["customers"]
myresult = mycol.find().limit(5)
#print the result:
for x in myresult:
print(x)
EXTRAS
How to Remove Duplicates From a Python List
Learn how to remove duplicates from a List in Python.
________________________________________
Example
Remove any duplicates from a List:
mylist = ["a", "b", "a", "c", "c"]
mylist = list(dict.fromkeys(mylist))
print(mylist)
Example Explained
First we have a List that contains duplicates:
A List with Duplicates
mylist = ["a", "b", "a", "c", "c"]
mylist = list(dict.fromkeys(mylist))
print(mylist)
Create a dictionary, using the List items as keys. This will automatically remove any duplicates because dictionaries cannot have duplicate keys.
Create a Dictionary
mylist = ["a", "b", "a", "c", "c"]
mylist = list( dict.fromkeys(mylist) )
print(mylist)
Then, convert the dictionary back into a list:
Convert Into a List
mylist = ["a", "b", "a", "c", "c"]
mylist = list( dict.fromkeys(mylist) )
print(mylist)
Now we have a List without any duplicates, and it has the same order as the original List.
Print the List to demonstrate the result
Print the List
mylist = ["a", "b", "a", "c", "c"]
mylist = list(dict.fromkeys(mylist))
print(mylist)
Create a Function
If you like to have a function where you can send your lists, and get them back without duplicates, you can create a function and insert the code from the example above.
Example
def my_function(x):
return list(dict.fromkeys(x))
mylist = my_function(["a", "b", "a", "c", "c"])
print(mylist)
Example Explained
Create a function that takes a List as an argument.
Create a Function
def my_function(x):
return list(dict.fromkeys(x))
mylist = my_function(["a", "b", "a", "c", "c"])
print(mylist)
Create a dictionary, using this List items as keys.
Create a Dictionary
def my_function(x):
return list( dict.fromkeys(x) )
mylist = my_function(["a", "b", "a", "c", "c"])
print(mylist)
Convert the dictionary into a list.
Convert Into a List
def my_function(x):
return list( dict.fromkeys(x) )
mylist = my_function(["a", "b", "a", "c", "c"])
print(mylist)
Return the list
Return List
def my_function(x):
return list(dict.fromkeys(x))
mylist = my_function(["a", "b", "a", "c", "c"])
print(mylist)
Call the function, with a list as a parameter:
Call the Function
def my_function(x):
return list(dict.fromkeys(x))
mylist = my_function(["a", "b", "a", "c", "c"])
print(mylist)
Print the result:
Print the Result
def my_function(x):
return list(dict.fromkeys(x))
mylist = my_function(["a", "b", "a", "c", "c"])
print(mylist)
PROGRAM 1
Example: prg1.py
print("Amman")
x=10
y=20
z=x+y
print(z)
print("Z=" ,z)
print("Z" + str(z))
if x==10:
print("X is 10")
#print("X is 10")
else:
print("X is not 10")
print("End of program")
Output:
c:\users\admin>python prg1.py
Amman
30
Z= 30
Z30
X is 10
End of program
Example: prg2.py
import numpy as np
import matplotlib as plt
print("Amman")
# variable declaration not required
x = 51
y = 10.5
z = 'Amman';
print("Enter X:",end='')
x=input()
print("X", end=':')
print(x)
print("Y=", end='')
print(y)
print("Z=" + str(z))
print(str(x) + ":" + str(y))
print(str(x) + ":" + str(y))
f=10.5
f2= int(f)
print("F2=" + str(f2))
"""
Multiline comment
dgdfgdf
gdf
gfg
dfgdf
"""
# exit() this is single line comment
print('Enter', end=':')
x = input()
print(x)
y=20
if x == 5:
print('y')
else:
print('n')
if y==20:
print ("Y " + str(y))
a = 100;
b = 20;
c = 30;
print(type(a)) #
x=100.45
print(type(x)) #
z="Amman"
print(type(z)) #
""" Biggest among
three numbers
"""
if a > b:
if a > c:
print('A')
else:
print('C')
# elif b > c:
# print('B')
#else:
# print('C')
i = 1
sum=0
while i <= 10:
print(i)
sum+=i
i +=1
print("Sum=" + str(sum)) #Sum=55
for i in range(1,21):
print(i)
for i in range(1, 11, 2):
print(i);
for i in range(10, 0, -2):
print(i);
#String Literals
#String literals in python are surrounded by either single quotation marks, or double quotation marks.
#'hello' is the same as "hello".
#get the character at position 1
a = "hello"
print(a[0]) #h
print(a[1]) #e
#Substring. Get the characters from position 2 to position 5:
b = "world"
print(b[2:5]) #rld
#The strip() method removes any whitespace from the beginning or the end:
a = " Hello, World! "
print(a.strip()) # returns "Hello, World!"
#The len() method returns the length of a string:
a = "Hello, World!"
print(len(a))
#The lower() method returns the string in lower case:
a = "Hello, World!"
print(a.lower())
#The upper() method returns the string in upper case:
a = "Hello, World!"
print(a.upper())
#The replace() method replaces a string with another string:
a = "Hello, World!"
print(a.replace("H", "J"))
Example: prg3.py
x=10
y=10
if x is y:
print("Yes")
else:
print("No")
Output
Yes
Comments
Post a Comment