Search in DBF and update record

[Vinicius Henrique]

Hello, Guys!


I'm starting with python, who recommended this forum was Ethan, he told me he would have people willing to help anyone willing to learn.

I need to replace the value '3' of the line 12 by the variable 'codigo'


The result is: dbf.ver_2.NotFoundError: 'Record 0 is not in list.'

But the value of a variable exists in the MEST2.DBF... I could make it work ... (procod), which has the number 3 he expects an integer value, not sure if he is pulling an integer value.


python python.py 3 000001 50

Code:

1 #!/usr/bin/python
2 import dbf
3 import sys
4
5 codigo = sys.argv[1]
6 procod = sys.argv[2]
7 prodest = sys.argv[3]
8
9 db = dbf.Table('MEST2.DBF')
10 with db:
11 procod_idx = db.create_index(lambda rec: (rec.codigo, rec.procod))
12 match = procod_idx.search(match=(3, procod))
13 record = match[0]
14 with record:
15 record.proest = prodest
16 record.dt_atualiz = '14/07/15 16:52'
17
18 print codigo
19 print procod
20 print prodest



PS: My english is bad

[Ethan Furman]

On 07/14/2015 04:31 PM, Vinicius Henrique wrote:

> python python.py 3 000001 50

> Code:
>
> 1 #!/usr/bin/python
> 2 import dbf
> 3 import sys
> 4
> 5 codigo = sys.argv[1]
> 6 procod = sys.argv[2]
> 7 prodest = sys.argv[3]
> 8
> 9 db = dbf.Table('MEST2.DBF')
> 10 with db:
> 11 procod_idx = db.create_index(lambda rec: (rec.codigo, rec.procod))
> 12 match = procod_idx.search(match=(3, procod))
> 13 record = match[0]
> 14 with record:
> 15 record.proest = prodest
> 16 record.dt_atualiz = '14/07/15 16:52'
> 17
> 18 print codigo
> 19 print procod
> 20 print prodest

12 match = procod_idx.search(match=(codigo, procod))

16 record.dt_atualiz = dbf.DateTime(2015, 7, 15, 16, 52)

Those two changes should hopefully get you going.

For learning Python you should check out the Python Tutor list [1], and for general python questions the Python List is great.

--
~Ethan~


[1] https://mail.python.org/mailman/listinfo/tutor
[2] https://mail.python.org/mailman/listinfo/python-list%C2%A0

[Ian Noble]

Hi guys,

I'm a bit of a newbie to both Python and especially this dbf stuff!  I'm was definitely drawn to this thread initially because "Search in DBF and update record" is exactly what I want to do with the code I've been trying to get working for about a month now!  Just can't figure out the logic of it all.

I really don't understand which bit of the code is the part that searches for a value, and which bit of the code is the part that writes to that same cell in the dbf database - can you break it down for a hapless newbie such as myself?  It would open the door to so many programs I want to write.

Many thanks in advance.

Ian

[Ethan Furman]

If you are to do many searches then you want to create an index [1].

some_table = dbf.Table('/path/to/my/table.dbf')
some_table.open()
temp_index = some_table.create_index(lambda rec: (rec.codigo,
rec.procod))

Now to do the searching, you pass in the values you want to find:

matches = temp_index.search(match=(val1, val2, ...))

If no matches were found `matches` will be empty, otherwise it will be a
list of the matching records.

To update a single record, the easiest way is to use the record as a
context manager:

with record:
record.field1 = value1
record.field2 = value2
# other code

As soon as the `with` block is exited (at the `# other code` line) the
record is written back to disk. If you have several records to update
you can use the `dbf.Process` function:

for record in dbf.Process(list_of_records):
record.field1 = value1
...

At the end of each loop any changes are written to disk.

It's good practice to close the table when you are finally done:

some_table.close()

If you still need help, please provide some samples of what you are
trying to do, and good luck!

--
~Ethan~

[Ian Noble]

Thanks Ethan, I'll experiment tonight and see how I go!

[Ian Noble]

Hi Ethan!

Here is my code right now:

import dbf

table = dbf.Table('players.dbf')

table.open()

index = table.create_index(lambda rec: (rec.fname, rec.name))

print (index)

