setting mysql with python.

Posted by Adarshreddy Adelli

Tuesday, December 26, 2017

  In our previous post we have explained installation of mysql in centos,windows,Ubuntu.
   🔽if you haven't installed mysql then visit our previous post click here

👉Now this time we go up with mysql setup and gathering information through python.

⏩For setup type the following command.

  mysql -u USERNAME -p

⏩Then mysql will ask your password then use this command.

mysql> CREATE DATABASE pythonspot;
mysql> USE pythonspot;

⏩Now we create a table

CREATE TABLE IF NOT EXISTS examples (
  id int(11) NOT NULL AUTO_INCREMENT,
  description varchar(45),
  PRIMARY KEY (id)
);

⏩  Now its time to insert data into the new tables .

INSERT INTO examples(description) VALUES ("Hello World");
INSERT INTO examples(description) VALUES ("MySQL Example");
INSERT INTO examples(description) VALUES ("Flask Example");

⏩  Extracting data using SQL query

mysql> SELECT * FROM examples;
+----+---------------+
| id | description   |
+----+---------------+
|  1 | Hello World   |
|  2 | MySQL Example |
|  3 | Flask Example |
+----+---------------+
3 rows in set (0.01 sec)

⏩Now its time to get the data from python.
➡MYSQLDB module provides you full access to the database.

#!/usr/bin/python
import MySQLdb
 
db = MySQLdb.connect(host="localhost",  # your host 
                     user="root",       # username
                     passwd="root",     # password
                     db="pythonspot")   # name of the database
 
# Create a Cursor object to execute queries.
cur = db.cursor()
 
# Select data from table using SQL query.
cur.execute("SELECT * FROM examples")
 
# print the first and second columns      
for row in cur.fetchall() :
    print row[0], " ", row[1]

🔀sources
    ➡python mysqldb site
    ➡mysql site

Adarshreddy Adelli Tuesday, December 26, 2017 databases mysql python