Querying in Python

Question

I'm building a database on python 2.7 using sqlite3 and I have reached a small problem.

Basically I have two tables which each store the following information:

Tweet:

user_name, 
tweet_time, 
tweet_text

User:

user_name, 
user_description, 
user_followers, 
user_verified, 
user_location

I am trying to add a function which prompts the user to enter a string, and every row in both tables which contains the submitted string, which will be found in the tweet_text column, is deleted.

The problem I'm having is that the 'User' Table does not contain a column tweet_text so I'm not sure how to delete corresponding rows in this table without actually having to add a tweet_text column.

What I have so far is just a simple delete query on the 'Tweet' Table.

delete_string = raw_input("Which string would you like to be removed from your database?: ")
c.execute("DELETE FROM Tweet WHERE tweet_text LIKE ?", ('%'+delete_string+'%',))

I'm wondering if there is any way to delete the corresponding rows in the 'User' Table without having to explicitly include tweet_text as a column.

I'm relatively inexperienced on python and this is my first time using query writing so any help will be greatly appreciated!

Thanks in advance.

Answer 1

According to your question, you may delete by querying the username using the tweet_text column but I do not believe you wish to delete the username if you have 2 tweets from the same user and you delete one.

I would probably suggest this:

# Fetch all users with a tweet_text that is similar to the delete_string
res = c.execute("SELECT user_name FROM Tweet WHERE tweet_text LIKE ?", ('%'+delete_string+'%',)).fetchall()

# If any user is found
if res:
    # Get his username
    user_name = res[0][0]
    # Delete the tweet
    c.execute("DELETE FROM Tweet WHERE tweet_text LIKE ?", ('%'+delete_string+'%',))
    # If he has no more tweets in the database
    if c.execute("SELECT 1 FROM Tweet WHERE user_name = ?", (user_name)).fetchone() is None:
        # Delete the user.
        c.execute("DELETE FROM User WHERE user_name = ?", (user_name))

1. First you fetch the user_name if the tweet exists
2. Then you delete the tweet
3. Then you check if there are more tweets from the same user.
4. If there are none you delete him from the database.

If you wish, you may support multiple users like so:

# Get all users with similar tweets
res = c.execute("SELECT user_name FROM Tweet WHERE tweet_text LIKE ?", ('%'+delete_string+'%',)).fetchall()

# If there are any
if res:
    # Get their user names (make it a set to avoid repeated names)
    user_names = set(u[0] for u in res)
    # Delete all the similar tweets
    c.execute("DELETE FROM Tweet WHERE tweet_text LIKE ?", ('%'+delete_string+'%',))
    for user in user_names:
        # Check for each user if he has more tweets in the database
        if c.execute("SELECT 1 FROM Tweet WHERE user_name = ?", (user,)).fetchone() is None:
            # If he has none, delete him.
            c.execute("DELETE FROM User WHERE user_name = ?", (user_name))

UPDATE:

If you have the same amount of rows, you must have an identifier(rowid) which will tie the tweet and the user. Define the tweet database as so: (The important part is the identifier)

identifier INTEGER PRIMARY KEY AUTOINCREMENT,
user_name TEXT, 
tweet_time DATETIME,
tweet_text TEXT

And the User database as so:

identifier PRIMARY KEY,
user_name TEXT, 
user_description TEXT, 
...

Upon inserting rows to the tweet database, use cursor.lastrowid in order to fetch the generated identifier and insert the generated identifier together with the user to the Users database. You should probably do it in the same transaction. Upon deleting, delete from both databases where the identifier is the same.

See this stackoverflow question and the sqlite documentation regarding rowid or identifier columns.

Here's an example:

# When adding
with connection:
    cursor = connection.execute("INSERT INTO Tweet (user_name, tweet_time, tweet_text) VALUES (?, ?, ?)", (user, time, text))
    rowid = cursor.lastrowid
    cursor.execute("INSERT INTO User (identifier, user_name, user_description, ...) VALUES (?, ?, ?, ...", (rowid, user, desc, ...))

# When deleting
tweet_ids = c.execute("SELECT identifier FROM Tweet WHERE tweet_text LIKE ?", ('%'+delete_string+'%',)).fetchall()
if tweet_ids:
    cursor.executemany("DELETE FROM Tweet WHERE identifier = ?", tweet_ids)
    cursor.executemany("DELETE FROM User WHERE identifier = ?", tweet_ids)

# Or one line when deleting (I'm not sure if it works)
c.execute("DELETE tweet, user FROM Tweet tweet JOIN User user "
          "ON tweet.identifier = user.identifier "
          "WHERE tweet.tweet_text LIKE ?", ('%'+delete_string+'%',))

Answer 2

I just wrote this from my mind but try this query:

DELETE tweet, user 
FROM Tweet tweet JOIN User user ON tweet.user_name = user.user_name
WHERE tweet.tweet_text LIKE ?;

Although what you're looking for is this, ON DELETE CASCADE. That will automatically delete it from foreign key table, which will be your User table.

Make sure to enable foreign key support in SQLite

PRAGMA foreign_keys = ON;