Python Databases: Python Database is a collection of information that is organized so that it can be easily accessed, managed and updated. Python standard for database interface is python DB-API.
Various Python Databases
Python database supports a variety of wide range of data servers listed here as follows:
- GadFly
- mSQL
- MySQL
- PostgreSQL
- Microsoft SQL Server 2000
- Informix
- Interbase
- Oracle
- Sybase
You must download a separate DB API module for each database, you need to access.
Python MySQL Database
What is MYSQLdb?
MYSQLdb acts as an interface for connecting to an MYSQL database server from python.
Installing MYSQLdb in Python
First, check on your device whether you have installed MYSQldb as follows:
import MySQLdb
Once if you get a message like this means it was not installed:
Output
Traceback (most a recent call last):
File “test.py”,line 3, in <module>
import MySQLdbImport
Error: No module named MySQLdb
While installing the MySQL DB module, use the following commands.
- At Ubuntu, use the following command – $ sudo apt-get install python-pip python-dev libmysqlclient-dev
- For Fedora, use the following command- $ sudo dnf install python python-devel mysql-devel redhat-rp
- config gcc In Python command prompt, use the following command – pip install MySQL-python
Python Database Connection
Before you connect to the database, make sure the following settings.
- You have created a database TESTDB.
- You have created a table EMPLOYEE in TESTDB.
- This table has fields FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
- User ID “testuser” and password “test123” are set to access TESTDB.
- Python module MySQLdb is installed properly on your machine.
Example
import MySQLdb
# Open database connection
db=MySQLdb.connect ("localhost","testuser","test123","TESTDB" )
#prepare a cursor object using a cursor() method
cursor=db.cursor()
#execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")
#Fetch a single row using fetchone() method.
data=cursor.fetchone()
print "Database version: %s " % data
#disconnect from server
db.close()
Output: Database version: 5.0.45
Creating a Database in Python
If you want to create any Python database, just follow these commands
Example
import MySQLdb db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql= """CREATE TABLE EMPLOYEE(#Create table as per requiremet)FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )""" cursor.execute(sql)
db.close() #disconnect from the server
Inserting
It is used when you want to create new records into a Python database table.
Example
import MySQLdb db=MySQLdb.connect("localhost","testuser","test123","TESTDB" ) #Open database connection
cursor=db.cursor()
sql= """INSERT INTO EMPLOYEE(FIRST_NAME, #Prepare SQL query to INSERT a record into the database. LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:cursor.execute(sql)
db.commit()#Commit your changes in the database
except:db.rollback()#Rollback in case there is any error
db.close()#disconnect from a server
If you want to execute the above program dynamically, written as follows:
Example
import MySQLdb db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )#Open database connection
cursor = db.cursor()
sql="INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME)\VALUES ('%s', '%s','%d', '%c', '%d' )" %('Mac', 'Mohan', 20, 'M', 2000)
try: cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
*Program for getting records of employee table having a salary more than 100:
Example
import MySQLdb db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > '%d'" % (1000)
try: cursor.execute(sql)
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \ (fname, lname, age, sex, income )
except:
print "Error: unable to fecth data"
db.close()
Output
fname=Mac, lname=Mohan, age=20, sex=M, income=2000
Primary Key
For creating a table, you should also create a column with a unique key for each record. So, it can be done by defining a PRIMARY KEY.
Example
import mysql.connector(mydb = mysql.connector.connect host="localhost",user="yourusername",passwd="yourpassword", database="mydatabase")
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
If the table already exists, use the ALTER TABLE keyword.
Example
import mysql.connector mydb = mysql.connector.connect(host="localhost",user="yourusername",passwd="yourpassword",database="mydatabase")
mycursor=mydb.cursor()
mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
Inserting Multiple Rows
If you would like to insert multiple rows in the Python database, use the executemany( ) method.
Example
import mysql.connector mydb=mysql.connector.connect(host="localhost",user="yourusername",passwd="yourpassword",database="mydatabase")
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.")
Output: 13 records were inserted.
MYSQL select
If you would like to select all records from the customer table.
Example
import mysql.connectomydb=mysql.connector.connect( host="localhost", user="yourusername",passwd="yourpassword",database="mydatabase")
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall() for x in myresult: print(x)
Output
(1, ‘John’, ‘Highway 21’)
(2, ‘Peter’, ‘Lowstreet 27’)
(3, ‘Amy’, ‘Apple st 652’)
(4, ‘Hannah’, ‘Mountain 21’)
(5, ‘Michael’, ‘Valley 345’)
(6, ‘Sandy’, ‘Ocean blvd 2’)
(7, ‘Betty’, ‘Green Grass 1’)
(8, ‘Richard’, ‘Sky st 331’)
(9, ‘Susan’, ‘One way 98’)
(10, ‘Vicky’, ‘Yellow Garden 2’)
(11, ‘Ben’, ‘Park Lane 38’)
(12, ‘William’, ‘Central st 954’)
(13, ‘Chuck’, ‘Main Road 989’)
(14, ‘Viola’, ‘Sideway 1633’)
(15, ‘Michelle’, ‘Blue Village’)
Selecting Columns
If you want to select specific or some category then this command can be used.
Example
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", passwd="yourpassword", database="mydatabase" )
mycursor = mydb.cursor()
mycursor.execute("SELECT name, address FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
MYSQL where
If you want to filter results, we should use were an option.
Example
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", passwd="yourpassword", 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)
MYSQL Order By
We can sort the result in the form of ascending and descending order. So, order by keyword sorts the results in ascending order, and DESC descends order.
Example
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", passwd="yourpassword", database="mydatabase") mycursor=mydb.cursor() sql="SELECT * FROM customers ORDER BY name" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult:print(x)
DESC
Example
import mysql.connector mydb=mysql.connector.connect( host="localhost", user="yourusername",passwd="yourpassword",database="mydatabase") mycursor=mydb.cursor() sql="SELECT * FROM customers ORDER BY name DESC" mycursor.execute(sql) myresult = mycursor.fetchall( ) for x in myresult: print(x)
Drop-Table
We can delete an existing table by using a drop table statement.
Example
import mysql.connector mydb = mysql.connector.connect( host="localhost",user="yourusername",passwd="yourpassword",database="mydatabase") mycursor=mydb.cursor() sql="DROP TABLE customers" mycursor.execute(sql)
Drop Only if Exists
If you wish that you want to delete the table, but it is already deleted and want to make sure whether it has removed or not.
Example
import mysql.connector mydb = mysql.connector.connect( host="localhost",user="yourusername",passwd="yourpassword",database="mydatabase") mycursor=mydb.cursor(sql = "DROP TABLE IF EXISTS customers" mycursor.execute(sql)
MySQL Update
If you want to change anything, it can be done by using the update keyword.
Example
import mysql.connector mydb = mysql.connector.connect( host="localhost",user="yourusername",passwd="yourpassword",database="mydatabase") 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")
Python SQL Limit
IF you want you can limit the records, as per your requirement.
Example
import mysql.connector mydb = mysql.connector.connect( host="localhost",user="yourusername",passwd="yourpassword",database="mydatabase")
mycursor=mydb.cursor( )
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult=mycursor.fetchall()
for x in myresult:
print(x)
MySQL joins
You can combine two or more rows or tables by using combine.
Example
import mysql.connector mydb = mysql.connector.connect(host="localhost",user="yourusername",passwd="yourpassword",database="mydatabase") mycursor=mydb.cursor() 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)
Note: You can use JOIN instead of INNER JOIN. They will both give you the same result.
There are different types of joins. They are 1.left join, 2.Right join.
1. Left Join
Example
sql="SELECT \ users.name AS user,\ products.name AS favorite \ FROM users \ LEFT JOIN products ON users.fav = products.id"
Output:
(‘John’, ‘Chocolate Heaven’)
(‘Peter’, ‘Chocolate Heaven’)
(‘Amy’, ‘Tasty Lemon’)
(‘Hannah’, None)
(‘Michael’, None)
2. Right Join
Example
sql="SELECT \ users.name AS user,\ products.name AS favorite\ FROM users\ RIGHT JOIN products ON users.fav = products.id\