Way to avoid repetition?

[Tuxedo]

I'm playing around right now with sqlite and was wondering if there is some mechanism within Python to avoid repeated use of 'my_cursor' in the below snippet:

import sqlite3
conn = sqlite3.connect('employee.db')
my_cursor = conn.cursor()   
my_cursor.execute("INSERT INTO employees2 VALUES ('Corey','Scagnasty',50000)")
my_cursor.execute("INSERT INTO employees2 VALUES ('Joe','Scagnasty',90000)")
my_cursor.execute("INSERT INTO employees2 VALUES ('Hiep','Arnold',32000)")
my_cursor.execute("SELECT * FROM employees2")
ans = my_cursor.fetchall()
print(ans)

I'm an old VBA guy and there is a wonderful trick where you can do the following:

import sqlite3
conn = sqlite3.connect('employee.db')
my_cursor = conn.cursor()  
with my_cursor 
     .execute("INSERT INTO employees2 VALUES ('Corey','Scagnasty',50000)")
     .execute("INSERT INTO employees2 VALUES ('Joe','Scagnasty',90000)")
     .execute("INSERT INTO employees2 VALUES ('Hiep','Arnold',32000)")
     .execute("SELECT * FROM employees2")
     ans = .fetchall()
end with
print(ans)

I know Python uses the 'with' keyword for other purposes, but I'd love to know if there is a way to streamline similar to the above.

[Serafim]

....

I'm an old VBA guy and there is a wonderful trick where you can do the following:

import sqlite3
conn = sqlite3.connect('employee.db')
my_cursor = conn.cursor()  
with my_cursor 
     .execute("INSERT INTO employees2 VALUES ('Corey','Scagnasty',50000)")
     .execute("INSERT INTO employees2 VALUES ('Joe','Scagnasty',90000)")
     .execute("INSERT INTO employees2 VALUES ('Hiep','Arnold',32000)")
     .execute("SELECT * FROM employees2")
     ans = .fetchall()
end with
print(ans)

I know Python uses the 'with' keyword for other purposes, but I'd love to know if there is a way to streamline similar to the above.

Never seen anything like it but it would be possible to:

import sqlite3
conn = sqlite3.connect('employee.db')
my_cursor = conn.cursor() 
inserts = [('Corey','Scagnasty',50000), ('Joe','Scagnasty',90000), ('Hiep','Arnold',32000)]
my_cursor.executemany("INSERT INTO employees2 VALUES (?, ?, ?)", inserts)
conn.commit()  
my_cursor.execute("SELECT * FROM employees2")
my_cursor.ans = .fetchall()
print(ans)

[deanhystad]

That isn't what "with" does. with sets a context manager for a block of code. When the program exits the code block, either by reaching the end of the block or jumping out of the block due to an exception, the context __exit__ code is executed automatically. This works really nice for things like files where you want to be sure appropriate cleanup is performed. All he cleanup code is placed in the __exit__ method.

I'm sure you could write a context manager for Sqlite3. Using it would look something like this:

with MySqlite3('employee.db') as cursor:
    cursor.execute("INSERT INTO employees2 VALUES ('Corey','Scagnasty',50000)")
    cursor.execute("INSERT INTO employees2 VALUES ('Joe','Scagnasty',90000)")
    cursor.execute("INSERT INTO employees2 VALUES ('Hiep','Arnold',32000)")
    cursor.execute("SELECT * FROM employees2")
    ans = cursor.fetchall()
    print(ans)

If there is any cleanup that has to be performed (I've never used aqlite3) it would happen automatically after the print command.

Notice this does not eliminate the need for an object. When Python encounters a name like "execute" it looks for that name in the current namespace. If the name is not found it raises a NameError exception. with does not change the namespace. using "with cursor" does not tell python to look for names in the "cursor" namespace.

There is a way to get rid of needing to type cursor, but I think it is a bad idea. Remember that namespace thing? You can add names to the current namespace. For example, you can add "execute" to the namespace.

import sqlite3
conn = sqlite3.connect('employee.db')
my_cursor = conn.cursor()
execute = my_cursor.execute  
execute("INSERT INTO employees2 VALUES ('Corey','Scagnasty',50000)")
execute("INSERT INTO employees2 VALUES ('Joe','Scagnasty',90000)")
execute("INSERT INTO employees2 VALUES ('Hiep','Arnold',32000)")
execute("SELECT * FROM employees2")
ans = my_cursor.fetchall()
print(ans)

In the code above, "execute = my_cursor.execute" creates a variable named "execute" and assigns it the method "my_cursor.execute". There is nothing special about the name "execute", it is just a variable in the current namespace. If execute is too long you could name it "x".

import sqlite3
conn = sqlite3.connect('employee.db')
my_cursor = conn.cursor()
x = my_cursor.execute  
x("INSERT INTO employees2 VALUES ('Corey','Scagnasty',50000)")
x("INSERT INTO employees2 VALUES ('Joe','Scagnasty',90000)")
x("INSERT INTO employees2 VALUES ('Hiep','Arnold',32000)")
x("SELECT * FROM employees2")
ans = my_cursor.fetchall()
print(ans)

That is a lot shorter, but it is also harder to read. What the heck is "x"? I have to read the code to find where x is defined and remember that x is the "sqlite3.connect().cursor().execute()". Yuck!

[Tuxedo]

That is a lot shorter, but it is also harder to read. What the heck is "x"? I have to read the code to find where x is defined and remember that x is the "sqlite3.connect().cursor().execute()". Yuck!

Yeah, that's a downside in the VBA world as well, but the construct is widely used because it has significant performance benefits.

[SheeppOSU]

I would do something like this:

import sqlite3
conn = sqlite3.connect('employee.db')
my_cursor = conn.cursor()   

values = [
    ('Corey','Scagnasty',50000),
    ('Joe','Scagnasty',90000),
    ('Hiep','Arnold',32000)
]

for value in values:
    my_cursor.execute(f"INSERT INTO employees2 VALUES {str(value)}")

my_cursor.execute("SELECT * FROM employees2")
ans = my_cursor.fetchall()
print(ans)

Looks much more clean to me

[Tuxedo]

Nice, thanks!!