Python - Databases - Connecting with MySQL database from Python

[akash-coded]

WAP to establish a connection with a MySQL database server from Python. Print a success or failure message.

After successfully connecting with the DB server, read some data from a table in the DB and display it using Python.

[JesusBel16]

import mysql.connector
from mysql.connector.errors import Error

conn = mysql.connector.connect(host="localhost",database="sakila", user="root", password="Password123")
if conn.is_connected():
    print("It's connected")
    cur = conn.cursor()
    cur.execute("Select * from actor")
    data = cur.fetchall()
    print(data)
else:
    print(Error.print())

[zenberr]

Code:

import mysql.connector

dbconnect = mysql.connector.connect(
  host="localhost",
  user="root",
  password="Zefd43S",
  database="employees"
)

mycursor = dbconnect.cursor()

try:
    print("Successfully connected to MYSQL, fetching data in process...")
    mycursor.execute("SELECT dname, loc FROM dept")
    myresult = mycursor.fetchall()
    for x in myresult:
        print(x)
except mysql.connector.Error as err:
    print(err)
    
mycursor.close()

Output:

Successfully connected to MYSQL, fetching data in process...
('ACCOUNTING', 'NEW YORK')
('RESEARCH', 'DALLAS')
('SALES', 'CHICAGO')
('OPERATIONS', 'BOSTON')

[Jyothig7]

import mysql.connector as sql
import pandas as pd

db_connection = sql.connect(host="localhost",user="root",password="Password123",database="sakila")
print(db_connection)

db_cursor = db_connection.cursor()

db_cursor.execute('SELECT * FROM actor')

table_rows = db_cursor.fetchall()

df = pd.DataFrame(table_rows)
print(df)

Output:
runfile('C:/Users/2000077602/Desktop/Pandas/DatabaseConnection_Test.py', wdir='C:/Users/2000077602/Desktop/Pandas')
<mysql.connector.connection_cext.CMySQLConnection object at 0x0000020BD05302E0>
0 1 2 3
0 1 PENELOPE GUINESS 2006-02-15 04:34:33
1 2 NICK WAHLBERG 2006-02-15 04:34:33
2 3 ED CHASE 2006-02-15 04:34:33
3 4 JENNIFER DAVIS 2006-02-15 04:34:33
4 5 JOHNNY LOLLOBRIGIDA 2006-02-15 04:34:33
.. ... ... ... ...
195 196 BELA WALKEN 2006-02-15 04:34:33
196 197 REESE WEST 2006-02-15 04:34:33
197 198 MARY KEITEL 2006-02-15 04:34:33
198 199 JULIA FAWCETT 2006-02-15 04:34:33
199 200 THORA TEMPLE 2006-02-15 04:34:33

[200 rows x 4 columns]

[ElijahHorowitz]

import mysql.connector
try:
    cnx = mysql.connector.connect(host='localhost', database='purchases', user='root', password='Password123')
    print("connection successful")
    cur = cnx.cursor()
    cur.execute("SELECT * FROM purchases")
    for i in cur:
        print(i)
except:
    print("Connection Failed")

[Edson-Rodrigo]

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',  database='sakila', user='root', password='Edson0919')
    
    query = "SELECT ACTOR_ID, FIRST_NAME, LAST_NAME FROM ACTOR WHERE FIRST_NAME = 'JOE';"
    if connection.is_connected():
        db_info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_info)
        cursor = connection.cursor()
        cursor.execute(query)
        record = cursor.fetchall()
        print(record)
        
except mysql.connector.Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed successfully")

OUTPUT:

Connected to MySQL Server version  8.0.29
[(9, 'JOE', 'SWANK')]
MySQL connection is closed successfully

[rama77605]

import mysql.connector
conn=mysql.connector.connect(host="localhost",user="root",
                               passwd="Password123",database="emp")
print(conn)
c=conn.cursor()
c.execute("SELECT ename from emp")
data = c.fetchall()
print(data)

[OlvarM]

1. Connect to MySQL and read data with python

# import
import mysql.connector as sql
import pandas as pd

def main():
    try:
        connection = sql.connect(host="localhost",
            user="root",
            password="XV#jR$m2!!xBe3y",
            database="sakila")
        print("Successful connection.")
        cursor = connection.cursor()

        # Execute a query
        cursor.execute("SELECT * FROM ACTOR")
        # use fetchall
        table = cursor.fetchall()
        # print(table)
        # Print using dataframe
        db = pd.DataFrame(table)
        print(db)

    except:
        print("Connection failed")
    finally:
        cursor.close()
        connection.close()
        print("Connection closed.")

#run main
main()

Output

Successful connection.
       0         1             2                   3
0      1  PENELOPE       GUINESS 2006-02-15 04:34:33
1      2      NICK      WAHLBERG 2006-02-15 04:34:33
2      3        ED         CHASE 2006-02-15 04:34:33
3      4  JENNIFER         DAVIS 2006-02-15 04:34:33
4      5    JOHNNY  LOLLOBRIGIDA 2006-02-15 04:34:33
..   ...       ...           ...                 ...
195  196      BELA        WALKEN 2006-02-15 04:34:33
196  197     REESE          WEST 2006-02-15 04:34:33
197  198      MARY        KEITEL 2006-02-15 04:34:33
198  199     JULIA       FAWCETT 2006-02-15 04:34:33
199  200     THORA        TEMPLE 2006-02-15 04:34:33

[200 rows x 4 columns]
Connection closed.

[KevinIturbe]

import pandas as pd
import mysql.connector as sql

try:
    db_connection = sql.connect(
        host="localhost",
        user="root",
        password="Nocturno00?",
        database="employees"
    )

    cursor = db_connection.cursor()

    cursor.execute("Select * From dept")

    data = cursor.fetchall()

    myDataFrame = pd.DataFrame(data)

    print(myDataFrame)
except:
    print("No connection")

[AlfredoITS]

CODE

import mysql.connector as sql
import pandas as pd

try:
    db = sql.connect(host='localhost', user='root', password='Switch#97', database='sakila')
    mycursor = db.cursor()
    print("Sucessfully connected, fetching data...")
    mycursor.execute("Select * from film limit 10")
    table = mycursor.fetchall()
    df = pd.DataFrame(table)
    print(df)
except:
    print("Error connecting to database.")
finally:
    db.close()
    print("Successfully terminated.")

OUTPUT

Sucessfully connected, fetching data...
   0   ...                  12
0   1  ... 2006-02-15 05:03:42
1   2  ... 2006-02-15 05:03:42
2   3  ... 2006-02-15 05:03:42
3   4  ... 2006-02-15 05:03:42
4   5  ... 2006-02-15 05:03:42
5   6  ... 2006-02-15 05:03:42
6   7  ... 2006-02-15 05:03:42
7   8  ... 2006-02-15 05:03:42
8   9  ... 2006-02-15 05:03:42
9  10  ... 2006-02-15 05:03:42

[10 rows x 13 columns]
Successfully terminated.

[Gayathrivinodh]

#establish connection

import mysql.connector
from mysql.connector.errors import Error

CT=mysql.connector.connect(host ="localhost:8080",user ="root", password ="0445" )
print(CT)
if CT.is_connected():
    print("It's connected")
    cur = CT.cursor()
    cur.execute("Select * from sakila.actor")
    data = cur.fetchall()
    print(data)
else:
    print(Error.print())