Interacting with Python

MySQL

We will use mysql connector with python

pip install mysql-connector-python

Connect to DB

import mysql.connector 
cnx=mysql.connector.connect(user='root', password='1234', host='127.0.0.1', database ='dbms')

You may need to create a non-root account to access the DBMS (you can allow root to remotely access DB but this is not a great idea).

To create a user

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Make sure to use a database in your local dbms.

To run a query

query='SELECT * FROM company;'
cursor = cnx.cursor()
cursor.execute(query)

Print the results:

for (o) in cursor:
  print(o)

Closing the connection

cnx.close()

Insert data into database

iquery="INSERT INTO  company VALUES('BMW',10,'Germany');"
cursor = cnx.cursor()
cursor.execute(iquery)

If you execute the following, you will see the newly inserted tuple.

query='SELECT * FROM company;'
cursor = cnx.cursor()
cursor.execute(query)
for (o) in cursor:
  print(o)

if you reconnect to the database You will see the updates are not committed.

To make sure the updates is commited

iquery='INSERT INTO  company VALUES('BMW',10,'Germany');COMMIT;'
cursor = cnx.cursor()
cursor.execute(iquery)

Because we have committed the changes, it would persist.

PostgreSQL

We will use psycopg2, the most popular PostgreSQL adapter for Python.

pip install psycopg2-binary

Connect to a PostgreSQL database

import psycopg2

cnx = psycopg2.connect(
    host="127.0.0.1",
    port=5432,
    dbname="dbms",
    user="sammy",
    password="password"
)

You may need to create a dedicated PostgreSQL role. Run the following in psql as a superuser:

CREATE USER sammy WITH PASSWORD 'password';
CREATE DATABASE dbms OWNER sammy;
GRANT ALL PRIVILEGES ON DATABASE dbms TO sammy;

To run a query

cursor = cnx.cursor()
cursor.execute("SELECT * FROM company;")

Print the results:

for row in cursor:
    print(row)

Insert data into the database

cursor = cnx.cursor()
cursor.execute("INSERT INTO company VALUES ('BMW', 10, 'Germany');")

psycopg2 opens a transaction automatically. Changes are not visible to other sessions until you commit.

cnx.commit()

Use a parameterised query to safely pass values and avoid SQL injection:

cursor = cnx.cursor()
cursor.execute(
    "INSERT INTO company VALUES (%s, %s, %s);",
    ("BMW", 10, "Germany")
)
cnx.commit()

Closing the connection

cursor.close()
cnx.close()

A complete example putting it all together:

import psycopg2

cnx = psycopg2.connect(
    host="127.0.0.1",
    port=5432,
    dbname="dbms",
    user="sammy",
    password="password"
)

cursor = cnx.cursor()

# Insert a row
cursor.execute(
    "INSERT INTO company VALUES (%s, %s, %s);",
    ("BMW", 10, "Germany")
)
cnx.commit()

# Query and print all rows
cursor.execute("SELECT * FROM company;")
for row in cursor:
    print(row)

cursor.close()
cnx.close()

🏠 Portal | 🚀 Getting Started | 📝 SQL Cheat Sheet