1

I have an SQLite database with a column whose entries are a string of the following form "A-B-C" where A, B, C have variable length and are delimited by the "-" character.

In particular, A should be 1 or 2 characters, B 1-4 characters and C 1-5 characters but they may be even longer for some unknown reason.

I tried to split these characters the following way:

  • A column where I would have the A substring
  • Another one where I'd have the B substring
  • Last one where I'd have the C substring.

However, since SQLite doesn't support string splitting and my efforts at hacking with substr() and different positioning ended up with a lot of confusion.

I'm trying to do this in python now. However I'm quite new to this, and while I was able to connect to the database and commit statements this way, I'm not quite sure how to go about doing what I want. I saw the str.rsplit() function but I'm not exactly sure how to mix between both languages.

For instance, I should probably write 

c = conn.execute(
       UPDATE database SET Column_1 = 
                 def First(
                    str.rsplit(Column), 1) 
                        SELECT Column FROM database )

with the First function returning the first value of the split string from Column, and so on for Column_2 and Column_3.

Once I have that I need to format each of column_1, column_2 and column_3 so that they each have at least 2, 4 and 5 characters each by adjoining zeroes to the left of the characters in the columns, depending on the length of the resulting string.

EDIT:

I'm having quite a few problems updating the database:

The following yields the first value: 

with con: cur = con.cursor() cur.execute('SELECT Column1 FROM MainTable') row = cur.fetchone() for line in row: a, b, c= line.split('-') print (b);

A slight modification using:

while True: cur.fetchone()== None then Break

yields all values. However, I can get it to update the database for a given column say Col2, because it has a ton of syntax errors related to mixing python and SQL.

José Urriola
  • 11
  • 4
  • Welcome to Stack Overflow! Please [take the tour](http://stackoverflow.com/tour), have a look around, and read through the [help center](http://stackoverflow.com/help), in particular [How do I ask a good question](http://stackoverflow.com/help/how-to-ask)? and [What topics can I ask about here](http://stackoverflow.com/help/on-topic)?. – Stephen Rauch Jan 26 '17 at 19:49

1 Answers1

1

You can use the split() method to split a string:

data = [
    'ab-de-ghijk',
    'a-bcde-fghi',
]
for string in data:
    a, b, c = string.split('-')
    print(a, b, c)
Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
  • How do I get the data? Data = fetchall(column_name) ? – José Urriola Jan 30 '17 at 21:21
  • In my example I showed how to split the string in python. It makes no assumptions about where the data came from or where/how you might want to store it. So take the string out of the DB however you would like, and then split it using the example code. – Stephen Rauch Jan 30 '17 at 21:23
  • Can you please look at my question about how to update a given column? Thanks a lot!! – José Urriola Feb 06 '17 at 15:01
  • 1
    @JoséUrriola, I found your question unclear in that regard. I suggest formulating an [Minimal, Complete, and Verifiable](http://stackoverflow.com/help/mcve) example and posting a new targeted question showing exactly where your code is going wrong. – Stephen Rauch Feb 06 '17 at 15:08
  • Thanks for the quick reply, I have posted the following new question: http://stackoverflow.com/questions/42074966/how-to-update-a-column-in-sqlite-using-pysqlite-where-i-first-needed-to-obtain-a – José Urriola Feb 06 '17 at 18:44