matches = index.search(match=('Wiggins'))

print (matches)

table.close()

When I print (index) I get:

<dbf.ver_33.Index object at 0x0000000003E15630>

When I print (matches) I get:

<class 'dbf.ver_33.List'>(key=(

        table_name, record_number

        ))

I'm so new to programming I'm not sure what I should be expecting, but does that look correct?

Thanks for your time, I really appreciate it.

Ian

[Ethan Furman]

Those results are correct -- or at least the result types are. Your
call to `index.search` is not correct.

When you create a tuple in Python, you must use at least one comma or
Python thinks you were just adding parenthesis to look cool. ;) So
what you want is

matches = index.search(match=('Wiggins',)) # note the comma!

That's problem one. Problem two is that `search` looks for exact
matches, and passing only one criterion to an index that was built with
two isn't going to match anything. So really what you want is:

matches = index.search(match=('Wiggins',), partial=True)

If memory serves, that will match all records with 'Wiggins' in the
fname field and anything (or nothing) in the name field.

As for your next steps: `matches` is a kind of list, so you can iterate
through it:

for record in matches:
print record.fname, record.name
# or whatever

> Thanks for your time, I really appreciate it.

Any time.

--
~Ethan~

[Ian Noble]

Thank you for your help Ethan!

Here is my finished code, for everyone's reference:

import dbf

print ('Which player do you want to change?')

playername = input('> ')

def index():

table = dbf.Table('players.dbf')

table.open()

with table:

# Create an index of column/s

index = table.create_index(lambda rec: (rec.name))

# Creates a list of matching values

match = index.search(match=(playername,), partial=True)

# Assigns the first value in the list to 'player'

player = match[0]

with player:

print ('Which team should this player join?')

player.team = input('> ')

print (player.team)

table.close()

index()

This program will allow me to assign players on my database to a different team.

Now I just need to get the tkinter GUI up and running and it's finished :) 

[Ethan Furman]

On 11/08/2015 12:43 PM, Ian Noble wrote:

> Here is my finished code, for everyone's reference:
>
> import dbf
>
> print ('Which player do you want to change?')
> playername = input('> ')
>
> def index():
> table = dbf.Table('players.dbf')
> table.open()
> with table:
> # Create an index of column/s
> index = table.create_index(lambda rec: (rec.name))
>
> # Creates a list of matching values
> match = index.search(match=(playername,), partial=True)
>
> # Assigns the first value in the list to 'player'
> player = match[0]
> with player:
> print ('Which team should this player join?')
> player.team = input('> ')
> print (player.team)
>
> table.close()
>
> index()

Thanks for the feedback! One point, though: since you are using `with
table` you don't need the `table.open()` and `table.close()` calls.

--
~Ethan~

[Ian Noble]

Excellent!  Thank you!

[Ian Noble]

Hi again guys,

I thought I'd post the final version of this project I've been working on for posterity.  The project below sources from a .dbf database, finds a specific player and assigns them to a different team.

I hope to use this project on my CV and maybe get a job in Python if I'm lucky!

Thank you Ethan, I couldn't have finished this without you.

import os

import dbf

from tkinter import *

from tkinter import filedialog

# Assign database location by finding most recently edited directory.

def find_database():

saves_dir = r'C:\Users\Ian\Documents\NBA Live 06\saves'

directory_list = []

 

for d in os.listdir(saves_dir):

bd = os.path.join(saves_dir, d)

if os.path.isdir(bd): directory_list.append(bd)

latest_subdir = max((os.path.getmtime(f),f) for f in directory_list)[1]

location = (latest_subdir + '\\' + 'players.dbf')

t = dbf.Table(location)

return t

find_database()

table = find_database()

# Change database location using filedialog if needed.

def change_db():

new_dir = filedialog.askdirectory()

table = new_dir

# Populate OptionMenu lists.

def create_lists():

surname = entry.get()

with table:

# Create an index of column/s

index = table.create_index(lambda rec: (rec.name))

# Creates a list of matching values

matches = index.search(match=(surname,), partial=True)

# Populate playerOption Menu with playerlist.

playerlist = []

for item in matches:

playerlist.append([item[4], item[2], item[1]])

m = playerOption.children['menu']

m.delete(0, END)

