SQL with python – complete guide
logo
Manas Sinha
Developer | Designer

By Manas | 21 January 2021 | 1 min read

SQL WITH PYTHON - THE COMPLETE GUIDE | FOR UBUNTU USERS

A quick guide to connect MySQL with python.

0.INSTALL MYSQL SERVER

You can install the MySQL server from the website or you can simply do it with the command line. For ubuntu systems use the following commands :
sudo apt-get update
sudo apt-get install mysql-server

1.install mysql-connector

To connect MySQL with python we will need a SQL-connector, To acieve that run the following command :

sudo apt-get install libmysqlclient-dev
pip install mysqlclient
sudo apt-get install libmysqlclient-dev
pip install mysqlclient

2.CONNECT TO THE DATABASE

import MySQLdb
db = MySQLdb.connect(host="localhost", user="username", passwd="your-password", db="testDB")

cur = db.cursor()

cur.execute("select * from some-table")

for row in cur.fetchall():

    print(row[0])

db.close()

3.errors you might encounter

Now if you are trying to connect to root user or some other user might face an error like :
“Access denied for user ‘username’@’localhost’ (using password: YES)”

4.Fix

open mysql and write the command :
SELECT user,authentication_string,plugin,host FROM mysql.user;
if the authentication_string of the user you are trying to use is empty :
ALTER USER ‘username’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘your-password’;
FLUSH PRIVILEGES;
check again
SELECT user,authentication_string,plugin,host FROM mysql.user;
it should be filled now and you should be able to connect

bonus

If before you were using something like sudo mysql to open MySQL-server you might not able to do that instead
mysql -u username -p

and then enter your password

if you are not able to make changes to the databases from some user other than root you will need to grant permissions, check out here

every time you change something in the database use  db.commit()commit the changes

LEAVE A COMMENT

If you like the post leave a comment and share it.

Leave a Reply