Online visitors

People learning around the world

Online

—

Visitors exploring W3Schools

Online visitors by country Circles show approximate country locations. The country list provides the visitor counts.

Circles represent countries, not exact visitor locations.

Country

Online

Loading country activity…

    Python Database Connection

    Python Programming Tutorials


    Programmers can expect Python to make database programming more painless and straightforward work, supplying the Python database API with a database-neutral programming interface for various databases.

    These are:

    • MySQL
    • SQLite
    • MS SQL
    • PostgreSQL
    • Informix
    • Sybase
    • Inter-base
    • Oracle etc….

    The DB-API is the standard for Python's database interface.

    What is Database?

    The database is a collection of organized information that can easily be used, managed, update, and they are classified according to their organizational approach.

    The Python Database interfaces are categorized into two. These are:

    Generic Database Interface

    Most Python's database interface remains to Python's DB-API standard, and most of the databases have ODBC support. Other than that, the Java database usually supports JDBC, and programmers can work with that from Jython.

    Relational Database System Interface

    This employs a relational model with support of SQL.  Lists of general-purpose database systems are:

    1. Firebird
    2. Informix
    3. SAP DB
    4. MS SQL server
    5. Access
    6. Ingres
    7. MySQL etc….

    Other than that, the lists of data warehouse database systems are:

    1. Teradata
    2. IBM Netezza

    There are other non-relational categories of databases. These are:

    • Record-based databases
    • XML databases
    • Graph databases

    Native Python databases are:

    • SnakeSQL
    • Buzhug

    What Database API Includes

    Using Python structure, DB-API provides standard and support for working with databases. The API consists of:

    • Bring in the API module
    • Obtain database connection
    • Issue SQL statements and then store procedures
    • Close the connection

    Benefits of Python Database Programming

    • Programming in Python is considerably simple and efficient with compared to other languages, so as the database programming
    • Python database is portable, and the program is also portable so both can give an advantage in case of portability
    • Python supports SQL cursors
    • It also supports Relational Database systems
    • The API of Python for the database is compatible with other databases also
    • It is platform-independent

    Defining MySQL Database

    It is an interface for associating the SQL database server from Python and uses the DB-API of Python to work.

    How to Implement MySQL Database

    To access the MySQL database using Python, you must install it on your machine; Then, type the script below to implement MySQL within your program:

    import MySQLdb
    

    If any error occurs then it means that the MySQL module is not installed and programmers can download it from -  https://sourceforge.net/projects/mysql-python/

    Database Program Using Python

    # importing the module
    import MySQLdb
    
    # opening a database connection
    db = MySQLdb.connect  ("localhost","testprog","stud","PYDB")
    
    # define a cursor object
    cursor = conn.cursor
    
    # drop table if exists
    Cursor.execute("IF STUDENT TABLE EXISTS DROP IT")
    
    # query
    sql =   "CREATE TABLE STUDENT (NAME CHAR(30) NOT NULL, CLASS CHAR(5), AGE INT, GENDER CHAR(8), MARKS INT"
    
    # execute query
    cursor.execute(sql)
    
    # close object
    cursor.close()
    
    # close connection
    conn.close()

    Database Operations

    There are various operations that programmers can perform within the Python program. To deal with these statements, you must have a good knowledge of Database programming and SQL.

    Database Operations
    Environment Variables Description
    INSERT It is an SQL statement used to create a record into a table.
    READ Fetches useful information from the database.
    UPDATE It is used to update those available or already existing record(s).
    DELETE It is used to delete records from the database.
    ROLLBACK It works like "undo", which reverts all the changes that you have made.


    Found This Page Useful? Share It!
    Get the Latest Tutorials and Updates
    Join us on Telegram

    Notifications

    Updates are enabled for this browser.

    Receive new tutorials, articles and learning resources. You can turn updates off anytime.