Connect to Mysql from python code:

Basic about connection to Mysql by Python:

http://frankfu.click/web-develop/python/chapter-8-2-work-database.html

Additional connection parameters.

To specify the path to a socket file for local host connections on Unix, omit the host parameter and provide a unix_socket parameter:

conn_params = {
"database": "testdb",
"unix_socket": "/var/tmp/mysql.sock",
"user": "cbuser",
"password": "cbpass",
}
conn = mysql.connector.connect(**conn_params)
print("Connected")

To specify the port number for TCP/IP connections, include the host parameter and provide an integer-valued port parameter:

conn_params = {
"database": "testdb",
"host": "127.0.0.1",
"port": 3307,
"user": "cbuser",
"password": "cbpass",
}
conn = mysql.connector.connect(**conn_params)

Handle error

When an error occurs, MySQL provides three values:
• A MySQL-specific error number
• A MySQL-specific descriptive text error message
• A five-character SQLSTATE error code defined according to the ANSI and ODBC standards

If an exception occurs, the errno, msg, and sqlstate members of the exception object contain the error number, error message, and SQLSTATE values, respectively. Note that
access to the Error class is through the driver module name.

 

Write library files

Library files have other benefits besides making it easier to write programs, such as promoting portability.

Code encapsulation can also improve security. If you make a private library file readable only to yourself, only scripts run by you can execute routines in the file.

If you place the code for establishing a connection to the MySQL server in a library file located outside the document tree, those parameters won’t be exposed to clients.

Execute Statement

use the cursor itself as an iterator that returns each row in turn:

cursor = conn.cursor()
cursor.execute("SELECT id, name, cats FROM profile")
for (id, name, cats) in cursor:
    print("id: %s, name: %s, cats: %s" % (id, name, cats))
print("Number of rows returned: %d" % cursor.rowcount)
cursor.close()

The fetchall() method returns the entire result set as a sequence of row sequences. Iterate through the sequence to access the rows:

cursor = conn.cursor()
cursor.execute("SELECT id, name, cats FROM profile")
rows = cursor.fetchall()
for row in rows:
    print("id: %s, name: %s, cats: %s" % (row[0], row[1], row[2]))

 

Special characters and NULL

 

To add a name such as De’mont, you can not input it straight away. The problem is the single quote inside a single-quoted string. To make the statement legal by escaping the quote, precede it with either a single quote or a backslash, or use double quotation around:

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12','blue','eggroll',4);

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De\'Mont','1973-01-12','blue','eggroll',4);

INSERT INTO profile (name,birth,color,foods,cats)
VALUES("De'Mont",'1973-01-12','blue','eggroll',4);

NULL:

 The SQL NULL value is not a special character, but it too requires special treatment. In SQL, NULL indicates “no value.”

You have two means deal with special characters or NULL:

• Use placeholders in the statement string to refer to data values symbolically, then bind the data values to the placeholders when you execute the statement.
• Use a quoting function (if your API provides one) for converting data values to a safe form that is suitable for use in statement strings.