Scribd Logo
Upload

Welcome to Scribd!

  • 101 views

    Python To MySql Connection

    Uploaded by

    Pranav Pratap Singh
    The document discusses connecting Python with SQL databases using the Python DB-API standard. It provides examples of connecting to a MySQL database, creating and modifying tables, and performing insert, update, and read operations. The key points are that the DB-API standard supports connections to many database types, and modules like MySQLdb allow connecting Python to MySQL for CRUD operations.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Python To MySql Connection

    Uploaded by

    Pranav Pratap Singh
    101 views16 pages
    The document discusses connecting Python with SQL databases using the Python DB-API standard. It provides examples of connecting to a MySQL database, creating and modifying tables, and performing insert, update, and read operations. The key points are that the DB-API standard supports connections to many database types, and modules like MySQLdb allow connecting Python to MySQL for CRUD operations.

    Original Description:

    Python to MySql Connection

    Original Title

    Python to MySql Connection

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses connecting Python with SQL databases using the Python DB-API standard. It provides examples of connecting to a MySQL database, creating and modifying tables, and performing insert, update, and read operations. The key points are that the DB-API standard supports connections to many database types, and modules like MySQLdb allow connecting Python to MySQL for CRUD operations.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    101 views16 pages

    Python To MySql Connection

    Uploaded by

    Pranav Pratap Singh
    The document discusses connecting Python with SQL databases using the Python DB-API standard. It provides examples of connecting to a MySQL database, creating and modifying tables, and performing insert, update, and read operations. The key points are that the DB-API standard supports connections to many database types, and modules like MySQLdb allow connecting Python to MySQL for CRUD operations.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 16
    At a glance
    Powered by AI
    The key takeaways are that the Python DB-API provides a standard interface for connecting to and interacting with different database systems from Python. It uses Connection and Cursor objects to manage connections and execute queries respectively.

    The main components of the Python DB-API are the Connection object, which manages the connection to the database, and the Cursor object, which is used to execute queries and fetch results. The Connection object provides methods like commit, rollback, close, while the Cursor object provides methods like execute, fetchone, fetchall etc.

    The main methods of the Connection object are close(), commit(), rollback() and cursor(). The main methods of the Cursor object are execute(), executemany(), fetchone(), fetchmany(), fetchall() and rowcount(). These methods allow performing operations like connecting, executing queries and fetching results.

    CONNECTING PYTHON WITH SQL

    DATABASE
    Introduction
    • The Python standard for database interfaces is the Python DB-API.
    Most Python database interfaces adhere to this standard.
    • We can choose the right database for our application. Python
    Database API supports a wide range of database servers such as −
    • GadFly
    • mSQL
    • MySQL
    • PostgreSQL
    • Microsoft SQL Server 2000
    • Informix
    • Interbase
    • Oracle
    • Sybase
    Download DB API
    You must download a separate DB API module for each database
    you need to access. For example, if you need to access an Oracle
    database as well as a MySQL database, you 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
    What is MySQLdb?
    MySQLdb is an interface for connecting to a MySQL database server
    from Python. It implements the Python Database API v2.0 and is built
    on top of the MySQL C API.
    How do I Install MySQLdb?
    Before proceeding, you make sure you have MySQLdb installed on
    your machine. Just type the following in your Python script and
    execute it −
    #!/usr/bin/python
    import MySQLdb
    If it produces the following result, then it means MySQLdb module is
    not installed −
    Traceback (most recent call last):
    File "test.py", line 3, in <module>
    import MySQLdb
    ImportError: No module named MySQLdb
    Connector for Python
    Installing Connector/Python on Microsoft Windows
    Managing all of your MySQL products, including MySQL
    Connector/Python, with MySQL Installer is the
    recommended approach. It handles all requirements
    and prerequisites, configurations, and upgrades.

    Prerequisite. The Microsoft Visual C++ 2015


    Redistributable must be installed on your system.
    To install MySQLdb module, download it from
    https://sourceforge.net/projects/mysql-python/
    Setup installation steps
    1. Install Python 2.7.0(ignore if already done)
    2. Install MySQL 5.1.33 Server
    3. Install Microsoft Visual C++ 2015
    4. Install MySQL-Python Connector
    Database Connection
    • Before connecting to a MySQL database, make
    sure of the followings −
    • 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 "root" and password "1234" are set to
    access TESTDB.
    • Python module MySQLdb is installed properly on
    your machine.
    Example1-Test Connection
    Following is the example of connecting with MySQL database "TESTDB“
    #import mysql.connector
    import MySQLdb
    # Open database connection
    db = MySQLdb.connect("127.0.0.1","root","1234","testdb")
    # prepare a cursor object using 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()

    While running this script, it is producing the following result in


    my windows machine.
    Database version : 5.1.33-community
    Example2-Creation of table
    import MySQLdb
    # Open database connection
    db = MySQLdb.connect("127.0.0.1","root","1234","testdb" )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    #Drop table if it already exist using execute() method.
    cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
    # Create table as per requirement
    sql = """CREATE TABLE EMPLOYEE (FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),AGE INT,SEX CHAR(1), INCOME FLOAT )"""
    # execute SQL query using execute() method.
    cursor.execute(sql)
    # disconnect from server
    db.close()
    Example3-Insertion
    import MySQLdb
    db = MySQLdb.connect("127.0.0.1","root","1234","testdb")
    cursor = db.cursor()
    name=raw_input("What is your first name? ") #raw_input() for String input
    lname=raw_input("What is your last name? ")
    age = input("What is your age? ") #input() for Numeric
    inputgender=raw_input("What is your gender?")
    income=input("What is your monthly income?")
    # Prepare SQL query to INSERT a record into the database.
    try:
    cursor.execute("""INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE,SEX,
    INCOME)
    VALUES (%s,%s,%s,%s,%s)""",(name,lname,age,gender,income))
    # Commit your changes in the database
    db.commit()
    except:
    # Rollback in case there is any error
    db.rollback()
    db.close()
    Example4-Updation of record
    import MySQLdb
    db = MySQLdb.connect("127.0.0.1","root","1234","testdb")
    cursor = db.cursor()
    # Prepare SQL query to UPDATE required records
    sql = "UPDATE EMPLOYEE SET AGE = AGE + 5 \
    WHERE SEX = '%c'" % ('M')
    try:
    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the database
    db.commit()
    except:
    # Rollback in case there is any error
    db.rollback()
    # disconnect from server
    db.close()
    READ Operation
    • READ Operation on any database means to fetch some useful
    information from the database.
    • Once our database connection is established, you are ready to
    make a query into this database. You can use
    either fetchone() method to fetch single record
    or fetchall() method to fetech multiple values from a database
    table.
    • fetchone() − It fetches the next row of a query result set. A result
    set is an object that is returned when a cursor object is used to
    query a table.
    • fetchall() − It fetches all the rows in a result set. If some rows have
    already been extracted from the result set, then it retrieves the
    remaining rows from the result set.
    • rowcount − This is a read-only attribute and returns the number of
    rows that were affected by an execute() method.
    Example-Read operation
    The following procedure queries all the records from EMPLOYEE table having salary more than 1000-
    import MySQLdb
    # Open database connection
    db = MySQLdb.connect("127.0.0.1","root","1234","TESTDB" )
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    sql = "SELECT * FROM EMPLOYEE \
    WHERE INCOME > '%d'" % (1000)
    try:
    # Execute the SQL command
    cursor.execute(sql)
    # Fetch all the rows in a list of lists.
    results = cursor.fetchall()
    for row in results:
    fname = row[0]
    lname = row[1]
    age = row[2]
    sex = row[3]
    income = row[4]
    # Now print fetched result
    print (fname, lname, age, sex, income )
    except:
    print "Error: unable to fecth data"
    # disconnect from server
    db.close()
    Important links
    1. Run python 2.7.14
    2. To install MySQLdb module, download it from
    https://sourceforge.net/projects/mysql-python/
    3. Prerequisite. The Microsoft Visual C++ 2015
    Redistributable must be installed on your system
    4. Microsoft MySql intaller or
    Download MySQL from MySQL.com
    OR https://dev.mysql.com/downloads/mysql/
    Python DB-API
    • Python DB-API
    – Database application programming interface
    – Portable across several databases
    – Consists of Connection and Cursor data
    objects
    • Connection data object (Fig. 22.30)
    – Accesses database through four methods
    • Cursor data object (Fig. 22.31)
    – Manipulates and retrieves data

    15
    Python DB-API
    Connection Data Objects Description
    close Closes the connection to the database.

    commit Commits (saves) a transaction (i.e., interaction with a database through SQL keywords
    and commands).
    rollback Exits a pending transaction without saving changes. Returns the user to the beginning
    of the transaction.
    cursor Returns a new Cursor object or the current connection.

    Fig. 22.30 Connection data objects for Python DB-API.


    Cursor Data Objects Description
    rowcount Returns the number of rows retrieved by the last execute method call.

    close Closes the Cursor object.


    execute( operation ) Executes a database query or command. Return values not defined.

    executemany( operation, Executes a database query or command against a set of parameters. Return values not
    defined.
    parameters )
    fetchone Returns the next row of a query result.
    fetchmany( size ) Returns a set of rows—defined in the parameter—for a query result set.

    16 fetchall
    Returns all the rows of a query result set.

    Fig. 22.31 Some Cursor data objects for PB-API.

    You might also like