Embedded Files
SDD Topic has been refreshed!
What to Install
What to Install
There are various libraries that will connect to a mySQL database.
The one we are using is the MySQL Connector
Which can be found here: https://dev.mysql.com/downloads/connector/python/
How to connect to a Database using Python
How to connect to a Database using Python
The Python code below will connect to a database called AH_Injection
We have had to specify the host, database name and a username and password to connect
If the connection is successful all code after the else: command will be executed
Running a SQL SELECT Query with Python
Running a SQL SELECT Query with Python
The code below creates a new instance of the cursor class.
This class instantiates objects that can execute operations such as SQL statements. Cursor objects interact with the MySQL server using a MySQLConnection object. We then call the execute method to run the SQL statement.
Another way of displaying data
Another way of displaying data
Just like before we have ran a SELECT statement but this time we are binding parameters to the values that are to be inserted. There is also another example with more than one parameter.
Displaying the number of records
Displaying the number of records
The cursor has a rowcount property that can be used to display the number of rows returned ( or modified by a INSERT/UPDATE or DELETE statement)
The row count isn’t be known before the rows have been fetched.
In this case, the number of rows is -1 immediately after query execution and is incremented as rows are fetched.
The fetchall method below fetches all (or all remaining) rows of a query result set and returns a list of tuples.
This example only has one parameter
This example has multiple parameters
Displaying Fields - Concatenation
Displaying Fields - Concatenation
We can use the fetchall() method to return the results whilst displaying the number of results beforehand)
This would allow you to display a suitable message if none were found
Similar to the code we used to display a row of a 2D array we can build a string which contains all of the data before it is displayed.
This code would display the entire record as one line
What if you want to display the field names?
What if you want to display the field names?
If you want to display the field names we can use the column_names property to return these.
We have also used an index to reference the element(field) of each row that has been returned.
Modifying Data with the SQL Connector
Modifying Data with the SQL Connector
By default Connector/Python does not autocommit (make) any modifications to a table/record. It is important to call the connection.commit() method after every transaction that modifies data in tables. This will mean any update/insert statements.
The commit method finalises the modification of the data. In the example above it would INSERT the data into the table.
Other Methods/Properties of the Cursor class
Other Methods/Properties of the Cursor class
Page updated
Report abuse