for line in playerlist:

m.add_command(label=line,command=lambda v=var,l=line:v.set(l))

# Populate teamOption Menu with teamlist.

n = teamOption.children['menu']

n.delete(0, END)

teamnumbers = []

x=0

for t in teamlist:

n.add_command(label=t,command=lambda v=tvar,l=t:v.set(l))

teamnumbers.append(x)

x = x + 1

# Set player id number

def player_id():

playeridstring = var.get()

firstfive = playeridstring[:5]

four_digit_id = firstfive[1:]

three_digit_id = firstfive[1:4]

two_digit_id = firstfive[1:3]

# Check to ensure player ID is only numbers

if four_digit_id.isdigit() == TRUE:

id = int(four_digit_id)

elif three_digit_id.isdigit() == TRUE:

id = int(three_digit_id)

elif two_digit_id.isdigit() == TRUE:

id = int(two_digit_id)

return id

# Set team id number

def team_id():

teamidstring = tvar.get()

firsttwo = teamidstring[:2]

teamid = int(firsttwo)

return teamid

# Assign new team id to player

def assign():

player_id()

plyid = int(player_id())

team_id()

tmid = team_id()

with table:

# Create an index of column/s

index = table.create_index(lambda rec: (rec.playerid))

# Creates a list of matching values

match = index.search(match=(plyid,), partial=True)

player = match[0]

playern = str(player.fname + player.name)

with player:

player.team = tmid

# Set status_message text.

status_message.config(text = playern + 'has been assigned to the ' + tvar.get())

# Initiate Tkinter

master = Tk()

master.geometry('{}x{}'.format(400, 125))

master.title('Assign a Player to a Team')

# Button to search for a different database location.

fd_button = Button(master, text="...", command=change_db).grid(row = 0, column = 6, sticky=W)

# Entry box for player surname.

entry = Entry(master, width=50)

entry.grid(row = 0, column = 0, columnspan = 5)

# Button to search for surnames.

surname_button = Button(master, text="Go", command=create_lists)

surname_button.grid(row = 0, column = 7, sticky = W)

# Menu for player choosing.

var = StringVar(master)

playerlist = ['']

playerOption = OptionMenu(master, var, *playerlist)

playerOption.grid(row = 1, column = 1, columnspan = 4, sticky = EW)

# Menu for team choosing.

tvar = StringVar(master)

teamlist = ['0 Atlanta Hawks', '1 Boston Celtics', '2 Charlotte Hornets', '3 Chicago Bulls', '4 Cleveland Cavaliers', '5 Dallas Mavericks', '6 Denver Nuggets', '7 Detroit Pistons', '8 Golden State Warriors', '9 Houston Rockets', '10 Indiana Pacers', '11 LA Clippers Clippers', '12 LA Lakers Lakers', '13 Memphis Grizzlies', '14 Miami Heat', '15 Milwaukee Bucks', '16 Minnesota Timberwolves', '17 Brooklyn Nets', '18 New Orleans Pelicans', '19 New York Knicks', '20 Orlando Magic', '21 Philadelphia 76ers', '22 Phoenix Suns', '23 Portland Trail Blazers', '24 Sacramento Kings', '25 San Antonio Spurs', '26 Oklahoma City Thunder', '27 Toronto Raptors', '28 Utah Jazz', '29 Washington Wizards', '50 Free Agents']

teamOption = OptionMenu(master, tvar, *teamlist)

teamOption.grid(row = 2, column = 1, columnspan = 4, sticky = EW)

# Button to assign player to team.

assign_button = Button(master, text="Assign", command=assign)

assign_button.grid(row = 2, column = 6, columnspan = 2)

# Status message.

status_message = Message(master, text='', width = 400, anchor = W)

status_message.grid(row = 3, column = 0, columnspan = 8, sticky = EW)

mainloop() 

[Ethan Furman]

On 11/22/2015 08:23 AM, Ian Noble wrote:

> I thought I'd post the final version of this project I've been working

> on for posterity. The project belowsources from a .dbf database, finds

> a specific player and assigns them to a different team.

> I hope to use this project on my CV and maybe get a job in Python if I'm
> lucky!

Thanks for the example, and good luck in your job hunt!

--
~Ethan~