how to write sql in python

To write SQL queries in Python, you need to use a database module that provides a Python DBAPI-2.0-compliant interface to the database you want to interact with. Two widely-used modules include sqlite3 for SQLite databases and mysql-connector for MySQL databases.

Connecting to a Database

To connect to a database using sqlite3, first import the module and then use the connect() function to create a connection object. Supply the path to the SQLite database as an argument to connect():

main.py
import sqlite3

conn = sqlite3.connect('example.db')
53 chars
4 lines

To connect to a MySQL database using mysql-connector, you need to first install the module:

main.py
pip install mysql-connector-python
35 chars
2 lines

Then import the module and use it to create a connection object. Supply the host, user, and password information as arguments to connect():

main.py
import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="example"
)
157 chars
9 lines

Executing SQL Queries

Once you've established a connection to your database, you can execute SQL queries using Python. To execute a query, use the execute() method of your connection object and supply your SQL query as a string argument.

For example, to create a table named "students" with columns for "name", "age", and "grade" in SQLite:

main.py
conn.execute('''CREATE TABLE students
             (name TEXT, age INTEGER, grade INTEGER)''')
95 chars
3 lines

And in MySQL:

main.py
conn.execute('''CREATE TABLE students
             (name VARCHAR(255), age INT, grade INT)''')
95 chars
3 lines

To insert data into the table, you can use the execute() method along with the SQL INSERT INTO statement:

main.py
# SQLite syntax
conn.execute("INSERT INTO students (name, age, grade) VALUES (?, ?, ?)",
            ("Alice", 18, 90))

# MySQL syntax
conn.execute("INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)",
            ("Alice", 18, 90))
243 chars
8 lines

To retrieve data from a table, you can use the execute() method along with the SQL SELECT statement:

main.py
# SQLite syntax
cursor = conn.execute("SELECT name, age, grade FROM students")
rows = cursor.fetchall()

# MySQL syntax
cursor = conn.execute("SELECT name, age, grade FROM students")
rows = cursor.fetchall()
208 chars
8 lines

These examples demonstrate the basics of writing SQL queries in Python. Depending on your needs and your database, however, there are many more SQL operations and Python functions available to help you manipulate your data.