AttributeError:__exit__ on python 3.4

Question

Original Code:

import sys
import os
import latexmake

import mysql.connector
conn = mysql.connector.connect(user='root',password='oilwell',host='localhost',database='sqlpush1')

with conn:
    mycursor = conn.cursor()
mycursor=execute("SELECT DATE,oil,gas,oilprice,gasprice,totrev FROM results WHERE DATE BETWEEN '2011-01-01' AND '2027-12-01'")
rows = mycursor.fetchall()
a.write("\\documentclass{standalone}\\usepackage{booktabs}\n\n\\usepackage{siunitx}\r \n\
\r\n\\begin{document}\r\n\\begin{tabular}{ccS[table-format = 5.2]} \\\\ \\toprule\r")
a.write("Date & Oil & Gas & Oil price & Gas price & Total Revenue \\\\ \\midrule \r")
for row in rows:
    a = open("testtest.tex", "w")
    a.write("" + str(row[0]) + " & " + str(row[1]) + " & " + str(row[2]) + " & " + str(row[3]) + " & " + str(row[4]) + " & " + str(row[5]) + " \\\\ \r")

    a.write("\\bottomrule \\end{tabular}\r\\end{document}")
    a.close
print (os.path.getsize("testtest.tex"))
os.system('latexmk.py -q testtest.tex')
mycursor.close()
conn.close()
a.close()

After run by IDLE, and red error pop up like

Traceback (most recent call last):
  File "C:\Users\Cheng XXXX\Desktop\tabletest.py", line 8, in <module>
    with conn:
AttributeError: __exit__

I checked the file and cannot file mistake, need help.

Answer 1

You are trying to use the connection as a context manager:

with conn:

This object doesn't implement the necessary methods to be used like that; it is not a context manager, as it is missing (at least) the __exit__ method.

If you are reading a tutorial or documentation that uses a different MySQL library, be aware that this feature may be supported by some libraries, just not this one. The MySQLdb project does support it, for example.

For your specific case, you don't even need to use the with conn: line at all; you are not making any changes to the database, no commit is required anywhere. You can safely remove the with conn: line (unindent everything under it one step). Otherwise you can replace the context manager with a manual conn.commit() elsewhere.

Alternatively, you can create your own context manager for this use-case, using the @contextlib.contextmanager() decorator:

from contextlib import contextmanager

@contextmanager
def manage_transaction(conn, *args, **kw):
    exc = False
    try:
        try:
            conn.start_transaction(*args, **kw)
            yield conn.cursor()
        except:
            exc = True
            conn.rollback()
    finally:
        if not exc:
            conn.commit()

and use this as:

with manage_transaction(conn) as cursor:
    # do things, including creating extra cursors

where you can pass in extra arguments for the connection.start_transaction() call.