8000 fix cursor use · Dineshjs/python_reference@5da40c3 · GitHub
[go: up one dir, main page]
Skip to content

Commit 5da40c3

Browse files
rasbtrasbt
committed
fix cursor use
1 parent d93f9fd commit 5da40c3

File tree

2 files changed

+57
-48
lines changed

2 files changed

+57
-48
lines changed

‎tutorials/sqlite3_howto/README.md

Lines changed: 33 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -682,77 +682,81 @@ convenient script to print a nice overview of SQLite database tables:
682682

683683

684684
import sqlite3
685-
685+
686+
686687
def connect(sqlite_file):
687688
""" Make connection to an SQLite database file """
688689
conn = sqlite3.connect(sqlite_file)
689690
c = conn.cursor()
690691
return conn, c
691-
692+
693+
692694
def close(conn):
693695
""" Commit changes and close connection to the database """
694696
# conn.commit()
695697
conn.close()
696-
698+
699+
697700
def total_rows(cursor, table_name, print_out=False):
698701
""" Returns the total number of rows in the database """
699-
c.execute('SELECT COUNT(*) FROM {}'.format(table_name))
700-
count = c.fetchall()
702+
cursor.execute('SELECT COUNT(*) FROM {}'.format(table_name))
703+
count = cursor.fetchall()
701704
if print_out:
702705
print('\nTotal rows: {}'.format(count[0][0]))
703706
return count[0][0]
704-
707+
708+
705709
def table_col_info(cursor, table_name, print_out=False):
706-
"""
707-
Returns a list of tuples with column informations:
708-
(id, name, type, notnull, default_value, primary_key)
709-
710+
""" Returns a list of tuples with column informations:
711+
(id, name, type, notnull, default_value, primary_key)
710712
"""
711-
c.execute('PRAGMA TABLE_INFO({})'.format(table_name))
712-
info = c.fetchall()
713-
713+
cursor.execute('PRAGMA TABLE_INFO({})'.format(table_name))
714+
info = cursor.fetchall()
715+
714716
if print_out:
715717
print("\nColumn Info:\nID, Name, Type, NotNull, DefaultVal, PrimaryKey")
716718
for col in info:
717719
print(col)
718720
return info
719-
721+
722+
720723
def values_in_col(cursor, table_name, print_out=True):
721-
""" Returns a dictionary with columns as keys and the number of not-null
722-
entries as associated values.
724+
""" Returns a dictionary with columns as keys
725+
and the number of not-null entries as associated values.
723726
"""
724-
c.execute('PRAGMA TABLE_INFO({})'.format(table_name))
725-
info = c.fetchall()
727+
cursor.execute('PRAGMA TABLE_INFO({})'.format(table_name))
728+
info = cursor.fetchall()
726729
col_dict = dict()
727730
for col in info:
728731
col_dict[col[1]] = 0
729732
for col in col_dict:
730-
c.execute('SELECT ({0}) FROM {1} WHERE {0} IS NOT NULL'.format(col, table_name))
731-
# In my case this approach resulted in a better performance than using COUNT
733+
c.execute('SELECT ({0}) FROM {1} '
734+
'WHERE {0} IS NOT NULL'.format(col, table_name))
735+
# In my case this approach resulted in a
736+
# better performance than using COUNT
732737
number_rows = len(c.fetchall())
733738
col_dict[col] = number_rows
734739
if print_out:
735740
print("\nNumber of entries per column:")
736741
for i in col_dict.items():
737742
print('{}: {}'.format(i[0], i[1]))
738743
return col_dict
739-
740-
744+
745+
741746
if __name__ == '__main__':
742-
747+
743748
sqlite_file = 'my_first_db.sqlite'
744749
table_name = 'my_table_3'
745-
750+
746751
conn, c = connect(sqlite_file)
747752
total_rows(c, table_name, print_out=True)
748753
table_col_info(c, table_name, print_out=True)
749-
values_in_col(c, table_name, print_out=True) # slow on large data bases
750-
754+
# next line might be slow on large databases
755+
values_in_col(c, table_name, print_out=True)
756+
751757
close(conn)
752-
753758

