Articole similare

Tutorial Python + MySQL + Windows 7

Nu este chiar un tutorial, ci mai mult niste notite sa nu uit cum am instalat Python si MySQL pe Windows 7 64bit, de unde ce am downloadat si niste exemple de accesare a unei baze de date MySQL din Python. Am folosit un server WAMP pentru ca voi avea nevoie si de Apache si PHP pentru proiecte viitoare care sa ruleze atat in Windows cat si in Linux pe Raspberry Pi.

mysql-python-logo Ce ai nevoie

Creeaza o baza de date pentru teste

Deschide o consola mysql din linie de comanda: mysql -uroot -proot

si creeaza un utilizator si o baza de date noua:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'bobtech';
CREATE DATABASE testdb;
USE testdb;
GRANT ALL ON testdb.* TO 'testuser'@'localhost';
quit;

Exemplu Python+MySQLdb

import MySQLdb as mydb
import sys

con = None

try:
    mycon = mydb.connect('localhost', 'testuser', 'bobtech', 'testdb');
    
    with mycon:
        cur = mycon.cursor()
        cur.execute("SELECT VERSION()")
        data = cur.fetchone()
        print "MySQL: %s " % data
        
        #create table and insert table records
        cur.execute("CREATE TABLE IF NOT EXISTS Microcontroler(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
        cur.execute("INSERT INTO Microcontroler(Name) VALUES('PIC18F26K22')")
        cur.execute("INSERT INTO Microcontroler(Name) VALUES('PIC18F628A')")
        cur.execute("INSERT INTO Microcontroler(Name) VALUES('PIC12F629')")
        cur.execute("INSERT INTO Microcontroler(Name) VALUES('ATmega32')")
        cur.execute("INSERT INTO Microcontroler(Name) VALUES('ATtiny2313')")
        
        print "\nMethod1: get all records, return a result set and print data row by row\n"
        
        cur.execute("SELECT * FROM Microcontroler")
        rows = cur.fetchall()
        for row in rows:
            print row
        
        print "\nMethod2: determine the number of rows and fetch the rows one by one\n"
        
        cur.execute("SELECT * FROM Microcontroler")
        numrows = int(cur.rowcount)
        for i in range(numrows):
            row = cur.fetchone()
            print row[0], row[1]
        
        print "\nMethod3: print table using dictionary cursor and refer data by column name\n"
        
        cur = mycon.cursor(mydb.cursors.DictCursor)
        cur.execute("SELECT * FROM Microcontroler")
        rows = cur.fetchall()
        for row in rows:
            print " %s %s" % (row["Id"], row["Name"])
            
        print "\nPrint table column names from cursor object's description attribute\n"
        
        desc = cur.description
        print "%s %3s" % (desc[0][0], desc[1][0])

except mdb.Error, e:
  
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
    
finally:    
        
    if mycon:    
        mycon.close()

rezultat:

MySQL: 5.5.28

Method1: get all records, return a result set and print data row by row

(1L, 'PIC18F26K22')
(2L, 'PIC18F628A')
(3L, 'PIC12F629')
(4L, 'ATmega32')
(5L, 'ATtiny2313')

Method2: determine the number of rows and fetch the rows one by one

1 PIC18F26K22
2 PIC18F628A
3 PIC12F629
4 ATmega32
5 ATtiny2313

Method3: print table contents using the dictionary cursor and refer data by column names

1 PIC18F26K22
2 PIC18F628A
3 PIC12F629
4 ATmega32
5 ATtiny2313

Print table column names from cursor object's description attribute

Id Name

Resurse