Python i MySQL

Sada ćemo se upoznati sa načinom na koji Python komunicira sa MySQL serverom. Detaljniji opis možete da pogledate u Connector/Python Coding Examples.

Uspostavljanje konekcije

Sledeći program uspostavlja konekciju sa MySQL serverom

import mysql.connector    # importovanje modula za konekciju sa mySQL serverom
cnx = mysql.connector.connect(user='root', password='', #  konektor objekat
                              host='127.0.0.1',
                              database='youngpoets')
cursor = cnx.cursor()  # konektor kursor, sve dalje operacije se vrše preko cursor objekta

cursor.close()          # zatvaranje kursor objekta
cnx.close()             # zatvaranje konekcije

Ako hoćete da pratite greške onda konekciju na MySQL možete da napravite i ovako:

import mysql.connector
from mysql.connector import errorcode
try:
  cnx = mysql.connector.connect(user='root', database='youngpoets')
  cursor = cnx.cursor()
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
    cursor.close()
    cnx.close()

U gornjem programu pojavljuju se delovi koji su vezani za MySQL za koje je potrebno dodatno obajašnjenje:

• Modul mysql.connector je drajver za povezivanje na MySQL server.
• Metod mysql.connector.connect uspostavlja konekciju sa MySQL serverom. Kompletnu listu mogućih argumenta možete da nađete u Connector/Python Connection Arguments.
• Objekat cursor interaguje sa MySQL serverom. Detaljan opis klase cursor možete da nađete u cursor.MySQLCursor Class. Najčešće korišćene metode iz klase kurosr su execute, fetchone, fetchall sa kojima ćemo se upoznati u narednim primerima.

Kreiranje nove baze podataka

Sledeći program kreira novu bazu pod imenom ‘youngpoets’, ako takva baza nije već kreirana.

import mysql.connector
from mysql.connector import errorcode
DB_NAME = 'youngpoets'          # naziv baze koju zelimo da kreiramo
def create_database(cursor):    # funkcija kojom se kreira baza
    try:
        cursor.execute(
            "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
    except mysql.connector.Error as err:
        print("Failed creating database: {}".format(err))
        exit(1)
cnx = mysql.connector.connect(user='root')      # Konektor na mySQL server
cursor = cnx.cursor() # otvaranje kursora
try:
    cursor.execute("USE {}".format(DB_NAME))    #  upotreba baze ako vec postoji
except mysql.connector.Error as err:
    print("Database {} does not exists.".format(DB_NAME)) # baza ne postoji treba je kreirati
    if err.errno == errorcode.ER_BAD_DB_ERROR:
        create_database(cursor)
        print("Database {} created successfully.".format(DB_NAME))
        cnx.database = DB_NAME # konektor na bazu
    else:
        print(err)
        exit(1)
cursor.close()
cnx.close() # zatvaranje konektora

Kreiranje tabela

import mysql.connector
from mysql.connector import errorcode

TABLES = {}
TABLES['user'] = (
"CREATE TABLE IF NOT EXISTS user ("
"id INT(11) NOT NULL AUTO_INCREMENT,"
"name VARCHAR(45) NOT NULL,"
"email VARCHAR(100) NOT NULL,"
"country VARCHAR(2) DEFAULT NULL,"
"password CHAR(32) NOT NULL,"
"is_admin TINYINT(1) NOT NULL,"
"PRIMARY KEY (id))"
)


TABLES['poem'] = (
"CREATE TABLE IF NOT EXISTS poem ("
"id INT(11) NOT NULL AUTO_INCREMENT,"
"poem_type varchar(20) DEFAULT NULL,"
"user_id  INT(11) NOT NULL,"
"title VARCHAR(45) NOT NULL,"
"content LONGTEXT NOT NULL,"
"year YEAR(4) DEFAULT NULL,"
"PRIMARY KEY (id),"
"FOREIGN KEY (user_id)"
"REFERENCES user (id))"
)

TABLES['bio'] = (
"CREATE TABLE IF NOT EXISTS bio ("
"id INT(11) NOT NULL AUTO_INCREMENT,"
"user_id INT(11) NOT NULL,"
"content LONGTEXT NOT NULL,"
"PRIMARY KEY (id),"
"FOREIGN KEY (user_id)"
"REFERENCES user (id))"
)

# Konekcija
try:
  cnx = mysql.connector.connect(user='root', database='youngpoets1')
  cursor = cnx.cursor()
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)

# Kreiranje tabela
for table_name in TABLES:
    table_description = TABLES[table_name]
    try:
        print("Creating table {}: ".format(table_name), end='')
        cursor.execute(table_description)
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
            print("already exists.")
        else:
            print(err.msg)
    else:
        print("OK")

cursor.close()
cnx.close()

Insertovanje redova tabele

import mysql.connector

cnx = mysql.connector.connect(user='root', database='youngpoets')
cursor = cnx.cursor()

sql = "INSERT INTO user (name,email,country,password,is_admin) VALUES (%s,%s,%s,%s,%s);"


name = input("User name: ")
if name == "":
   exit(1)
email = input("Email: ")
country = input("Country: ")
password = input("Password: ")
is_admin =int(input("Administrator? (0/1): "))
cursor.execute(sql, (name,email,country,password,is_admin))

cnx.commit()
cursor.close()
cnx.close()

Ažuriranje tabele

import mysql.connector

cnx = mysql.connector.connect(user='root', database='youngpoets')
cursor = cnx.cursor()

query = "UPDATE user set name = 'Janković Janko' where country = %s"

cursor.execute(query, ("me",))
cnx.commit()

cursor.close()
cnx.close()

Brisanje redova iz tabele

import mysql.connector

cnx = mysql.connector.connect(user='root', database='youngpoets')
cursor = cnx.cursor()

query = "DELETE FROM user WHERE country = %s"
cursor.execute(query, ("rs",))

cnx.commit();
cursor.close()
cnx.close()

Pretraživanje tabele

import mysql.connector

cnx = mysql.connector.connect(user='root', database='youngpoets')
cursor = cnx.cursor('dict')

query = "SELECT name, country FROM user WHERE country = %s limit 10"

cursor.execute(query, ("sr",))
rows = cursor.fetchall()
# fetchall vraća listu tuplova, svaki tuple je kedan red tabele
print(rows)

for name, country in rows:
    print("Name:",name,"Country:",country)

cursor.close()
cnx.close()