< SQL | mysql.connector | MEBD >

mysql.connector

pip install mysql-connector-python

??First.py3 Listing: First.py3.

#!/usr/bin/python3

import mysql.connector


mydb = mysql.connector.connect(
  host="127.0.0.1",
  user="test",
  password="1234",
  database="test"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")
for x in mycursor:
  print(x)

Warning! Unable to pipe system command: cd /usr/local/Slides/1_Disciplines/5_MEBD/03_mysql_connector; /usr/bin/python3 First.py3

Output from: /usr/bin/python3 First.py3

Access to mysql:

In DATABASE mysql
- In TABLE user:
  - Host: localhost
  - User: user (=db)
  - Password: password
  - Privs: N(o) to all!!
- In TABLE db:
  - Host: localhost
  - Db: db
  - User: user
  - Privs: Y(es) to all!!
- Restart mysqld!!

??Main.py3 Listing: Main.py3.

#!/usr/bin/python3


from Quote import *
from Where import *
from Connect import *
from Exists import *
from Select import *
from Insert import *
from Update import *
from Delete import *
from Create import *

from Show import *


#Main execution: run test
where={
  "Email": "ole@ufg.br",
  "Phone": "62 5555555",
}

columns=["ID","Name","Email"]
orderby=["ID","Name"]


mydb = Connect(
  "127.0.0.1",
  "test",
  "1234",
  "test"
)

items=Select_Hashes(mydb,"Persons",where,columns,orderby)
Show_Hashes(items,columns)


persons=[
    {
        "ID": 1,
        "Name": "Ole",
        "Email": "ole@ufg.br",
        "Birthday": "11/01/1964",
        "Phone": "62 5555555",
        "Fax": "62 5555555",
    },
    {
        "ID": 2,
        "Name": "Fulano",
        "Email": "ole@ufg.br",
        "Birthday": "11/01/1999",
        "Phone": "62 5555555",
        "Fax": "62 5555555",
    },
    {
        "ID": 3,
        "Name": "Beltrano",
        "Email": "ole@ufg.br",
        "Birthday": "11/01/1964",
        "Phone": "62 5555555",
        "Fax": "62 5555555",
    },
    {
        "ID": 4,
        "Name": "Ciclano",
        "Email": "ole@ufg.br",
        "Birthday": "11/01/1964",
        "Phone": "62 444444",
        "Fax": "62 4444444",
    },
    {
        "ID": 5,
        "Name": "Odin",
        "Email": "ole@ufg.br",
        "Birthday": "11/01/1964",
        "Phone": "62 5555555",
        "Fax": "62 5555555",
    },
    {
        "ID": 6,
        "Name": "Thor",
        "Email": "thor@ceu.br",
        "Birthday": "11/01/1964",
        "Phone": "62 5555555",
        "Fax": "62 5555555",
    },
]


#query=Insert_Query("Persons",persons)

#print (query)

print (Exists_Table(mydb,"Persons"))
print (Exists_Table(mydb,"TEST"))


Create_Table(mydb,"TEST")
print (Exists_Table(mydb,"TEST"))


print (Exists_Column(mydb,"test","TEST","ID"))

print (Exists_Column(mydb,"test","TEST","hellooo"))

defs={
    "SQL": "VARCHAR(64)",
}

print (Create_Column(mydb,"test","TEST","hellooo",defs))

Python Listing: Quote.py.

#!/usr/bin/python3

#
#In MySql no quoting of column names
#

def Quote_Column(column):
  return column
    
#
#Quote list of columns
#

def Quote_Columns(columns):
  if (len(columns)==0): return "*"
  
  comps=[]
  for column in columns:
    comps.append(Quote_Column(column))

  return ",".join(comps)

#
#Quote value
#

def Quote_Value(value):
  return "'"+value+"'"


#
#Quote values
#
def Quote_Values(values):
  comps=[]
  for value in values:
    comps.append(Quote_Value(value))

  return ",".join(comps)
    
#
#Quote table name
#

def Quote_Table(table):
  return "`"+table+"`"

Python Listing: Where.py.

from Quote import *

#
#Convert where (dict) to string.
#

def Where_From_Dict(where={}):
  comps=[]
  for key in where.keys():
    comps.append(
      Quote_Column(key)+
      "="+
      Quote_Value(where[ key ])
    )
    
  return " AND ".join(comps)

Python Listing: Connect.py.

import mysql.connector

#
#Connect to DB and return DB handle
#
def Connect(host,user,password,database):
  return mysql.connector.connect(
    host=host,
    user=user,
    password=password,
    database=database
  )

Python Listing: Exists.py.

from Quote import *

#
#Generate SQL SELECT query
#

def Exists_Table_Query(table):
  sql=[
    "SHOW TABLES LIKE",
    Quote_Value(table)
  ]

  return " ".join(sql)
  
#
#Generate SQL SELECT query
#

def Exists_Table(mydb,table):
  sql=Exists_Table_Query(table)

  mycursor = mydb.cursor()
  mycursor.execute(sql)

  res=False
  for row in mycursor:
    res=True

  return res


#
#Query for checking whether Column exists.
#

def Exists_Column_Query(dbname,table,column):
  sql=[
    "SELECT TABLE_NAME, COLUMN_NAME ",
    "FROM INFORMATION_SCHEMA.COLUMNS",
    "WHERE", 
    "table_schema="+Quote_Value(dbname),
    "AND COLUMN_NAME="+Quote_Value(column),
    "AND TABLE_NAME="+Quote_Value(table)
  ]

  return " ".join(sql)+";"
#
#Query for checking whether Column exists.
#

def Exists_Column(mydb,dbname,table,column):
  sql=Exists_Column_Query(dbname,table,column)

  mycursor = mydb.cursor()
  mycursor.execute(sql)

  res=False
  for row in mycursor:
    res=True

  return res

Python Listing: Select.py.

#!/usr/bin/python3

import mysql.connector

from Quote import *
from Where import *

#
#Generate SQL SELECT query
#

def Select_Query(table,where,columns,orderby=[]):
  comps=[
    "SELECT",
    Quote_Columns(columns),
    "FROM",
    Quote_Table(table),
    "WHERE",
    Where_From_Dict(where),
  ]

  if (len(orderby)>0):
    comps.append("ORDER BY")
    comps.append(Quote_Columns(orderby))

  return " ".join(comps)+";"


#
#Generate SQL SELECT, execute and retrive returned items:
#List of dicts
#

def Select_Hashes(mydb,table,where,columns,orderby=[]):
  sql=Select_Query(table,where,columns,orderby)
  

  mycursor = mydb.cursor()
  mycursor.execute(sql)

  items=[]
  for row in mycursor:
    if (len(row)!=len(columns)):
      print("ERROR in Select_Hashes")
      
    item={}
    for i in range(len(columns)):
      column=columns[i]
      value=row[i]
      item[ column ]=value

    items.append(item)

  return items

Python Listing: Insert.py.

from Quote import *


#
#Query for doing an insert.
#

def Insert_Hashes_Query(table,items):
  columns=items[0].keys()
  columns=list(items[0])
  print(items[0],columns)
  
  comps=[
    "INSERT INTO\n",
    Quote_Table(table),
    "("+Quote_Columns(columns)+")",
    "VALUES\n",
  ]

  itemvalues=[]
  for item in items:
    values=[]
    for column in columns:
      value=str(item[ column ])
      values.append(Quote_Value(value))

    itemvalues.append( "   ("+",".join(values)+")" )

  comps.append( ",\n".join(itemvalues) )
          
  return " ".join(comps)+";"

#
#Do an insert.
#

def Insert_Hashes(mydb,table,items):
  sql=Insert_Hashes_Query(table,items)
  sql="".join(sql)
  print(sql)

  
  res=True
  mycursor = mydb.cursor()
  res=mycursor.execute(sql)

  mydb.commit()
  
  return res

Python Listing: Update.py.

from Quote import *



#
#Query for updating
#

 
def Update_Query(table,item,where):
  comps=[
    "UPDATE\n",
    Quote_Table(table),
    "SET\n",
  ]

  sets=[]
  for column in item.keys():
    key=Quote_Column(column)
    value=Quote_Value(str(item[ column ]))

    sets.append(key+"="+value)

  comps.append( ",".join(sets) )
  comps.append("WHERE")
  comps.append(Where_From_Dict(where))

  return " ".join(comps)
  
#
#do updating
#
def Update_Where(mydb,table,item,where):
  sql=Update_Query(table,item,where)

  mycursor = mydb.cursor()
  res=mycursor.execute(sql)

  return res

Python Listing: Delete.py.

from Quote import *

#
#Delete query
#
def Delete_Query(table,where):
  comps=[
    "DELETE FROM",
    Quote_Table(table),
    "WHERE\n",
  ]

  comps.append(Where_From_Dict(where))
  
  return " ".join(comps)

#
#do delete
#
def Delete_Where(mydb,table,where):
  sql=Delete_Query(table,where)

  mycursor = mydb.cursor()
  res=mycursor.execute(sql)

  return res

Python Listing: Create.py.

from Quote import *
from Exists import *


#
#Query for doing an insert.
#

def Create_Table_Query(table):
  comps=[
    "CREATE TABLE\n",
    Quote_Table(table),
    "(",
    "   ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT",
    ")",
  ]          
  return " ".join(comps)+";"

#
#Do an insert.
#

def Create_Table(mydb,table):
  res=True
  if (not Exists_Table(mydb,table)):
    sql=Create_Table_Query(table)
  
    mycursor = mydb.cursor()
    res=mycursor.execute(sql)

  return res

#
#Query for adding a column
#

def Create_Column_Query(table,column,col):
  comps=[
    "ALTER TABLE",
    Quote_Table(table),
    "ADD",
    Quote_Column(column),
    col[ "SQL" ],
  ]
  
  return " ".join(comps)+";"

#
#Query for adding a column
#

def Create_Column(mydb, dbname,table,column,col):
  res=True
  if (not Exists_Column(mydb,dbname,table,column)):
    sql=Create_Column_Query(table,column,col)
    
    mycursor = mydb.cursor()
    res=mycursor.execute(sql)

  
  return res

#
#Query for adding several columns
#

def Create_Columns(mydb, dbname,table,cols):
  for col in cols.keys():
    Create_Column(mydb, dbname,table,col,cols[ col ])

Python Listing: Show.py.

#
#Pretty print item (dict)
#

def Show_Hash(item,columns):
  for key in columns:
    print(key+": "+str(item[ key ]))

  print ("")


#
#Pretty print list of items (dicts)
#

def Show_Hashes(items,columns):
  for item in items:
    Show_Hash(item,columns)

Test case, School Classes and Disciplines:

Python Listing: Discs.py.

from Quote import *
from Where import *
from Connect import *
from Exists import *
from Select import *
from Insert import *
from Update import *
from Delete import *
from Create import *

from Show import *

dbname="Schools"

class_table="Classes"
disc_table="Discs"

class_columns={
    "Name": {
        "SQL": "VARCHAR(256)",
    },
}
disc_columns={
    "Name": {
        "SQL": "VARCHAR(256)",
    },
    "Title": {
        "SQL": "VARCHAR(256)",
    },
}
    
classes=[
    {
        "Name": '1o ano',
    },
    {
        "Name": '2o ano',
    },
    {
        "Name": '3o ano',
    },
]

disciplines=[
    {
        "Name": 'Portuguese',
        "Title": 'Língua Portuguesa',
    },
    {
        "Name": 'Inglês',
        "Title": 'Língua Inglês',
    },
]



def Init_Schools():
    db = Connect(
        "127.0.0.1",
        "Schools",#user
        "1234",
        dbname
    )

    #Create table and columns for classes
    #Then inserts
    Create_Table(db,class_table)
    Create_Columns(
        db, dbname,class_table,class_columns
    )
    
    Insert_Hashes(db,class_table,classes)

    
    #Create table and columns for discs
    #Then inserts
    
    Create_Table(db,disc_table)
    Create_Columns(
        db, dbname,disc_table,disc_columns
    )

    
    Insert_Hashes(db,disc_table,disciplines)

    return db


mydb=Init_Schools()

< SQL | mysql.connector | MEBD >