Database Using Python: A Tutorial

0 downloads 0 Views 84KB Size Report
Dec 13, 2016 - 1.1 dfdfd. 1. Download mysql workbench using the following link: ... Search at the table The SQL Select statement is used to retrieve a set of.
Database Using Python: A Tutorial Ahmed Othman Eltahawey ∗ December 13, 2016 In python file, you have to first establish a connection between your file and the database. After that, you can add, search, delete or update your database. Moreover, you can retrieve the data from the database, make any operation on it then re-add it to the database. The database operations are performed using SQL statements. In the first section of this chapter, a set of useful links is provided that could help you in downloading necessary database program and python connector. Moreover, a link to a small video describing how to create database using mysql. In the second section, a description of how to make the connection between python and database is provided. In the third section, a quick review of the basic SQL statements is presented. In the forth section, the main database operations are performed using python.

1

Useful Links

1.1

dfdfd

1. Download mysql workbench using the following link: http://dev.mysql.com/downloads/workbench/ 2. Download the connector from python to mysql form the following link: http://dev.mysql.com/downloads/connector/python/ 3. Create your own database: Use the video at my youtube channel: https://www.youtube.com/channel/UCmjn4mwdKoCaE_Z7OuFWIWQ ∗

Ahmed Othman is an assistant professor at the Information System department, Faculty of Computers & Informatics, Suez Canal University, Ismailia, Egypt. e-mail: [email protected], Web: http: ahmedeltahawy.weebly.com/

1

2

Establish a python connection to MYSQL database

The following code is used to establish a connection between the python program and mysql database: from mysql . c o n n e c t o r i m p o r t MySQLConnection i m p o r t mysql . c o n n e c t o r C o n n e c t i o n = mysql . c o n n e c t o r . c o n n e c t ( h o s t =” l o c a l h o s t ” , u s e r = u s e r , passwd = p a s s w o r d , d a t a b a s e = d a t a b a s e ) You first have to import the connector class into your program, then you can use it. It is a built in function at python (mysql.connector.connect) that use the connector which you installed in step 2 at section 1. This function make the link between python and the database. The function accept 4 inputs as follows: 1. The host: here it is local host as your database on your pc not at the server. 2. The user name: which you set when you create your database after downloading the mysql. Usually it is ”root” 3. The password: which you set when creating the database. 4. The database: the name of the database you created. Now you created a database and link it to python and the result of the connection is at the variable connection.

3

Basic SQL statement

The SQL language is responsible to deal with database. The process of adding new record, retrieving a record, update existing record and delete a record are the four main database operations. Here, a short description of the four basic SQL statements is presented If we have a table called Students, that include four attributes: 1. The student ID 2. The student Name 3. The student Address 4. The student Email. A view of the table could be as shown in Table. 1. Using Table 1, the basic operations could be performed as follows: 2

ID 1 2

Name Ahmed Aly

Address Cairo Suez

Email [email protected] [email protected]

Table 1: The Students table. 1. Add new record to the table. The SQL statement insert is used to add a new record to the table: q u e r y 2 = ( ” INSERT INTO s t u d e n t s ( ID , Name , A d d r e s s , E m a i l ) VALUES ( 3 , ’ I b r a h i m ’ , ’ A l e x a n d r i a ’ , ’ c@gmail . com ’ ) ; ” ) The insert statement means to insert into the table student which has the following attributes (‘ID‘,‘Name‘, ‘address‘, ‘Email‘) the following values (3, ’Ibrahim’, Alexandria,[email protected]). and the values should be ordered in the same order of attributes. After executing the statement, the tables will looks as shown in table 2. ID 1 2 3

Name Ahmed Aly Ibrahim

Address Cairo Suez Alexandria

Email [email protected] [email protected] [email protected]

Table 2: The Student table. 2. Search at the table The SQL Select statement is used to retrieve a set of columns from the table as follows: • Select all column from table student: SELECT ∗ from S t u d e n t s • Select set of column from student: SELECT ID , Name from S t u d e n t s Here it selects the whole rows but only for ID and Name columns • Select Specific row or set of rows and all or set of columns SELECT ID , Name from S t u d e n t s where ID = 1

3

The previous statement return the ID and the Name of only one row which has ID = 1 because ID should be the primary key of the table. The following statement may return more than one row because you may find more than one student with name = Ahmed. SELECT ID , Name from S t u d e n t s WHERE Name = ’Ahmed ’ 3. Update a value inside the table: You must first locate the row that you want to update then give it a new value as follows: U p d a t e S t u d e n t s s e t Name = ’ Mohamed ’ WHERE ID = ’ 1 ’ In this statement, the name of the student with ID = 1 will changed from Ahmed to Mohamed. 4. Delete From the table Again you first locate the row or set of rows you would like to delete, then delete them: D e l e t e from s t u d e n t s where ID = 10 This will delete the row of the student has ID =10. If you want to delete more than one row, you can specify the condition in the where clause D e l e t e from s t u d e n t where a d d r e s s = Ismailia Here, all student live in Ismailia will be deleted from the table

4

Database using Python

Now back to python, we already established a connection in the variable Connection at section 1. The second step is to define the cursor object which has a lot of useful methods. The cursor class is defined using the following statement: cursor = Connection . cursor ( ) The cursor object will be responsible for carrying all SQL statement from python to be executed on the database. It is defined using the connection variable. After creating the cursor object, you can assign any SQL statement to a variable as string. Then the cursor will be responsible to execute the SQL statement as follows:

4

q u e r y = ( ” SELECT ∗ FROM S t u d e n t s ” ) c u r s o r . execute ( query ) The cursor class will carry the SQL statement and execute it at the database. As the query carry the following SQL statement (Select * from student) which should return the whole table of student. Therefore, the cursor now have a full access over the student data. The cursor class has a lot of functions that allow you to deal with the database such as fetchall(). This function returns all the rows found at the table in the variable row. row = c u r s o r . f e t c h a l l ( ) Now, the variable Row has all rows of the table, you can access the information at Row as follows: p r i n t ( row ) −> A l l rows a t s t u d e n t s w i l l a p p e a r p r i n t ( row [ 0 ] ) −> p r i n t t h e f i r s t row o n l y p r i n t ( row [ 0 ] [ 0 ] ) > I d v a l u e o f t h e f i r s t row w i l l a p p e a r You may find more function under the cursor class at the following link: https://www.python.org/dev/peps/pep-0249/#cursor-methods Below, you can find the complete code for establishing a connection to student table and retrieving all data of student to row variable. i m p o r t mysql . c o n n e c t o r c o n n e c t i o n = mysql . c o n n e c t o r . c o n n e c t ( h o s t =” l o c a l h o s t ” , u s e r = u s e r , passwd = p a s s w o r d , d a t a b a s e = d a t a b a s e ) cursor = connection . cursor () q u e r y = ( ” SELECT ∗ FROM s t u d e n t ” ) c u r s o r . execute ( query ) row= c u r s o r . f e t c h a l l ( ) If you want to add new row to student, the query variable at the previous code should be changed to: q u e r y = ( ” INSERT INTO S t u d e n t s ( ID , Name , a d d r e s s , E m a i l ) VALUES ( 4 , ’ I b r a h i m ’ , ’ A l e x a n d r i a ’ , ’ c@gmail . com ’ ) ; ” ) C o n n e c t i o n . commit ( )

5

Connection.commit() is a necessary statement after any modification to the table by adding, updating or deleting. However, if you only want to view the data at the table by select statement, you don’t need to commit. As well, you can make the other operation described in section 2.

6