#!/usr/bin/env python# coding: utf-8
# In[63]:

import pymongo;

# In[64]:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]

# In[65]:

# Check if database exist

# In[66]:

print(myclient.list_database_names())

# In[67]:

dblist = myclient.list_database_names()if "mydatabase" in dblist:  print("The database exists.")

# In[68]:

# Create a collection

# In[69]:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")mydb = myclient["mydatabase"]
mycol = mydb["customers"]

# In[70]:

#collection exist

# In[71]:

print(mydb.list_collection_names())

# In[72]:

collist = mydb.list_collection_names()if "customers" in collist:  print("The collection exists.")

# In[73]:

# Insert into collection

# In[74]:

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)

# In[75]:

# return id of the inserted record

# In[76]:

mydict = { "name": "Peter", "address": "Lowstreet 27" }x = mycol.insert_one(mydict)print(x.inserted_id)

# In[77]:

# How to insert multiple documents

# In[78]:

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)

# In[79]:

#insert many documents and also provide the id to each record/document

# In[80]:

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)

# In[81]:

# find one

# In[82]:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")mydb = myclient["mydatabase"]mycol = mydb["customers"]
x = mycol.find_one()
print(x)

# In[83]:

# Select * i.e. find

# In[84]:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")mydb = myclient["mydatabase"]mycol = mydb["customers"]
for x in mycol.find():  print(x)

# In[23]:

# find some columns

# In[26]:

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)

# In[27]:

# Exclude address from the result

# In[28]:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")mydb = myclient["mydatabase"]mycol = mydb["customers"]
for x in mycol.find({},{ "address": 0 }):  print(x)

# In[29]:

#error if both 0 and 1 in the same object

# In[30]:

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)

# In[31]:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")mydb = myclient["mydatabase"]mycol = mydb["customers"]
for x in mycol.find({},{ "name": 1 }):  print(x)

# In[32]:

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)

# In[33]:

# advanced query with regular expressions

# In[34]:

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)

# In[35]:

# Sort

# In[36]:

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)

# In[37]:

# reverse, alphabetically

# In[38]:

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)

# In[39]:

#delete_one, only the first

# In[40]:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")mydb = myclient["mydatabase"]mycol = mydb["customers"]
myquery = { "address": "Mountain 21" }
mycol.delete_one(myquery)

# In[41]:

# delete_many

# In[42]:

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.")

# In[43]:

# delete all documents

# In[44]:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")mydb = myclient["mydatabase"]mycol = mydb["customers"]
x = mycol.delete_many({})
print(x.deleted_count, " documents deleted.")

# In[45]:

import pymongo
myclient = pymongo.MongoClient("mongodb://localhost:27017/")mydb = myclient["mydatabase"]mycol = mydb["customers"]
mycol.drop()

# In[46]:

# update data: update one

# In[47]:

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)

# In[48]:

#update many

# In[49]:

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.")

# In[50]:

# limit

# In[85]:

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)

# In[ ]:

Ref: W3Schools

#!/usr/bin/env python# coding: utf-8
# In[1]:

import mysql.connector

# In[2]:

# create a database connection

# In[3]:

import mysql.connector
mydb = mysql.connector.connect(  host="localhost",  user="root",  password="")
print(mydb)

# In[4]:

import mysql.connector
mydb = mysql.connector.connect(  host="localhost",  user="root",  password="")
mycursor = mydb.cursor()
mycursor.execute("drop DATABASE mydatabase");mycursor.execute("CREATE DATABASE mydatabase");

# In[5]:

# check if database exists

# In[6]:

import mysql.connector
mydb = mysql.connector.connect(  host="localhost",  user="root",  password="")
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:  print(x)

# In[7]:

# connect to the database and see if you can connect

# In[8]:

import mysql.connector
mydb = mysql.connector.connect(  host="localhost",  user="root",  password="",  database="mydatabase")

# In[9]:

# Create table

# In[10]:

import mysql.connector
mydb = mysql.connector.connect(  host="localhost",  user="root",  password="",  database="mydatabase")
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

# In[11]:

# Check if the Table Exists

# In[12]:

import mysql.connector
'''mydb = mysql.connector.connect(  host="localhost",  user="yourusername",  password="yourpassword",  database="mydatabase")'''
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:  print(x)

# In[13]:

# Create table with primary key

# In[14]:

mycursor.execute("CREATE TABLE customers_p (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

# In[15]:

# Create primary key for an existing table

# In[16]:

mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

# In[17]:

# insert into tables

# In[18]:

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"val = ("John", "Highway 21")mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")

# In[19]:

# insert multiple rows

# In[20]:

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.")

# In[21]:

# get the inserted row id

# In[22]:

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);

# In[23]:

# Select Records

# In[24]:

import mysql.connector

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:  print(x)

# In[25]:

# select columns

# In[26]:

mycursor = mydb.cursor()
mycursor.execute("SELECT name, address FROM customers")
myresult = mycursor.fetchall()
for x in myresult:  print(x)

# In[27]:

#fetch only one row

# In[28]:

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchone()
print(myresult)

# In[29]:

# use where condition

# In[30]:

import mysql.connector
mydb = mysql.connector.connect(  host="localhost",  user="root",  password="",  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)

# In[31]:

# Prevent SQL Injection

# In[32]:

import mysql.connector
mydb = mysql.connector.connect(  host="localhost",  user="root",  password="",  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)

# In[33]:

# order by

# In[34]:

mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:  print(x)

# In[35]:

# order by desc

# In[36]:

mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:  print(x)

# In[37]:

# Delete From By

# In[38]:

mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")

# In[39]:

# delete with SQL Injection

# In[40]:

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")

# In[41]:

# drop tables

# In[42]:

mycursor = mydb.cursor()
sql = "DROP TABLE customers"
mycursor.execute(sql)

# In[43]:

# drop table only if exists

# In[44]:

mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS customers"
mycursor.execute(sql)

# In[45]:

mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

# In[46]:

#MySQL Update Table

# In[47]:

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")

# In[48]:

# update with SQL Injection

# In[49]:

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")

# In[50]:

# limit results

# In[51]:

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:  print(x)

# In[52]:

# Limit from a different position

# In[53]:

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = mycursor.fetchall()
for x in myresult:  print(x)

# In[54]:

# create table users

# In[55]:

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE products (id varchar(100), name VARCHAR(255), address VARCHAR(255))")

# In[56]:

mycursor = mydb.cursor()mycursor.execute("CREATE TABLE users (id varchar(100), name VARCHAR(255), fav VARCHAR(255))")

# In[57]:

# insert into users

# In[58]:

sql = "INSERT INTO users (id, name, fav) VALUES (%s, %s, %s)"val = [  ('1', 'John', '4'),  ('2', 'Apple', '55')      ]

# In[59]:

sql = "INSERT INTO products (id, name) VALUES (%s, %s)"val = [  ('4', 'chocolate'),  ('5', 'Milk')    ]

# In[60]:

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)

# In[ ]:

Ref: W3Schools.