Skip to content

Instantly share code, notes, and snippets.

@bchhun
Last active August 29, 2015 13:56
Show Gist options
  • Save bchhun/8909903 to your computer and use it in GitHub Desktop.
Save bchhun/8909903 to your computer and use it in GitHub Desktop.
La magie de SQLAlchemy
import os
import inspect
from datetime import datetime
from fabric.colors import red, green
from sqlite3 import dbapi2 as sqlite
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import (
Column, Integer, String, DateTime, Date
)
CURRENT_DIRECTORY = os.path.dirname(
inspect.getfile(inspect.currentframe())
)
DATABASE_NAME = "users"
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column("id", Integer, primary_key = True)
nas = Column("nas", Integer)
country = Column("country", String(200))
fullname = Column("fullname", String(200))
created_at = Column("created_at", DateTime)
birthday = Column("birthday", Date)
email = Column("email", String(200))
def connect(debug=False):
path = os.path.join(CURRENT_DIRECTORY, "{}.db".format(DATABASE_NAME))
engine = create_engine(
'sqlite+pysqlite:///{}'.format(path),
module=sqlite,
echo=debug
)
SessionMakerInstance = sessionmaker(bind=engine)
return SessionMakerInstance()
def init_db():
path = os.path.join(CURRENT_DIRECTORY, "{}.db".format(DATABASE_NAME))
if os.path.isfile(path):
print(red(u"La base de données existe déjà..."))
print(red(u"Sa création est annulé"))
return
sqlite.connect(path)
engine = create_engine('sqlite:///%s' % path, convert_unicode=True)
Base.metadata.create_all(bind=engine)
print(green(u"Base de données créé !"))
# ===========================
import csv
from glob import glob
from datetime import datetime
CSV_PATH = os.path.join("..", "data", "*.csv")
csv_files = glob(CSV_PATH)
csv_files.reverse()
if __name__ == '__main__':
init_db()
session = connect(debug=True)
for csv_file in csv_files:
# ouverture du fichier
with open(csv_file) as f:
csv_content = csv.DictReader(f)
session.add_all((
User(
nas=row["nas"],
country=row["country"],
fullname=row["fullname"],
# Tue Dec 31 1996 11:35:43
created_at=datetime.strptime(
row["created_at"], "%a %b %d %Y %H:%M:%S"
).date(),
birthday=datetime.strptime(
row["birthday"], "%d/%m/%Y"
).date(),
email=row["email"]
) for row in csv_content
))
session.commit()
country nas fullname created_at birthday email
Maldives 122 Tre Kunde Sat Oct 04 1980 19:18:34 23/5/2007 [email protected]
Christmas Island 123 Nina Sauer I Sun Sep 11 2011 07:11:11 6/3/2008 [email protected]
Western Sahara 124 Jerod O'Reilly Mon May 24 1993 03:34:03 27/5/1982 [email protected]
Maldives 125 Aliza Nienow Fri Nov 29 1996 07:39:35 26/8/1993 [email protected]
Sierra Leone 126 Litzy Altenwerth MD Sun Jun 06 1993 04:05:20 14/1/1981 [email protected]
Peru 127 Willow Bahringer Tue Sep 15 1992 12:20:10 4/12/1988 [email protected]
Andorra 128 Ward Bode Mon Oct 12 1987 17:56:50 10/8/1992 [email protected]
Philippines 129 Maureen Boehm Wed Sep 09 1998 00:22:14 7/6/1996 [email protected]
United Arab Emirates 130 Linda Bins Sat May 09 1992 06:41:04 15/1/1984 [email protected]
San Marino 131 Abdiel Hahn Wed Mar 10 2004 03:07:17 22/8/1991 [email protected]
Cape Verde 132 Cheyanne Donnelly Wed Oct 07 1992 17:17:25 26/6/1982 [email protected]
Antigua and Barbuda 133 Kamron Schiller Wed Mar 08 2000 01:04:44 17/4/1997 [email protected]
Canada 134 Katheryn Parker Sun Mar 30 1980 14:57:16 5/2/1988 [email protected]
Bangladesh 135 Lisandro Hane Sr. Wed Oct 27 1993 05:48:02 1/11/1990 [email protected]
Barbados 136 Ms. Christophe Robel Thu Mar 22 2007 22:03:45 12/3/1996 [email protected]
Australie 137 Dr. Hilton Rowe Fri Oct 22 1982 17:58:23 2/1/2011 [email protected]
Germany 138 Tavares Considine Tue Aug 01 1995 20:54:49 19/12/2011 [email protected]
Hong Kong SAR China 139 Ali Rice Tue Mar 29 1994 21:24:33 25/5/1992 [email protected]
Malta 140 Monte Leannon Tue Dec 21 2010 00:37:19 10/8/2010 [email protected]
Bosnia and Herzegovina 141 Emanuel Krajcik Thu Nov 28 1996 09:14:44 22/11/2000 [email protected]
Malawi 142 Ms. Norma Volkman Wed Sep 14 1983 18:18:53 21/6/1992 [email protected]
Saint Lucia 143 Alek Thiel Thu Oct 11 2007 04:53:34 5/3/2013 [email protected]
Trinidad and Tobago 144 Valerie Bartell Wed Dec 17 1997 04:11:10 24/8/2007 [email protected]
Tuvalu 145 Gloria Bradtke Mon Dec 09 1991 12:28:28 3/8/1988 [email protected]
Neutral Zone 146 Laverne Hoeger Sun Mar 17 1996 19:00:57 28/2/1996 [email protected]
New Zealand 147 Deondre Pfannerstill Thu Nov 09 2000 10:44:49 28/10/1983 [email protected]
Kyrgyzstan 148 Afton Wisozk Wed Jun 13 2007 13:38:44 24/12/1985 [email protected]
Lesotho 149 Arturo Leannon Mon May 23 2005 18:08:51 15/4/2012 [email protected]
Kiribati 150 Annamae Mosciski Tue Dec 30 2008 22:20:02 19/7/1996 [email protected]
Niger 151 Oleta Cummerata Fri Apr 15 1994 00:54:48 8/11/2010 [email protected]
Lithuania 152 Evans Zboncak Thu Feb 10 1983 22:57:11 4/3/1997 [email protected]
Brazil 153 Mason Hagenes Wed Aug 09 1989 09:02:59 11/7/1992 [email protected]
Burundi 154 Kaya Klocko Sun Aug 12 2001 15:50:55 22/6/1996 [email protected]
Falkland Islands 155 Asa Hand Mon Nov 09 1998 12:17:30 26/8/2012 [email protected]
Pitcairn Islands 156 Georgiana Reynolds Thu Jul 04 2013 09:56:51 1/12/1997 [email protected]
South Georgia and the South Sandwich Islands 157 Lyric Doyle Fri Mar 14 2003 20:17:53 8/2/2008 [email protected]
Saint Pierre and Miquelon 158 Santina Ward II Sun May 26 1985 09:51:59 28/6/1980 [email protected]
Hungary 159 Pascale Barton Sun Sep 15 1991 09:44:21 10/10/2010 [email protected]
Metropolitan France 160 Lenny Bogan Wed Feb 26 1986 17:30:05 19/1/1987 [email protected]
Comoros 161 Miss Alexzander Goldner Fri Sep 20 1985 00:46:48 12/12/1991 [email protected]
Madagascar 162 Lawson Krajcik Sun Jul 12 1981 01:22:33 11/11/2013 [email protected]
Brunei 163 Antoinette Rice Wed Jul 30 1980 01:42:00 31/1/1980 [email protected]
Canton and Enderbury Islands 164 Macey Stoltenberg Mon Sep 06 1999 12:41:31 29/3/1986 [email protected]
Faroe Islands 165 Frederick Botsford Sat Sep 03 1983 22:43:53 12/10/2005 [email protected]
Johnston Island 166 Meagan Hoppe Wed Dec 07 1988 06:38:00 1/8/1986 [email protected]
Peru 167 Wilton Bosco Sat Dec 11 1999 07:48:03 10/10/2008 [email protected]
Turks and Caicos Islands 168 Wayne Effertz Thu Sep 24 2009 04:48:32 24/3/1983 [email protected]
Samoa 169 Daryl Kerluke Sat Oct 25 1986 18:36:18 13/8/1999 [email protected]
Uruguay 170 Andres Turcotte Mon Mar 02 2009 04:24:52 10/1/2006 [email protected]
Finland 171 Aaliyah Prosacco Thu Jul 05 2001 18:47:50 9/3/2009 [email protected]
Midway Islands 172 Ryleigh Terry Sat Jul 31 1982 13:10:20 5/4/1983 [email protected]
British Indian Ocean Territory 173 Miss Luz Ullrich Fri Feb 18 1994 01:01:06 7/6/1989 [email protected]
Malta 174 Liliane Mitchell Mon Dec 02 1991 22:37:18 25/12/2003 [email protected]
Puerto Rico 175 Dedrick Ortiz Sat Sep 27 2003 21:38:49 5/12/1995 [email protected]
Gambia 176 Durward Emard Thu Jul 20 2006 13:23:22 26/2/2009 [email protected]
Mauritania 177 Rogers Skiles Sat Nov 09 1996 21:55:48 1/8/1995 [email protected]
Haiti 178 Alexie Reilly Tue May 25 2010 09:46:23 5/4/1989 [email protected]
Argentina 179 Ibrahim Steuber Sat Jun 02 2012 21:25:44 11/6/2009 [email protected]
Israel 180 Kyleigh Bayer Sat Apr 22 2006 17:05:39 2/8/1995 [email protected]
Cayman Islands 181 Lyric Aufderhar Tue Sep 22 1992 17:25:56 30/1/2002 [email protected]
Libya 182 Rashad Romaguera Tue Jul 10 2007 15:06:27 13/12/1981 [email protected]
Latvia 183 Corbin Gerhold PhD Sun May 03 2009 11:53:41 5/6/2009 [email protected]
British Indian Ocean Territory 184 Lois Zieme IV Fri Nov 03 1989 16:07:23 3/8/1996 [email protected]
United Kingdom 185 Rosie Bogan PhD Sun Sep 03 1989 14:57:28 7/1/1996 [email protected]
Iraq 186 Anissa Gerhold Thu Mar 25 1999 09:17:32 23/10/2006 [email protected]
Saint Lucia 187 Garrick Cole Fri Oct 07 1983 11:20:47 10/10/1988 [email protected]
Antarctica 188 Clemens Ortiz Mon May 21 1990 04:12:31 24/6/1994 [email protected]
Myanmar [Burma] 189 Miss Efrain Tremblay Fri Nov 19 1982 09:37:49 15/11/1984 [email protected]
Jamaica 190 Tia Torphy Thu Dec 23 1993 23:10:50 19/8/1980 [email protected]
South Africa 191 Isabella Will Thu Jul 14 1994 18:35:11 1/5/1999 [email protected]
Saint Helena 192 Justen Hessel Wed Jun 04 1997 00:28:03 23/12/1982 [email protected]
Lebanon 193 Miss Rosalee Cassin Sat Feb 18 2012 03:15:44 15/7/1986 [email protected]
Afghanistan 194 Edison Beatty Mon Feb 02 1998 22:54:00 16/9/1982 [email protected]
Chile 195 Edmund Becker Thu May 12 1988 17:46:46 1/7/2011 [email protected]
French Polynesia 196 Zetta Crona Mon Apr 14 1997 17:12:34 23/10/2011 [email protected]
Oman 197 Henry Wilderman Mon Dec 17 1984 09:39:52 15/2/1997 [email protected]
Vanuatu 198 Chance Stroman Wed Apr 30 1986 16:05:16 14/7/1992 [email protected]
Guyana 199 Alvera Reilly Tue Mar 03 1998 06:30:03 4/10/1985 [email protected]
Anguilla 200 Keegan Bruen MD Sat Apr 09 1994 09:25:48 15/9/1993 [email protected]
Falkland Islands 201 Roy Moen Sun Aug 24 2003 16:27:44 11/10/1981 [email protected]
Pitcairn Islands 202 Allison Hansen Sun Feb 26 2006 06:09:11 1/3/1999 [email protected]
Norway 203 Mary Macejkovic Tue Dec 07 1999 07:48:20 16/10/1996 [email protected]
Afghanistan 204 Mrs. Florian McClure Wed Sep 30 1981 04:32:15 1/4/1996 [email protected]
Iceland 205 Ms. Miracle Brown Sun Nov 14 1982 00:13:18 12/5/1986 [email protected]
Saint Lucia 206 Ryder Kuhlman I Fri Aug 16 1985 14:25:21 3/12/1997 [email protected]
Israel 207 Ena Jaskolski Thu Dec 03 1998 02:12:18 10/8/1988 [email protected]
Ghana 208 Maddison McDermott Fri Oct 21 1988 14:44:03 31/1/2000 [email protected]
Guadeloupe 209 Marcos Cummerata Sun Jan 14 1990 07:09:43 17/2/1988 [email protected]
Kiribati 210 Clint Brakus Tue May 09 1989 00:34:30 24/6/1987 [email protected]
Saint Lucia 211 Ambrose Nikolaus Tue Nov 21 2006 18:52:06 4/4/2013 [email protected]
Trinidad and Tobago 212 Dr. Desmond Grady Wed Aug 07 1996 12:58:51 5/10/2008 [email protected]
Wake Island 213 Ms. Antone Hara Sun Apr 08 2007 16:04:52 25/7/1998 [email protected]
Barbados 214 Charley Nienow Fri Nov 26 1993 16:35:00 3/5/1995 [email protected]
Cambodia 215 Paige Kozey Sun Apr 12 1981 13:45:46 22/11/2013 [email protected]
India 216 Malika Mraz Tue Oct 13 1981 02:05:36 26/11/1992 [email protected]
Zimbabwe 217 Dr. Alexandre McGlynn Mon May 27 1991 19:48:11 2/6/1984 [email protected]
Brunei 218 Sunny Olson Sat Nov 19 2011 15:34:19 11/4/1989 [email protected]
Bermuda 219 Greta Huels Wed Jan 03 1996 19:40:57 11/7/1980 [email protected]
Netherlands 220 Audra Ziemann Sat Jan 07 1995 13:04:54 5/12/2006 [email protected]
Dronning Maud Land 221 Reggie Zemlak Wed Oct 29 1980 09:46:34 10/1/2011 [email protected]
Falkland Islands 222 Fletcher Mayer Tue Sep 16 1997 11:20:57 29/10/2001 [email protected]
Colombia 223 Odell Jacobs II Thu Nov 07 1991 20:01:05 21/4/1986 [email protected]
Antarctica 224 Maye Koch Jr. Wed Sep 18 1985 22:06:20 18/11/1983 [email protected]
Falkland Islands 225 Ms. Jaquelin Wiegand Mon Dec 21 1987 19:14:54 21/3/1992 [email protected]
Algeria 226 Mr. Dariana Braun Mon Jul 16 2012 23:04:37 16/2/1982 [email protected]
Antigua and Barbuda 227 Gregg Mueller Sat Mar 05 1994 08:47:00 6/9/2006 [email protected]
Tonga 228 Malcolm Murray Thu May 05 1988 20:43:39 10/2/1991 [email protected]
Cocos [Keeling] Islands 229 Nathanial Hilll DVM Sat Jan 29 2000 14:53:26 17/8/2005 [email protected]
Union of Soviet Socialist Republics 230 Art Dickens Fri Nov 19 2010 11:55:21 5/9/2005 [email protected]
Denmark 231 William Nicolas Jr. Mon Sep 03 2012 04:03:39 10/6/1989 [email protected]
Brunei 232 Holly Reynolds Sat Nov 24 2012 10:04:45 4/12/2006 [email protected]
Nicaragua 233 Orlo Kovacek Wed Feb 06 2013 04:33:49 4/10/1983 [email protected]
Swaziland 234 Dr. Esther Reichert Tue Jul 19 1983 02:21:34 13/2/2001 [email protected]
Aruba 235 Esteban Waelchi Fri Mar 13 1998 07:46:38 25/9/1987 [email protected]
Algeria 236 Kara Osinski Mon Jul 09 2007 11:44:30 14/9/1994 [email protected]
China 237 Daron Ortiz Sun Jun 24 2007 20:40:45 14/5/1991 [email protected]
Norway 238 Mallory Schaefer Fri May 12 1989 18:16:49 18/8/1994 [email protected]
Bermuda 239 Alize Heathcote Thu Nov 07 1996 17:33:58 17/7/2000 [email protected]
Tokelau 240 Tillman Kerluke I Fri Jul 29 1994 23:12:02 17/4/1990 [email protected]
Svalbard and Jan Mayen 241 Yasmin Ledner Tue Jul 13 1993 10:37:56 9/2/1997 [email protected]
Jamaica 242 Nestor Armstrong Sat Nov 09 2013 16:19:03 29/9/1995 [email protected]
Qatar 243 Americo Wuckert Fri Oct 01 1999 18:07:01 19/3/1990 [email protected]
Isle of Man 244 Fleta Rau Wed Apr 04 2001 10:20:39 7/11/1984 [email protected]
Equatorial Guinea 245 Mr. Stevie Quitzon Mon Jan 28 2013 17:32:49 28/9/1988 [email protected]
Somalia 246 Henri Smitham Fri Jun 10 1983 19:22:42 7/11/1988 [email protected]
Latvia 247 Dr. Reina Homenick Sun Jan 28 1996 12:32:36 19/6/1986 [email protected]
Panama Canal Zone 248 Verna Murphy Fri Mar 16 2007 11:46:35 14/2/2008 [email protected]
South Korea 249 Jeremy Crist DVM Sat May 21 2011 18:03:51 17/7/1983 [email protected]
Lebanon 250 Amie Padberg Tue Apr 19 1994 15:49:36 30/4/2012 [email protected]
Falkland Islands 251 Sonia Dibbert Tue Jul 13 1993 18:39:38 13/3/2000 [email protected]
Germany 252 Landen Emard Thu May 16 1985 21:43:20 30/11/2000 [email protected]
Libya 253 Giuseppe Purdy Sun Aug 14 1988 11:28:26 21/10/1982 [email protected]
United States 254 Mr. Esteban Thompson Mon Jun 20 1988 10:44:24 3/7/2010 [email protected]
Tunisia 255 Eusebio Lehner Tue Nov 29 1994 15:15:38 14/6/2007 [email protected]
Palau 256 Isobel Emard Wed Nov 01 1995 06:22:36 28/1/1980 [email protected]
Aruba 257 Furman Harann Tue Sep 25 2001 16:29:27 18/11/1998 [email protected]
Australie 258 Kelley Considine Thu May 07 1998 23:20:53 10/11/1981 [email protected]
Guam 259 Dax Robel Sun Nov 15 1998 12:34:13 21/12/2013 [email protected]
Vatican City 260 Dr. Mikel Ortiz Mon Sep 30 2013 01:06:06 11/10/2003 [email protected]
Rwanda 261 Mercedes Pollich Thu Apr 26 2001 04:36:54 11/3/1989 [email protected]
Antarctica 262 Tavares Bauch Sun Jul 03 2011 06:10:55 18/6/1997 [email protected]
Australie 263 Fernando Lind Mon Oct 31 2011 21:47:10 20/8/1982 [email protected]
Maldives 264 Patsy Graham Wed Mar 07 2007 01:25:03 12/8/2011 [email protected]
New Zealand 265 Marcelle Lynch I Fri Nov 29 2002 12:26:08 4/10/1989 [email protected]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment