Python Connector

[Kevin Rodriguez]

Hello All,

I am having an issue with the Python connector that I was hoping someone might be able to shed some light on. First off I am running Kubuntu 14.04 LTS with Python 3.4.0 and MySQL 5.5.43. I have manually gone into MySQL to make my tables and columns:

mysql> show tables
-> ;
+---------------------+
| Tables_in_Firewalls |
+---------------------+
| Host |
| Host_History |
+---------------------+
2 rows in set (0.00 sec)

mysql> Describe Host;
+-----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+----------------+
| Firewall_ID | int(11) | NO | MUL | NULL | auto_increment |
| Session | varchar(30) | NO | | NULL | |
| Hostname | varchar(25) | NO | | NULL | |
| Address | varchar(15) | NO | | NULL | |
| Status | varchar(10) | NO | | NULL | |
| SmartNet | enum('Y','N') | YES | | NULL | |
| Contract_Level | varchar(15) | YES | | NULL | |
| Contract_Expire | date | YES | | NULL | |
| Site_Name | varchar(30) | NO | | NULL | |
| Installed | date | YES | | NULL | |
+-----------------+---------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> Describe Host_History;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| Firewall_ID | smallint(6) | NO | | NULL | |
| Implemented | date | NO | | NULL | |
| Make | varchar(10) | NO | | NULL | |
| Model | varchar(10) | NO | | NULL | |
| Serial_Number | varchar(20) | NO | | NULL | |
| Version | varchar(10) | NO | | NULL | |
| Memory | smallint(6) | NO | | NULL | |
+---------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

My script connects to the database just fine with

FirewallDB = mysql.connector.connect(user = 'UserID', password = 'password', host = '127.0.0.1', database = 'Firewalls')
Cursor = FirewallDB.cursor()

Per the manual I then create a string for a query:

Query = ("SELECT Session FROM Host AS H WHERE H.Session = FileTemplate")
Cursor.execute (Query)

I originally had the Query string quoted with two individual lines similar to the example:

query = ("SELECT first_name, last_name, hire_date FROM employees "
"WHERE hire_date BETWEEN %s AND %s")

But I found that Python did not like this even if a + was inserted between the two quoted strings. After altering the Query string a few times based on various error messages, the latest errors I am seeing are:

* Unknown column 'FileTemplate' in 'where clause'

I replaced 'FileTemplate' with a %s and changed my Cursor.execute(Query, (FileTemplate)) so now I am getting:

* mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1

I added a Cursor.commit() after the execute, but this didn't seem to matter. It appears that my variable 'FileTemplate' is going to be seen by MySQL as a column unless I use a %s and substitute. I am unsure of why I am getting a syntax error.