c# pdf to image conversion : Change font size pdf form SDK control service wpf azure html dnn book_00718-part1813

14.6. SpideringTwitterusingadatabase
167
14.6 SpideringTwitterusingadatabase
Inthissection,wewillcreateasimplespideringprogramthatwillgothrough
Twitteraccountsandbuildadatabaseofthem.Note:Beverycarefulwhenrunning
thisprogram. Youdonotwanttopulltoomuchdataorruntheprogramfortoo
longandenduphavingyourTwitteraccessshutoff.
Oneoftheproblemsofanykindofspideringprogramisthatitneedstobeable
tobestoppedandrestartedmanytimesandyoudonotwanttolosethedatathat
youhaveretrievedsofar. Youdon’twanttoalwaysrestartyourdataretrievalat
theverybeginningsowewanttostoredataasweretrieveitsoourprogramcan
startbackupandpickupwhereitleftoff.
Wewillstartbyretrievingoneperson’sTwitterfriendsandtheirstatuses,looping
throughthe listoffriends, andaddingeachofthefriendstoa databaseto be
retrievedinthefuture. Afterweprocessoneperson’sTwitterfriends,wecheck
inourdatabaseandretrieveoneofthefriendsofthefriend. Wedothisoverand
over,pickingan“unvisited”person,retrievingtheirfriendlistandaddingfriends
wehavenotseentoourlistforafuturevisit.
Wealsotrackhowmanytimeswehaveseenaparticularfriendinthedatabaseto
getsomesenseof“popularity”.
Bystoringourlistofknownaccountsandwhetherwehaveretrievedtheaccount
ornot,andhowpopulartheaccountisinadatabaseonthediskofthecomputer,
wecanstopandrestartourprogramasmanytimesaswelike.
Thisprogramisabitcomplex.Itisbasedonthecodefromtheexerciseearlierin
thebookthatusestheTwitterAPI.
HereisthesourcecodeforourTwitterspideringapplication:
import sqlite3
import urllib
import xml.etree.ElementTree e as ET
TWITTER_URL =
'
http://api.twitter.com/l/statuses/friends/ACCT.xml
'
conn = = sqlite3.connect(
'
twdata.db
'
)
cur = = conn.cursor()
cur.execute(
'''
CREATE TABLE E IF F NOT T EXISTS
Twitter (name TEXT, retrieved INTEGER, friends s INTEGER)
'''
)
while True:
acct = raw_input(
'
Enter a Twitter account, , or r quit:
'
)
if ( ( acct ==
'
quit
'
) : : break
if ( ( len(acct) < 1 ) :
cur.execute(
'
SELECT name FROM Twitter r WHERE E retrieved = 0 LIMIT 1
'
)
try:
acct = = cur.fetchone()[0]
Change font size pdf form - C# PDF Field Edit Library: insert, delete, update pdf form field in C#.net, ASP.NET, MVC, Ajax, WPF
Online C# Tutorial to Insert, Delete and Update Fields in PDF Document
create a fillable pdf form; add text field to pdf acrobat
Change font size pdf form - VB.NET PDF Field Edit library: insert, delete, update pdf form field in vb.net, ASP.NET, MVC, Ajax, WPF
How to Insert, Delete and Update Fields in PDF Document with VB.NET Demo Code
add attachment to pdf form; add editable fields to pdf
168
Chapter14. UsingdatabasesandStructuredQueryLanguage(SQL)
except:
print
'
No unretrieved Twitter accounts found
'
continue
url = TWITTER_URL.replace(
'
ACCT
'
, acct)
print
'
Retrieving
'
, url
document = urllib.urlopen (url).read()
tree = ET.fromstring(document)
cur.execute(
'
UPDATE Twitter SET T retrieved=1 1 WHERE name = ?
'
, (acct, ) )
countnew = 0
countold = 0
for user in n tree.findall(
'
user
'
):
friend = = user.find(
'
screen_name
'
).text
cur.execute(
'
SELECT friends s FROM M Twitter WHERE name = ? LIMIT 1
'
,
(friend, ) )
try:
count = cur.fetchone()[0]
cur.execute(
'
UPDATE Twitter SET friends = ? ? WHERE E name e = = ?
'
,
(count+1, friend) ) )
countold = countold + 1
except:
cur.execute(
'''
INSERT INTO O Twitter (name, retrieved, , friends)
VALUES ( ?, 0, 1 )
'''
, ( friend, ) )
countnew = countnew + 1
print
'
New accounts=
'
,countnew,
'
revisited=
'
,countold
conn.commit()
cur.close()
Ourdatabaseisstoredinthefile
twdata.db
andithasonetablenamed
Twitter
andeachrowinthe
Twitter
tablehasacolumnfortheaccountname,whether
wehaveretrievedthefriendsofthisaccount,andhowmanytimesthisaccount
hasbeen“friended”.
Inthemainloopoftheprogram,weprompttheuserforaTwitteraccountname
or“quit”toexittheprogram. IftheuserentersaTwitteraccount,weretrieve
thelistoffriendsandstatusesforthatuserandaddeachfriendtothedatabaseif
notalreadyinthedatabase. Ifthefriendisalreadyinthelist,weaddonetothe
friends
fieldintherowinthedatabase.
Iftheuserpressesenter,welookinthedatabaseforthenextTwitteraccountthat
wehavenotyetretrievedandretrievethefriendsandstatusesforthataccount,add
themtothedatabaseorupdatethemandincreasetheir
friends count
.
Onceweretrievethelistoffriendsandstatuses,weloopthroughallofthe
user
itemsinthereturnedXMLandretrievethe
screen_name
foreachuser. Then
weusethe
SELECT
statementtoseeifwealreadyhavestoredthisparticular
screen_name
inthedatabaseandretrievethefriendcount(
friends
)iftherecord
exists.
countnew = 0
C# PDF File Compress Library: Compress reduce PDF size in C#.net
can help to reduce PDF file size effectively. RasterEdge.Imaging.Font.dll. ops.MonochromeImageOptions.TargetResolution = 150F; // to change image compression
adding text field to pdf; change font size in pdf form
VB.NET PDF File Compress Library: Compress reduce PDF size in vb.
can help to reduce PDF file size effectively Reduce font resources: Font resources will also take up too TargetResolution = 150.0F 'to change image compression
change font size pdf form; add email button to pdf form
14.6. SpideringTwitterusingadatabase
169
countold = 0
for user in n tree.findall(
'
user
'
):
friend = = user.find(
'
screen_name
'
).text
cur.execute(
'
SELECT friends s FROM M Twitter WHERE name = ? LIMIT 1
'
,
(friend, ) )
try:
count = cur.fetchone()[0]
cur.execute(
'
UPDATE Twitter SET friends = ? ? WHERE E name e = = ?
'
,
(count+1, friend) ) )
countold = countold + 1
except:
cur.execute(
'''
INSERT INTO O Twitter (name, retrieved, , friends)
VALUES ( ?, 0, 1 )
'''
, ( friend, ) )
countnew = countnew + 1
print
'
New accounts=
'
,countnew,
'
revisited=
'
,countold
conn.commit()
Oncethecursorexecutesthe
SELECT
statement,wemustretrievetherows. We
coulddothiswitha
for
statement,butsinceweareonlyretrievingonerow(
LIMIT
1
),wecanusethe
fetchone()
methodtofetchthefirst(andonly)rowthatisthe
resultofthe
SELECT
operation.Since
fetchone()
returnstherowasatuple(even
thoughthereisonlyonefield),wetakethefirstvaluefromthetupleusing
[0]
to
getthecurrentfriendcountintothevariable
count
.
Ifthisretrievalissuccessful,weusetheSQL
UPDATE
statementwitha
WHERE
clausetoaddonetothe
friends
columnfortherowthatmatchesthefriend’s
account.Noticethattherearetwoplaceholders(i.e.questionmarks)intheSQL,
andthesecondparametertothe
execute()
isatwo-elementtuplewhichholds
thevaluestobesubstitutedintotheSQLinplaceofthequestionmarks.
Ifthecodeinthe
try
blockfailsitisprobablybecausenorecordmatchedthe
WHERE name = = ?
clauseontheSELECTstatement. Sointhe
except
block,
weusetheSQL
INSERT
statementtoaddthefriend’s
screen_name
tothetable
withanindicationthatwehavenotyetretrievedthe
screen_name
andsettingthe
friendcounttozero.
SothefirsttimetheprogramrunsandweenteraTwitteraccount,theprogram
runsasfollows:
Enter a a Twitter r account, , or quit: drchuck
Retrieving http://api.twitter.com/l/statuses/friends/drchuck.xml
New accounts= 100
revisited= 0
Enter a a Twitter r account, , or quit: quit
Sincethisisthefirsttimewehaveruntheprogram,thedatabaseisemptyandwe
createthedatabaseinthefile
twdata.db
andaddatablenamed
Twitter
tothe
database.Thenweretrievesomefriendsandaddthemalltothedatabasesincethe
databaseisempty.
Atthispoint,wemightwanttowriteasimpledatabasedumpertotakealookat
whatisinour
twdata.db
file:
C# PDF insert text Library: insert text into PDF content in C#.net
Powerful .NET PDF edit control allows modify existing scanned PDF text. Ability to change text font, color, size and location and output a new PDF document.
add form fields to pdf without acrobat; android edit pdf forms
C# PDF Annotate Library: Draw, edit PDF annotation, markups in C#.
Able to edit and change PDF annotation properties such as font size or color. Abilities to draw markups on PDF document or stamp on PDF file.
create a pdf form to fill out; add jpg to pdf form
170
Chapter14. UsingdatabasesandStructuredQueryLanguage(SQL)
import sqlite3
conn = = sqlite3.connect(
'
twdata.db
'
)
cur = = conn.cursor()
cur.execute(
'
SELECT * FROM Twitter
'
)
count = = 0
for row in cur :
print row
count = count t + + 1
print count,
'
rows.
'
cur.close()
Thisprogramsimplyopensthedatabaseandselectsallofthecolumnsofallofthe
rowsinthetable
Twitter
,thenloopsthroughtherowsandprintsouteachrow.
IfwerunthisprogramafterthefirstexecutionofourTwitterspiderabove, its
outputwillbeasfollows:
(u
'
opencontent
'
, 0, 1)
(u
'
lhawthorn
'
, 0, , 1)
(u
'
steve_coppin
'
, 0, 1)
(u
'
davidkocher
'
, 0, 1)
(u
'
hrheingold
'
, 0, 1)
...
100 rows.
Weseeonerowforeach
screen_name
,thatwehavenotretrievedthedataforthat
screen_name
andeveryoneinthedatabasehasonefriend.
NowourdatabasereflectstheretrievalofthefriendsofourfirstTwitteraccount
(drchuck).Wecanruntheprogramagainandtellittoretrievethefriendsofthe
next“unprocessed”accountbysimplypressingenterinsteadofaTwitteraccount
asfollows:
Enter a a Twitter r account, , or quit:
Retrieving http://api.twitter.com/l/statuses/friends/opencontent.xml
New accounts= 98
revisited= 2
Enter a a Twitter r account, , or quit:
Retrieving http://api.twitter.com/l/statuses/friends/lhawthorn.xml
New accounts= 97
revisited= 3
Enter a a Twitter r account, , or quit: quit
Sincewepressedenter(i.e. wedidnotspecifyaTwitteraccount),thefollowing
codeisexecuted:
if ( ( len(acct) < 1 ) :
cur.execute(
'
SELECT name FROM Twitter r WHERE E retrieved = 0 LIMIT 1
'
)
try:
acct = = cur.fetchone()[0]
except:
print
'
No unretrieved twitter accounts found
'
continue
WeusetheSQL
SELECT
statementtoretrievethenameofthefirst(
LIMIT 1
)user
whostillhastheir“haveweretrievedthisuser”valuesettozero.Wealsousethe
C# PDF Sticky Note Library: add, delete, update PDF note in C#.net
Allow users to add comments online in ASPX webpage. Able to change font size in PDF comment box. Able to save and print sticky notes in PDF file.
best way to make pdf forms; create a pdf form that can be filled out
C# PDF Convert to Word SDK: Convert PDF to Word library in C#.net
PDF document, keeps the elements (like images, tables and chats) of original PDF file and maintains the original text style (including font, size, color, links
add form fields to pdf online; pdf form save
14.6. SpideringTwitterusingadatabase
171
fetchone()[0]
patternwithinatry/exceptblocktoeitherextracta
screen_name
fromtheretrieveddataorputoutanerrormessageandloopbackup.
Ifwesuccessfullyretrievedanunprocessed
screen_name
,weretrievetheirdata
asfollows:
url = TWITTER_URL.replace(
'
ACCT
'
, acct)
print
'
Retrieving
'
, url
document = urllib.urlopen (url).read()
tree = ET.fromstring(document)
cur.execute(
'
UPDATE Twitter SET T retrieved=1 1 WHERE name = ?
'
, (acct, ) )
Onceweretrievethedatasuccessfully, weusethe
UPDATE
statementtosetthe
retrieved
columntoonetoindicatethatwehavecompletedtheretrievalofthe
friendsofthisaccount. Thiskeepsusfromre-retrievingthesamedataoverand
overandkeepsusprogressingforwardthroughthenetworkofTwitterfriends.
Ifwerunthefriendprogramandpressentertwicetoretrievethenextunvisited
friend’sfriends,thenrunthedumpingprogram,itwillgiveusthefollowingout-
put:
(u
'
opencontent
'
, 1, 1)
(u
'
lhawthorn
'
, 1, , 1)
(u
'
steve_coppin
'
, 0, 1)
(u
'
davidkocher
'
, 0, 1)
(u
'
hrheingold
'
, 0, 1)
...
(u
'
cnxorg
'
, 0, , 2)
(u
'
knoop
'
, 0, , 1)
(u
'
kthanos
'
, 0, 2)
(u
'
LectureTools
'
, 0, 1)
...
295 rows.
We cansee that we have properly recorded that we e have e visited
lhawthorn
and
opencontent
. Alsotheaccounts
cnxorg
and
kthanos
alreadyhave two
followers. Sincewenowhaveretrievedthefriendsofthreepeople(
drchuck
,
opencontent
and
lhawthorn
)ourtablehas295rowsoffriendstoretrieve.
Eachtime weruntheprogramandpress enter, itwillpickthenextunvisited
account(e.g.thenextaccountwillbe
steve_coppin
),retrievetheirfriends,mark
themasretrievedandforeachofthefriendsof
steve_coppin
,eitheraddthem
totheendofthedatabase,orupdatetheirfriendcountiftheyarealreadyinthe
database.
Sincetheprogram’sdataisallstoredondiskinadatabase,thespideringactivity
canbesuspendedandresumedasmanytimesasyoulikewithnolossofdata.
Note: Onemoretimebeforeweleavethistopic,beverycarefulwhenrunning
thisTwitterspideringprogram.Youdonotwanttopulltoomuchdataorrunthe
programfortoolongandenduphavingyourTwitteraccessshutoff.
Generate Barcodes in Web Image Viewer| Online Tutorials
Select "Generate" to process barcode generation; Change Barcode Properties. Select "Font" to choose human-readable text font style, color, size and effects;
change font in pdf fillable form; create pdf form
VB.NET Image: Visual Basic .NET Guide to Draw Text on Image in .
Please note that you can change some of the example, you can adjust the text font, font size, font type (regular LoadImage) Dim DrawFont As New Font("Arial", 16
convert word document to editable pdf form; adding text to a pdf form
172
Chapter14. UsingdatabasesandStructuredQueryLanguage(SQL)
14.7 Basicdatamodeling
Therealpowerofarelationaldatabaseiswhenwemakemultipletablesandmake
linksbetweenthosetables. Theactofdecidinghowtobreakupyourapplication
dataintomultipletablesandestablishingtherelationshipsbetweenthetwotables
iscalleddatamodeling. Thedesigndocumentthatshowsthetablesandtheir
relationshipsiscalledadatamodel.
Datamodelingisarelativelysophisticatedskillandwewillonlyintroducethe
mostbasicconceptsofrelationaldatamodelinginthissection.Formoredetailon
datamodelingyoucanstartwith:
http://en.wikipedia.org/wiki/Relational_model
Let’ssayforourTwitterspiderapplication, insteadofjustcountingaperson’s
friends,wewantedtokeepalistofalloftheincomingrelationshipssowecould
findalistofeveryonewhoisfollowingaparticularaccount.
Sinceeveryonewillpotentiallyhavemanyaccountsthatfollowthem,wecannot
simplyaddasinglecolumntoour
Twitter
table. Sowecreateanewtablethat
keepstrackofpairsoffriends. Thefollowingisasimplewayofmakingsucha
table:
CREATE TABLE E Pals (from_friend TEXT, , to_friend TEXT)
Eachtimeweencounterapersonwho
drchuck
isfollowing,wewouldinserta
rowoftheform:
INSERT INTO O Pals s (from_friend,to_friend) ) VALUES S (
'
drchuck
'
,
'
lhawthorn
'
)
Asweareprocessingthe100friendsfromthe
drchuck
Twitterfeed,wewillinsert
100recordswith“drchuck”asthefirstparametersowewillendupduplicating
thestringmanytimesinthedatabase.
Thisduplicationofstringdataviolatesthebestpracticesfordatabasenormal-
izationwhichbasicallystatesthatweshouldneverputthesamestringdatainthe
databasemorethanonce.Ifweneedthedatamorethanonce,wecreateanumeric
keyforthedataandreferencetheactualdatausingthiskey.
Inpracticalterms,astringtakesupalotmorespacethananintegeronthedisk
andinthememoryofourcomputerandtakesmoreprocessortimetocompareand
sort.Ifweonlyhaveafewhundredentriesthestorageandprocessortimehardly
matters.Butifwehaveamillionpeopleinourdatabaseandapossibilityof100
millionfriendlinks,itisimportanttobeabletoscandataasquicklyaspossible.
WewillstoreourTwitteraccountsinatablenamed
People
insteadofthe
Twitter
tableusedinthepreviousexample. The
People
tablehasanadditionalcolumn
tostorethenumerickeyassociatedwiththerowforthisTwitteruser.SQLitehas
afeaturethatautomaticallyaddsthekeyvalueforanyrowweinsertintoatable
usingaspecialtypeofdatacolumn(
INTEGER PRIMARY Y KEY
).
Generate Image in .NET Winforms Imaging Viewer| Online Tutorials
Click "Generate" to process barcode generation; Change Barcode Properties. Click "Font" to choose human-readable text font style, color, size and effects.
create a pdf form; chrome save pdf with fields
14.8. Programmingwithmultipletables
173
Wecancreatethe
People
tablewiththisadditional
id
columnasfollows:
CREATE TABLE E People
(id INTEGER R PRIMARY Y KEY, name TEXT UNIQUE, , retrieved INTEGER)
Noticethatwearenolongermaintainingafriendcountineachrowofthe
People
table. Whenweselect
INTEGER PRIMARY Y KEY
asthe typeofour
id
column,
weareindicatingthatwewouldlikeSQLitetomanagethiscolumnandassigna
uniquenumerickeytoeachrowweinsertautomatically.Wealsoaddthekeyword
UNIQUE
toindicatethatwewillnotallowSQLitetoinserttworowswiththesame
valuefor
name
.
Nowinsteadofcreatingthetable
Pals
above,wecreateatablecalled
Follows
withtwointegercolumns
from_id
and
to_id
andaconstraintonthetablethatthe
combinationof
from_id
and
to_id
mustbeuniqueinthistable(i.e. wecannot
insertduplicaterows)inourdatabase.
CREATE TABLE E Follows
(from_id INTEGER, to_id INTEGER, , UNIQUE(from_id, , to_id) )
Whenweadd
UNIQUE
clausestoourtables,wearecommunicatingasetofrules
thatweareasking thedatabasetoenforcewhenweattempttoinsertrecords.
Wearecreatingtheserulesasaconvenienceinourprogramsaswewillseeina
moment. Therulesbothkeepusfrommakingmistakesandmakeitsimplerto
writesomeofourcode.
Inessence,increatingthis
Follows
table,wearemodellinga”relationship”where
oneperson”follows”someoneelseandrepresentingitwithapairofnumbersin-
dicatingthat(a)thepeopleareconnectedand(b)thedirectionoftherelationship.
People
name
drchuck
opencontent
1
1
retrieved
Follows
from_id
1
1
3
to_id
id
1
2
3
4
lhawthorn
steve_coppin
1
0
2
1
4
...
...
14.8 Programming with multiple tables
We will now re-do the Twitter spider program using two tables, the primary keys,
and the key references as described above. Here is the code for the new version of
174
Chapter 14. Using databases and Structured Query Language (SQL)
the program:
import sqlite3
import urllib
import xml.etree.ElementTree as ET
TWITTER_URL =
'
http://api.twitter.com/l/statuses/friends/ACCT.xml
'
conn = sqlite3.connect(
'
twdata.db
'
)
cur = conn.cursor()
cur.execute(
'''
CREATE TABLE IF NOT EXISTS People
(id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)
'''
)
cur.execute(
'''
CREATE TABLE IF NOT EXISTS Follows
(from_id INTEGER, to_id INTEGER, UNIQUE(from_id, to_id))
'''
)
while True:
acct = raw_input(
'
Enter a Twitter account, or quit:
'
)
if ( acct ==
'
quit
'
) : break
if ( len(acct) < 1 ) :
cur.execute(
'''
SELECT id,name FROM People
WHERE retrieved = 0 LIMIT 1
'''
)
try:
(id, acct) = cur.fetchone()
except:
print
'
No unretrieved Twitter accounts found
'
continue
else:
cur.execute(
'
SELECT id FROM People WHERE name = ? LIMIT 1
'
,
(acct, ) )
try:
id = cur.fetchone()[0]
except:
cur.execute(
'''
INSERT OR IGNORE INTO People
(name, retrieved) VALUES ( ?, 0)
'''
, ( acct, ) )
conn.commit()
if cur.rowcount != 1 :
print
'
Error inserting account:
'
,acct
continue
id = cur.lastrowid
url = TWITTER_URL.replace(
'
ACCT
'
, acct)
print
'
Retrieving
'
, url
document = urllib.urlopen (url).read()
tree = ET.fromstring(document)
cur.execute(
'
UPDATE People SET retrieved=1 WHERE name = ?
'
, (acct, ) )
countnew = 0
countold = 0
for user in tree.findall(
'
user
'
):
friend = user.find(
'
screen_name
'
).text
cur.execute(
'
SELECT id FROM People WHERE name = ? LIMIT 1
'
,
(friend, ) )
try:
14.8. Programming with multiple tables
175
friend_id = cur.fetchone()[0]
countold = countold + 1
except:
cur.execute(
'''
INSERT OR IGNORE INTO People (name, retrieved)
VALUES ( ?, 0)
'''
, ( friend, ) )
conn.commit()
if cur.rowcount != 1 :
print
'
Error inserting account:
'
,friend
continue
friend_id = cur.lastrowid
countnew = countnew + 1
cur.execute(
'''
INSERT OR IGNORE INTO Follows
(from_id, to_id) VALUES (?, ?)
'''
, (id, friend_id) )
print
'
New accounts=
'
,countnew,
'
revisited=
'
,countold
conn.commit()
cur.close()
This program is starting to get a bit complicated, but it illustrates the patterns that
we need to use when we are using integer keys to link tables. The basic patterns
are:
1. Creating tables with primary keys and constraints.
2. When we have a logical key for a person (i.e. account name) and we need
the
id
value for the person. Depending on whether or not the person is
already in the
People
table, we either need to: (1) look up the person in the
People
table and retrieve the
id
value for the person or (2) add the person
the the
People
table and get the
id
value for the newly added row.
3. Insert the row that captures the “follows” relationship.
We will cover each of these in turn.
14.8.1 Constraints in database tables
As we design our table structures, we can tell the database system that we would
like it to enforce a few rules on us. These rules help us from making mistakes and
introducing incorrect data into out tables. When we create our tables:
cur.execute(
'''
CREATE TABLE IF NOT EXISTS People
(id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)
'''
)
cur.execute(
'''
CREATE TABLE IF NOT EXISTS Follows
(from_id INTEGER, to_id INTEGER, UNIQUE(from_id, to_id))
'''
)
We indicate that the
name
column in the
People
table must be
UNIQUE
.We also
indicate that the combination of the two numbers in each row of the
Follows
table
must be unique. These constraints keep us from making mistakes such as adding
the same relationship more than once.
We can take advantage of these constraints in the following code:
176
Chapter 14. Using databases and Structured Query Language (SQL)
cur.execute(
'''
INSERT OR IGNORE INTO People (name, retrieved)
VALUES ( ?, 0)
'''
, ( friend, ) )
We add the
OR IGNORE
clause to our
INSERT
statement to indicate that if this par-
ticular
INSERT
would cause a violation of the “
name
must be unique” rule, the
database system is allowed to ignore the
INSERT
.We are using the database con-
straint as a safety net to make sure we don’t inadvertently do something incorrect.
Similarly, the following code ensures that we don’t add the exact same
Follows
relationship twice.
cur.execute(
'''
INSERT OR IGNORE INTO Follows
(from_id, to_id) VALUES (?, ?)
'''
, (id, friend_id) )
Again we simply tell the database to ignore our attempted
INSERT
if it would
violate the uniqueness constraint that we specified for the
Follows
rows.
14.8.2 Retrieve and/or insert a record
When we prompt the user for a Twitter account, if the account exists, we must
look up its
id
value. If the account does not yet exist in the
People
table, we must
insert the record and get the
id
value from the inserted row.
This is a very common pattern and is done twice in the program above. This code
shows how we look up the
id
for a friend’s account when we have extracted a
screen_name
from a
user
node in the retrieved Twitter XML.
Since over time it will be increasingly likely that the account will already be in
the database, we first check to see if the
People
record exists using a
SELECT
statement.
If all goes well
2
inside the
try
section, we retrieve the record using
fetchone()
and then retrieve the first (and only) element of the returned tuple and store it in
friend_id
.
If the
SELECT
fails, the
fetchone()[0]
code will fail and control will transfer
into the
except
section.
friend = user.find(
'
screen_name
'
).text
cur.execute(
'
SELECT id FROM People WHERE name = ? LIMIT 1
'
,
(friend, ) )
try:
friend_id = cur.fetchone()[0]
countold = countold + 1
except:
cur.execute(
'''
INSERT OR IGNORE INTO People (name, retrieved)
VALUES ( ?, 0)
'''
, ( friend, ) )
conn.commit()
2
In general, when a sentence starts with “if all goes well” you will find that the code needs to
use try/except.
Documents you may be interested
Documents you may be interested