unit-5 SQL 1 creating a databse connection.pptx

Sql

1. CHAPTER-5 INTERFACE PYTHON WITH SQL

2. INTERFACE PYTHON WITH SQL DATABASE Databases pay a vital role in the efficient working of an organization( schools, companies)etc.. Every Organizations depends upon large databases. E.g In schools ,Admin section will store the details related to Students(personal and academic wise),teaching and non teaching staffs details etc. In database ,Data are organized into rows, columns and tables and it is indexed to make it easier to find relevant information . So it become necessary to develop project/software using any programming language like python in such a manner which can interface with such databases which support MYSQL .

3. INTERFACE PYTHON WITH SQL DATABASE SQL is just a query language, it is not a database. To perform SQL queries, we need to install any database for example Oracle, MySQL, MongoDB, PostgreSQL SQL, SQL Server, DB2 etc. Python supports all the above Databases . Python supports DDL,DML statements which is used to create and manipulate data in Databases( My Sql). The Python standard for database interfaces is the Python DB-API(Data Base-Application Programming Interface).

4. INTERFACE PYTHON WITH SQL DATABASE While designing real life applications ,certain situations arise pertaining to storing some important and necessary information by the user . Usually data inputted by the user along with generated output is displayed but not stored like in My SQL Query and the Displayed output will be erased once we exit from the database. This limitations can be overcome by sending the output generated and saving the input fetched from the user in a database created at the Backend of the application( My SQL ) . The input is fetched from the user using Python interface. This is termed as Front end Interface of the application . STORE DATA CODING BACK END(My SQL FRONT END (PYTHON)

5. INTERFACE PYTHON WITH SQL DATABASE Generalised form of Interface of python with SQL Database can be understood with the help of this diagram. Form/any user interface designed in any programming language is Front End where as data given by database as response is known as Back-End database My SQL PYTHON BACK END FRONT END

6. WHY CHOOSE PYTHON FOR DATABASE PROGRAMMING Programming more efficient and faster compared to other languages. Portability of python programs. Support platform independent program development. Python supports SQL cursors(pointers). Python itself take care of open and close of connections . Python supports relational database systems . Porting of data from one dbms to other is easily possible as it support large range of APIs for various databases.

7. INSTALLING MYSQL-CONNECTOR To establish connectivity between Python and MySQL ,we require Python Database Interfaces and APIs. We must download a separate DB API module for each database we need to access. Suppose we need to access an Oracle database as well as a MySQL database, we must download both the Oracle and the MySQL database modules . The DB API provides a minimal standard for working with databases using Python structures and syntax wherever possible. This API includes the following − ● Importing the API module. ● Acquiring a connection with the database. ● Issuing SQL statements and stored procedures. ● Closing the connection

8. INSTALLING MYSQL-CONNECTOR Steps for python MySQL connectivity Mysql.connector -Library or package to connect from python to MySQL. 1 . Install Python 2. Install MySQL 3. Open Command prompt 4. Switch on internet connection 5. Type pip install mysql-connector-python COMMAND and EXECUTE . 6. Open python IDLE 7. import mysql.connector

9. INSTALLING MYSQL-CONNECTOR( STEP:5 ) GOAL: Connect Python with MySQL. Your system should be connect with internet. Python and MySQL should be installed in your system. Install MySQL connector go to Command prompt (run cmd) Click OK Button, command Prompt will open .

10. INSTALLING MYSQL-CONNECTOR( STEP:5.. ) Do ensure that MySQL-connector is installed in the same folder as Python and takes the path as NOTE: if you are not installing it through the direct path sometimes MYSQL connector will not work properly

11. INSTALLING MYSQL-CONNECTOR( STEP:5….) Once you have set the path, type the command as – After type this command, successfully MySQL connector will installed in your computer. COMMANDS WILL RUN AUTOMATICALLY ….

12. OPEN PYTHON IDLE( STEP 6 ) Now we need to check whether mysql connector is properly installed or not – (1) Open Python IDLE(Python shell) (2) Type the command import mysql.connector If it is successfully installed then no error message will be there.

13. ESTABLISH CONNECTION( STEP 7 ) First Step is finished, Next step is Using MYSQL in Python scripts is to make a connection to the database that you wish to use .(MAKE SURE THE DATABASE YOU ARE GOING TO CONNECT TO THE Python IS ALREADY CREATED IN MySQL) Open python file in script mode and type the below command. Once Type finished , Save it and Run the module, If every thing is worked fine you will get a message in Python Shell. Charset is optional.

14. ESTABLISH CONNECTION( STEP 7 ) Make sure the username and password you are entering for MySQL should be correct. Mostly it is “root” as user id and pwd as “root” or ” “ .Here it is root for both. Your server/PC: normally it is localhost (if any other server is connected to your PC, then you have to give the IP address of it) Which database you are going to connect that database name should be mentioned here.(Pls make sure Database is already created.) User ID and Password of My SQL Character set which is supported by Python. It is Unicode8Bit encoding System Mostly host,user,passwd and charset will be common for all . Charset is optional.

15. ESTABLISH CONNECTION( STEP 7 ) Make sure the username and password you are entering for MySQL should be correct. Mostly it is “root” as user id and pwd as “root” or ” “ .Here it is root for both. Your server/PC: normally it is localhost (if any other server is connected to your PC, then you have to give the IP address of it) Which database you are going to connect that database name should be mentioned here.(Pls make sure Database is already created.) User ID and Password of My SQL Character set which is supported by Python. It is Unicode8Bit encoding System connect() function creates a connection to the MySQL Server and returns a MySQL connection Object. Charset is optional.

16. PRACTICAL IMPLEMENTATION:1 TO ESTABLISH A CONNECTION BETWEEN MYSQL AND PYTHON Charset is optional.