754-
Download the script: [print_db_info.py](https://raw.github.com/rasbt/python_sq
755-
lite_code/master/code/print_db_info.py)
759+
Download the script: [print_db_info.py](code/print_db_info.py)
756760

757761
![8_sqlite3_print_db_info_1.png](../../Images/8_sqlite3_print_db_info_1.png)
758762

‎tutorials/sqlite3_howto/code/print_db_info.py

Lines changed: 24 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -22,52 +22,57 @@
2222

2323
import sqlite3
2424

25+
2526
def connect(sqlite_file):
2627
""" Make connection to an SQLite database file """
2728
conn = sqlite3.connect(sqlite_file)
2829
c = conn.cursor()
2930
return conn, c
3031

32+
3133
def close(conn):
3234
""" Commit changes and close connection to the database """
33-
#conn.commit()
35+
# conn.commit()
3436
conn.close()
3537

38+
3639
def total_rows(cursor, table_name, print_out=False):
3740
""" Returns the total number of rows in the database """
38-
c.execute('SELECT COUNT(*) FROM {}'.format(table_name))
39-
count = c.fetchall()
41+
cursor.execute('SELECT COUNT(*) FROM {}'.format(table_name))
42+
count = cursor.fetchall()
4043
if print_out:
4144
print('\nTotal rows: {}'.format(count[0][0]))
4245
return count[0][0]
4346

47+
4448
def table_col_info(cursor, table_name, print_out=False):
45-
"""
46-
Returns a list of tuples with column informations:
47-
(id, name, type, notnull, default_value, primary_key)
48-
49+
""" Returns a list of tuples with column informations:
50+
(id, name, type, notnull, default_value, primary_key)
4951
"""
50-
c.execute('PRAGMA TABLE_INFO({})'.format(table_name))
51-
info = c.fetchall()
52-
52+
cursor.execute('PRAGMA TABLE_INFO({})'.format(table_name))
53+
info = cursor.fetchall()
54+
5355
if print_out:
5456
print("\nColumn Info:\nID, Name, Type, NotNull, DefaultVal, PrimaryKey")
5557
for col in info:
5658
print(col)
5759
return info
5860

61+
5962
def values_in_col(cursor, table_name, print_out=True):
60-
""" Returns a dictionary with columns as keys and the number of not-null
61-
entries as associated values.
63+
""" Returns a dictionary with columns as keys
64+
and the number of not-null entries as associated values.
6265
"""
63-
c.execute('PRAGMA TABLE_INFO({})'.format(table_name))
64-
info = c.fetchall()
66+
cursor.execute('PRAGMA TABLE_INFO({})'.format(table_name))
67+
info = cursor.fetchall()
6568
col_dict = dict()
6669
for col in info:
6770
col_dict[col[1]] = 0
6871
for col in col_dict:
69-
c.execute('SELECT ({0}) FROM {1} WHERE {0} IS NOT NULL'.format(col, table_name))
70-
# In my case this approach resulted in a better performance than using COUNT
72+
c.execute('SELECT ({0}) FROM {1} '
73+
'WHERE {0} IS NOT NULL'.format(col, table_name))
74+
# In my case this approach resulted in a
75+
# better performance than using COUNT
7176
number_rows = len(c.fetchall())
7277
col_dict[col] = number_rows
7378
if print_out:
@@ -85,7 +90,7 @@ def values_in_col(cursor, table_name, print_out=True):
8590
conn, c = connect(sqlite_file)
8691
total_rows(c, table_name, print_out=True)
8792
table_col_info(c, table_name, print_out=True)
88-
values_in_col(c, table_name, print_out=True) # slow on large data bases
89-
90-
close(conn)
93+
# next line might be slow on large databases
94+
values_in_col(c, table_name, print_out=True)
9195

96+
close(conn)

0 commit comments

Comments
 (0)
0