Python and MySQL Operations. Code Examples

#!/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.