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)
Becuase we have commited the changes, it would presist.