Python: connecting to MySQL to connect
Before you query with Python data from MySQL, you must establish a connection. But don't forget you, before the required MySQL Module to install.
- Insert at the beginning of your Python code with the command "import mysql.connector" and the command "import mysql" (without the quotation marks) so that the required MySQL-modules can be used.
- A connection mysql with the command "connection =.connector.connect(host = "127.0.0.1", user = "bloggs", passwd = "hallo1234", db = "my database")" to restore. Do not forget the command to properly fit.
- At the end of the code, the connection with "connection can.close()" to be closed.
Connection with MySQL
MySQL table using Python
First, you should create a first table in MySQL with Python.
- For this you need to first run the command "cursor = connection.cursor()" in your Code to insert to get the Cursor of the connection.
- A table, you can then, for example, with the command "cursor.execute("CREATE TABLE test (id int(3), text varchar(255))")" create. The table is, in this case, the name "test".
- Finally, do not forget the Cursor with the command "cursor.to close close()" again.
MySQL table using Python
MySQL: records in the table
Now that you have a connection and a table, you can add new records.
- First, insert the command "cursor = connection.cursor()" to your Code to.
- With the command "cursor.execute("INSERT INTO test (id,text) VALUES (%s,%s)",("1","Hello world",))" you can now enter, for example, a first set of data. The wildcard "(%s,%s)" is used, so that a protection against SQL Injection is. The data are internally masked accordingly.
- Do not forget also your Cursor with the command "cursor.to close close()" again and your Changes with the command "connection.to take over commit ()".
MySQL: records, enter
MySQL: data records are read
Finally, we would like to show you how you can read from your table data again.
- Add the command "cursor = connection.cursor()" (without the quotes) to your Code to.
- Then you can use the command "cursor.execute("SELECT * from test")" in your select table.
- Now you can use the command "result = cursor.fetchall ()", all data can be read out and, in the case of the variable "result" to Deposit.
- Also, don't forget to close your Cursor: "cursors.close()"
- So you can output your data, you must bow first to a for to add to your Code: "for data in result:"
- Below that, you need to key, finally, only (using the tab to the right) the command "print "number:" + str(data[0]) + "; Text: "+ data[1]" insert. The data are then output in the Python Terminal.
MySQL: data records are read
More tips to Python in our CHIP counselors
On our topic page for the Python programming language, you will find additional Tutorials for programming. There we will show you, for example, how easy a GUI program in Python can.
