Python MySQL and SQLite connectivity

In Python Programming we can use any kind of database to our data/values/information

So, The first step of implementing a database in your python program is the installation of your database modules. Following are some commonly used python database modules that you have to install on your system

1) MySQL

2) SQLite


1. Mysql :

Unlike SQLite, there’s no default Python SQL module that you can use to connect to a MySQL database. Instead, you’ll need to install a Python SQL driver for MySQL in order to interact with a MySQL database from within a Python application. One such driver is mysql-connector-python. You can download this Python SQL module with pip:

pip install mysql-connector-python

import mysql.connector
from mysql.connector import Error

def create_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection

connection = create_connection("localhost", "root", "")

2. SQLite

When you connect to an SQLite database file that does not exist, SQLite automatically creates the new database for you.

To create a database, first, you have to create a Connection object that represents the database using the connect() function of the sqlite3 module.

For example, the following Python program creates a new database file pythonsqlite.db in the c:\sqlite\db folder.

And good thing is, You don't need to install sqlite3 module. It is included in the standard library (since Python 2.5).(Stackoverflow)

Follwoing are the steps to implement SQLite in your python program 

1) Import sqlite3 module in your program 

2) Create the connection and object of sqlite3 using [sqlite3.connect()] method 

    Example : obj-name = sqlite3.connect('Your-Database-Name.db')

    So it will create the object of sqlite3 with  your database name with (dot) .db extension

 3) Execute queries using that object : Example

    obj-name.execute("INSTERT INTO table-name()")

4) Creating cursor 

import sqlite3  
  
conn = sqlite3.connect('javatpoint.db')  
print "Opened database successfully";  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (1, 'Ajeet', 27, 'Delhi', 20000.00 )");  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (2, 'Allen', 22, 'London', 25000.00 )");  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (3, 'Mark', 29, 'CA', 200000.00 )");  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (4, 'Kanchan', 22, 'Ghaziabad ', 65000.00 )");  
  
conn.commit()  
print "Records inserted successfully";  
conn.close()  
إرسال تعليق (0)
أحدث أقدم