CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Sani Evans
Sani Evans

Reputation: 81

Insert 2d Array into Table without importing sqlalchemy Table Column etc objects?

I'm writing an app in Python and part of it includes an api that needs to interact with a MySQL database. Coming from sqlite3 to sqlalchemy, there are parts of the workflow that seem a bit too verbose for my taste and wasn't sure if there was a way to simplify the process.

Sqlite3 Workflow

If I wanted to take a list from Python and insert it into a table I would use the approach below

# connect to db 
con = sqlite3.connect(":memory:")
cur = con.cursor()

# create table
cur.execute("create table lang (name, first_appeared)")

# prep data to be inserted 
lang_list = [
    ("Fortran", 1957),
    ("Python", 1991),
    ("Go", 2009)
]

# add data to table
cur.executemany("insert into lang values (?, ?)", lang_list)
con.commit()

SqlAlchemy Workflow

In sqlalchemy, I would have to import the Table, Column, String, Integer Metadata etc objects and do something like this

# connect to db
engine = create_engine("mysql+pymysql://....")

# (re)create table, seems like this needs to be
# done every time I want to insert anything into it?
metadata = MetaData()
metadata.reflect(engine, only=['lang'])
table = Table('lang', meta, 
    Column('name', String),
    Column('first_appeared', Integer),
    autoload=True, autoload_with=engine)

# prep data to be inserted 
lang_list = [
    {'first_appeared': 1957, 'name': 'Fortran'},
    {'first_appeared': 1991, 'name': 'Python'},
    {'first_appeared': 2009, 'name': 'Go'}
]

# add data to table
engine.execute(table.insert(), lang_list)

Question

Is there a way to add data to a table in sqlalchemy without having to use Metadata, Table and Column objects? Specifically just using the connection, a statement and the list so all that needs to be run is execute?

I want to do as little sql work in Python as possible and this seems too verbose for my taste.

Possible different route

I could use a list comprehension to transform the list into one long INSERT statement so the final query looks like this

statement = """
INSERT INTO lang 
VALUES ("Fortran", 1957),
       ("Python", 1991),
       ("Go", 2009);"""

con.execute(statement)

- but wasn't sure if sqlalchemy had a simple equivalent to sqlite3's executemany for inserts and a list without having to incorporate all these objects every time in order to do so.

If a list comprehension -> big statement -> execute was the simplest way to go in that regard then that's fine, I am just new to sqlalchemy and had been using sqlite3 up until this point.

For clarification, in my actual code the connection is already using the appropriate database and the tables themselves exist - the code snippets used above have nothing to do with the actual data/tables I'm working with and are just for reproducibility/testing sake. It's the workflow for adding to them that felt verbose when I had to reconstruct the tables with imported objects just to add to them.

Upvotes: 0

Views: 708

Answers (1)

Ian Wilson
Ian Wilson

Reputation: 9059

I didn't know SQLite allowed weakly typed columns as your demonstrated in your example. As far as I know most other databases, mysql and postgresql, will require strongly typed columns. Usually the table metadata is either reflected or pre-defined and used. Sort of like type definitions in a statically typed language. SQLAlchemy will use these types to determine how to properly format the SQL statements. Ie. wrapping strings with quotes and NOT wrapping integers with quotes.

In your mysql example you should be able to use the table straight off the metadata with metadata.tables["lang"], they call this reflecting-all-tables-at-once in the docs. This assumes the table is already defined in the mysql database. You only need to define the table columns if you need to override the reflected table's definition, as they do in the overriding-reflected-columns docs.

The docs state that this should utilize executemany and should work if you reflected the table from a database that already had it defined:

engine = create_engine("mysql+pymysql://....")


metadata = MetaData()

# Pull in table definitions from database, only lang table.
metadata.reflect(engine, only=['lang'])


# prep data to be inserted 
lang_list = [
    {'first_appeared': 1957, 'name': 'Fortran'},
    {'first_appeared': 1991, 'name': 'Python'},
    {'first_appeared': 2009, 'name': 'Go'}
]

# add data to table
engine.execute(metadata.tables["lang"].insert(), lang_list)

Upvotes: 1

Related Questions