"AttributeError: exit" when importing data in SQL database

Question

I am currently following alongside a book (cpp for quantitative finance) and I am trying to import the symbols for S&P500 from wiki into a sql database I've created. However, I am getting the AttributeError: exit with regards to my "with con" statement (see below). I have read posts from similar errors but I cannot seem to fix mine. I am extremely new to python so perhaps there is some fundamental misunderstanding on my part. I have included the relevant code below, any advice would be hugely appreciated.

    """
    Insert the S&P500 symbols into the MySQL database.
    """
    # Connect to the MySQL instance
    db_host = 'localhost'
    db_user = 'sec_user'
    db_pass = 'database_password'
    db_name = 'database_name'

    con = mdb.connect(
            host=db_host, user=db_user, passwd=db_pass, db=db_name
    )

    # Create the insert strings
    column_str = """ticker, instrument, name, sector,
    currency, created_date, last_updated_date
    """
    insert_str = ("%s, " * 7)[:-2]
    final_str = "INSERT INTO symbol (%s) VALUES (%s)" % \
    (column_str, insert_str)
    # Using the MySQL connection, carry out
    # an INSERT INTO for every symbol
    with con:
        cur = con.cursor()
        cur.executemany(final_str, symbols)       

if __name__ == "__main__":
    symbols = obtain_parse_wiki_snp500()
    insert_snp500_symbols(symbols)
    print("%s symbols were successfully added." % len(symbols))

What is con - what library is that coming from? Does that object support being a context manager? — rdas
– rdas, Commented Apr 18, 2020 at 14:31
I don't think it is coming from any library, it is defined above as con = mdb.connect( host=db_host, user=db_user, passwd=db_pass, db=db_name ) — Horro
– Horro, Commented Apr 18, 2020 at 16:27
I meant what library you're using to connect to mysql. What is the type() of con — rdas
– rdas, Commented Apr 18, 2020 at 16:28

snakecharmerb · Accepted Answer · 2020-09-21 18:24:37Z

1

The error is telling you that the object returned by mdb.connect is not a context manager, that is it cannot be used in a with statement. You'll need to close the connection manually once you've finished with it (con.close()) or use a package that provides a connection that is a context manager.

A quick study of commonly used connectors suggests you want to use pymysql

>>> import MySQLdb
>>> import mysql.connector
>>> import pymysql
>>> params = {'host': 'localhost', 'user': 'root', 'password': '', 'database': 'test'}
>>> for pkg in (MySQLdb, mysql.connector, pymysql):
...     conn = pkg.connect(**params)
...     try:
...         with conn:
...             pass
...     except AttributeError as ex:
...         print(pkg.__name__, 'failed with', ex)
... 
MySQLdb failed with __enter__
mysql.connector failed with __enter__

If you have to use a connection that is not a context manager, you can emulate it in a try/except/finally suite:

import MySQLdb

conn = MySQLdb.connect(host='localhost', user='root', password='', database='test')

try:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM my_table;')
    for row in cursor.fetchall():
        print(row)
    cursor.close()
    conn.commit()
except:
    # log the error here
    conn.rollback()
finally:
    conn.close()

Or you can make your own context manager using the tools provided in contextlib:

import contextlib
import MySQLdb

@contextlib.contextmanager
def managed_connection(conn):
    try:
        yield
        conn.commit()
    except:
        # log the error here
        conn.rollback()
    finally:
        conn.close()


conn = MySQLdb.connect(host='localhost', user='root', password='', database='test')
with managed_connection(conn) as mc:
    cursor = mc.cursor()
    cursor.execute('SELECT * FROM my_table;')
    for row in cursor.fetchall():
        print(row)
    cursor.close()

(You can make a cursor context manager too, or have the context manager yield a cursor rather than the connection).

edited Sep 21, 2020 at 18:24

answered Apr 19, 2020 at 7:57

snakecharmerb

57.2k13 gold badges137 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Horro Over a year ago

Thank you for the help. Strangely I am getting a syntax error on the "except" statement which seems unusual.

snakecharmerb Over a year ago

The code is only to demonstrate that only pymysql's connection is a context manager - it isn't something you would actually use. I've added some examples to show how you might emulate pymysql's context manager if you are going to use MySQLdb

Collectives™ on Stack Overflow

"AttributeError: exit" when importing data in SQL database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related