In this tutorial, we will learn how to fetch data from a database using user input in Python. We’ll be connecting to a MySQL database using the popular MySQL Connector/Python package, and later execute an SQL query to fetch the desired data.
Step 1: Sample Database and Table Setup
For this tutorial, we will assume a sample database named testDB with a table named employee. The employee table has the following columns: emp_id, first_name, last_name, age, and city.
Here’s a sample table setup that you could use:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE DATABASE testDB; USE testDB; CREATE TABLE employee (emp_id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255), last_name VARCHAR(255), age INT, city VARCHAR(255)); INSERT INTO employee (first_name, last_name, age, city) VALUES ('John', 'Doe', 30, 'New York'), ('Jane', 'Smith', 28, 'Los Angeles'), ('Michael', 'Johnson', 35, 'Chicago'); |
Step 2: Connecting to the Database
Before fetching data, we need to connect to the MySQL database. Here’s how to connect using the MySQL Connector/Python package:
|
1 2 3 4 5 6 7 8 9 10 |
import mysql.connector def connect_to_db(): db_connection = mysql.connector.connect( host="localhost", user="your_user", password="your_password", database="testDB" ) return db_connection |
Step 3: Fetching Data
Now that we have a connection to the database, we can fetch data from the employee table using user input. In this example, we’ll filter the data by the employee’s city.
|
1 2 3 4 5 6 7 8 9 10 |
def fetch_data_from_database(city): db_connection = connect_to_db() cursor = db_connection.cursor() query = f"SELECT * FROM employee WHERE city = '{city}'" cursor.execute(query) results = cursor.fetchall() return results |
The fetch_data_from_database function takes the user input (city) and fetches all the records where the city matches the input. The data is retrieved using the fetchall() method, and the function returns the results.
Step 4: Accept User Input and Display Fetched Data
Finally, we’ll accept user input and display the fetched data using the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
def main(): city = input("Enter a city: ") results = fetch_data_from_database(city) if len(results) == 0: print(f"No records found for the city: {city}") return print("Employee Records:") for emp_id, first_name, last_name, age, city in results: print(f"{first_name} {last_name} (ID: {emp_id}), Age: {age}, City: {city}") if __name__ == "__main__": main() |
When you run the script and enter a city name, it will display the employee records for the inputted city.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
import mysql.connector def connect_to_db(): db_connection = mysql.connector.connect( host="localhost", user="your_user", password="your_password", database="testDB" ) return db_connection def fetch_data_from_database(city): db_connection = connect_to_db() cursor = db_connection.cursor() query = f"SELECT * FROM employee WHERE city = '{city}'" cursor.execute(query) results = cursor.fetchall() return results def main(): city = input("Enter a city: ") results = fetch_data_from_database(city) if len(results) == 0: print(f"No records found for the city: {city}") return print("Employee Records:") for emp_id, first_name, last_name, age, city in results: print(f"{first_name} {last_name} (ID: {emp_id}), Age: {age}, City: {city}") if __name__ == "__main__": main() |
Output:
Enter a city: New York Employee Records: John Doe (ID: 1), Age: 30, City: New York
Conclusion
In this tutorial, we learned how to fetch data from a database using user input in Python with the help of MySQL Connector/Python package. You can easily modify and adapt the code to fetch data using any other database or user input.